What would be quicker, Dlookup or using DAO

[SurveyorinVA via AccessMonster.com]

Good morning-

I am updating a small database that I wrote and before I change code I would
like to get the opinion of the experts.
The database is being utilizied in a Front End and Back End and has an open
recordset to help speed everything up. Right now when I open one of my forms,
I am utilizing the Dlookup function to populate the controls on the form.
Would there be any variation in quickness if I were to create a query and
gather the data utilizing DAO or ADO, or is this all the same?

For example right now I have this code looking up several variables:
intSurveyID = Dlookup("[SurveyID]","tblSurvey","[ClientID] = " & intClientID)

instead something like:

Set rsMyConnection = CurrentProject.Connection
rsMySFR.ActiveConnection = rsMyConnection
rsMySFR.Open mySQL
rsMySFR.MoveFirst

Do Until rsMySFR.EOF Or intTaskCount > 12

'intSFRequestNO = rsMySFR.Fields("SFRequestNo")
dtDateField = rsMySFR.Fields("DateField")
intCrewNumber = rsMySFR.Fields("CrewNumber")
dblEODTimeRegular = Format(rsMySFR.Fields("EODtimeRegular"), "#0.
00")

Any thoughts would be greatly appreciated.

Thanks,
CF

 

[Douglas J. Steele]

Using a recordset, rather than multiple DLookups, should be faster.

 

[Stefan Hoffmann]

hi,

Right now when I open one of my forms,
I am utilizing the Dlookup function to populate the controls on the form.

You may replace single DLookup with http://allenbrowne.com/ser-42.html
or use your way (get the data with one SQL in a recordset).

But why don't you use linked tables and bound forms?


mfG
--> stefan <--

 

[itzfritz]

The database is being utilizied in a Front End and Back End and has an open
recordset to help speed everything up.

If you don't mind, what do you mean by "has an open recordset to help speed
everything up. "? I am a relatively new developer, and am always looking for
speed tips.

 

[Rick Brandt]

If you don't mind, what do you mean by "has an open recordset to help speed
everything up. "? I am a relatively new developer, and am always looking for
speed tips.

When you first open a linked table to your back end file the LDB file needs to
be created. This can cause a small delay. If you close the last connection to
the back end (by say closing the only opened form) then the LDB file is deleted.
Then when you open another connection it has to be created again.

Leaving one connection open all the time eliminates the constant deleting and
recreating of the LDB file.