+ Reply to Thread
Results 1 to 6 of 6

Questions about Functions and the Expression Service

  1. Questions about Functions and the Expression Service

    I've used Access for a while, so I know that Access queries which use
    VBA functions or custom functions cannot be evaluated from outside
    Access. This was never a problem for me before, but it is a major
    obstacle in my current project. I'd like to do a little research into
    the issue and see if I can find an elegant workaround.

    >From what I understand, VBA functions work in Access queries because

    DAO uses something in Access called the "expression service". I'd be
    grateful if someone could help answer a few questions about the
    expression service:

    1. What kind of an animal is the expression service? Is it an object
    that can be referenced through the Access object model?

    2. Is there a way to create an instance of the Access expression
    service from outside of Access and hook up that instance to DAO?

    3. Do other applications, like VB6 or Excel, have their own versions
    of the expression service, and can those be used to support functions
    in queries?


    -TC


  2. Re: Questions about Functions and the Expression Service

    Hi TC

    I can't give you definitive answers on this, so hopefully others will
    contribute too.

    AFAIK, Access does not expose the ES. It is not part of the VBA model. I
    don't think it's part of the DAO model. My (limited) understanding is that
    it is part of Access itself, but not exposed through the Access library in
    code. The ES evaluates expressions such as:
    [Forms].[Form1].[Text0]
    substituting the value from the object into the expression, with any
    delimiters. For example, the ES can sort out the 3rd argument for DLookup(),
    such as:
    DLookup("Field1", "Table1", "[Field2] = [Forms].[Form1].[Text0]")
    However, you can't call the expression service, and DAO can't use it, so
    that expression would fail in code that simulates DLookup() function such as
    this one:
    http://allenbrowne.com/ser-42.html

    Similarly, RunSQL can call the ES, whereas DAO's Execute method can't.

    Re Q2, since your code cannot call the ES from within Access, you can't call
    it externally either.

    Tim, I'm not sure that the problem with VBA functions in queries is related
    to the ES. It could be about libraries. Say the current database uses just
    the Access, VBA and DAO libraries. You OpenDatabase on another MDB that uses
    those 3 plus other libraries. In *that* database, a query could contain a
    function that uses another library. But when examining that query from the
    current database, the query would be unintelligible, since it contains an
    irresolvable function call. It seems to me that this is a generic reason why
    such queries in other databases could not be reliably understood from the
    current database.

    Perhaps someone has more specific details.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "TC" wrote in message
    news:1187809493.326520.207860@z24g2000prh.googlegroups.com...
    > I've used Access for a while, so I know that Access queries which use
    > VBA functions or custom functions cannot be evaluated from outside
    > Access. This was never a problem for me before, but it is a major
    > obstacle in my current project. I'd like to do a little research into
    > the issue and see if I can find an elegant workaround.
    >
    >>From what I understand, VBA functions work in Access queries because

    > DAO uses something in Access called the "expression service". I'd be
    > grateful if someone could help answer a few questions about the
    > expression service:
    >
    > 1. What kind of an animal is the expression service? Is it an object
    > that can be referenced through the Access object model?
    >
    > 2. Is there a way to create an instance of the Access expression
    > service from outside of Access and hook up that instance to DAO?
    >
    > 3. Do other applications, like VB6 or Excel, have their own versions
    > of the expression service, and can those be used to support functions
    > in queries?
    >
    >
    > -TC



  3. Re: Questions about Functions and the Expression Service


    "Allen Browne" wrote in message
    news:46cd03c4$0$27795$5a62ac22@per-qv1-newsreader-01.iinet.net.au...

    > AFAIK, Access does not expose the ES


    AFAIK it does, via the Eval function...

    Problem is, this can only be called within the Access environment, and it's
    not an object,so even automation won't help to make use of it externally. I
    suppose you could call RunCode to execute it, but that's a little too
    convoluted for my liking - however others may disagree...




  4. Re: Questions about Functions and the Expression Service

    "Allen Browne" wrote in
    news:46cd03c4$0$27795$5a62ac22@per-qv1-newsreader-01.iinet.net.au:

    > AFAIK, Access does not expose the ES. It is not part of the VBA
    > model. I don't think it's part of the DAO model. My (limited)
    > understanding is that it is part of Access itself, but not exposed
    > through the Access library in code.


    Access has its own expression service and so does Jet, for the
    functions supported in both Jet and VBA. I don't know the particular
    relationship between them, but I expect they are very closely
    related, with the Jet ES handing off to the Access ES anything it
    doesn't understand.

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/

  5. Re: Questions about Functions and the Expression Service

    "Stuart McCall" wrote in
    news:fak5ls$5hb$1$8302bc10@news.demon.co.uk:

    > "Allen Browne" wrote in message
    > news:46cd03c4$0$27795$5a62ac22@per-qv1-newsreader-01.iinet.net.au..
    >
    >> AFAIK, Access does not expose the ES

    >
    > AFAIK it does, via the Eval function...
    >
    > Problem is, this can only be called within the Access environment,
    > and it's not an object,so even automation won't help to make use
    > of it externally. I suppose you could call RunCode to execute it,
    > but that's a little too convoluted for my liking - however others
    > may disagree...


    Huh? Eval is a member of the Access.Application object, as is almost
    anything that you can call as a standalone command without any
    prefixing object.

    Take a look at the Object Browser -- it tells you exactly what
    object these kinds of things hang off of (as it were).

    --
    David W. Fenton http://www.dfenton.com/
    usenet at dfenton dot com http://www.dfenton.com/DFA/

  6. Re: Questions about Functions and the Expression Service


    "David W. Fenton" wrote in message
    news:Xns9995796CCE367f99a49ed1d0c49c5bbb2@127.0.0.1...

    > Eval is a member of the Access.Application object


    Of course it is . Duh.

    Thanks



+ Reply to Thread