[AccessD] Dlookup - where parameter being a form control vs a function

[Paul Wolstenholme]

John suggested DLookup is slower than the equivalent VBA.
The equivalent VBA (with an added sort order parameter) can be found at
http://allenbrowne.com/ser-42.html

On Fri, 21 May 2021 at 02:27, Jim Dettman via AccessD <
accessd at databaseadvisors.com> wrote:

> << any time you edit code stuff ends up decompiled>>
>
>  Not always.  Depends on the change.
>
> << the next time you try to run any code the source will
> be run through the compiler, turning everything back into p-code, which as
> I understand it, is what is actually interpreted.>>
>
>   As soon as you enter a line, it is tokenized and stored as p-code.  Basic
> syntax checking is performed at that point.   When you get a line
> highlighted in red, it means VBA couldn't figure the syntax out and the
> source line has yet to be stored as p-code.
>
>   When you click for a compile, then additional steps are carried out on
> the
> p-code.   Last I heard, VBA had sixteen different levels of compilation,
> all
> of which must be run through to end up with code that is considered
> "compiled".  But with an interpretive compiler, it's always the p-code that
> is executed.  The difference between "compiled" and "uncompiled" is just if
> all that checking is done at runtime or not.
>
> << TBH I have no idea whether it recompiles everything or just the module
> that
> changed.>>
>
>  With Access and VBA, I'm not sure.   It used to be that the modules stood
> on their own and each one could have different compile states.   But the
> Access team changed that behavior (starting with A2000 I believe) and since
> then, everything is treated as one.   I believe that was just a difference
> in the way they stored things (multiple projects vs one), and from the VBA
> viewpoint, it is always working with a project file.
>
>  Wayne Phillips would know for sure.   He just came out with Twin Basic,
> which at some point will be a viable replacement for VBA.   Right now, it's
> only good for writing code outside of Access, but he plans to continue
> enhancing it.   Wayne is the guy that wrote vbWatchDog and other products
> and has delved deep into VBA.
>
> Jim.
>
>
> -----Original Message-----
> From: AccessD On Behalf Of John Colby
> Sent: Thursday, May 20, 2021 9:16 AM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Dlookup - where parameter being a form control vs a
> function
>
> any time you edit code stuff ends up decompiled.  It is my understanding
> (useless I know) that the next time you try to run any code the source will
> be run through the compiler, turning everything back into p-code, which as
> I understand it, is what is actually interpreted.
>
> TBH I have no idea whether it recompiles everything or just the module that
> changed.
>
> On Thu, May 20, 2021 at 9:02 AM Jim Dettman via AccessD <
> accessd at databaseadvisors.com> wrote:
>
> >
> >  When it's SQL, it's not that it must be interpreted, but rather a
> > combination of Access helping you out, and that the statement is funneled
> > through the JET engines expression service.
> >
> > Between the two, that's what takes care of resolving things like form
> > references and VBA expressions.
> >
> >  Source is always compiled into p-code btw.  Source is never executed
> > directly.
> >
> > Jim.
> >
> > -----Original Message-----
> > From: AccessD On Behalf Of John Colby
> > Sent: Thursday, May 20, 2021 8:43 AM
> > To: Access Developers discussion and problem solving
> > <accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] Dlookup - where parameter being a form control vs
> a
> > function
> >
> > Code in access has to go through the interpreter, which may or may not
> > involve compiling to p-code first.  In any event it is a very different
> > animal from getting the value of a control on a form.  Very old memories
> > are telling me that dlookup is way slower than equivalent vba.  Not to
> > mention that the jet engine has to process the sql.  How and when it will
> > cause the function to be handed off to the interpreter is unknown.
> >
> > What are you trying to determine?
> >
> > On Wed, May 19, 2021 at 10:46 PM Borge Hansen <pcs.accessd at gmail.com>
> > wrote:
> >
> > > Hello everyone,
> > > Anyone got an answer to this question?
> > >
> > > So I've been replacing
> > > [forms]![frmSystem]![txtUserName]
> > > with a function
> > > fGetApplicationParameter("CurrentLoggedInUser")
> > > that returns the same text string
> > > i.e. the UserName of the current logged in User in a User Profile
> > > table called tblSystem
> > >
> > > Below is the code for testing and comparing Dlookup with the similar
> > > SQL select statement.
> > > Notice that when we use the function on the DLookup it has to be
> > > resolved outside of the third parameter of the DLookup whereas the
> > > forms!frmSystem!txtUserName can be resolved as part of the third
> > > DLookup parameter.
> > >
> > > And using a plain SQL Select statement the Forms!frmSystem!txtUserName
> > > is interchangeable with the function.
> > >
> > > Why  the difference on the DLookup whereas the two SQL select
> > > statements are similar in form?
> > >
> > > /borge
> > >
> > > === code ===
> > > ?DLookup("NextAvailableUFN", "tblSystem", "UserName =
> > > forms!frmSystem!txtUserName")
> > >  900005
> > > or
> > > SELECT tblSystem.NextAvailableUFN, tblSystem.UserName
> > > FROM tblSystem
> > > WHERE (((tblSystem.UserName)=[forms]![frmSystem]![txtUserName]));
> > >
> > > => notice the difference here: UserName = ' " & etc & " '
> > > ?DLookup("NextAvailableUFN", "tblSystem", "UserName = '" &
> > > fGetApplicationParameter("CurrentLoggedInUser") & "'")
> > >  900005
> > > or
> > > => notice: similar to =[forms]![frmSystem]![txtUserName])); above
> > > SELECT tblSystem.NextAvailableUFN, tblSystem.UserName
> > > FROM tblSystem
> > > WHERE
> > >
> (((tblSystem.UserName)=fGetApplicationParameter("CurrentLoggedInUser")));
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > John W. Colby
> > Colby Consulting
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> John W. Colby
> Colby Consulting
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>