Which is more efficient?

[PeterM]

I have several of the following throughout my database. I had a thought: is
it more efficient to use the recordset listed below or to do multiple
DLOOKUP's? In this example, I know for sure that only one row of data will
always be returned.

Dim dbMDS As Database
Dim Appointment As Recordset
Set dbMDS = Application.CurrentDb

Set Appointment = dbMDS.OpenRecordset _
("select ap_start_time, ap_end_time, ap_type, ap_comments,
ap_with_whom, ap_date, apremindertime " _
& " FROM Appointments " _
& " WHERE AP_Date = #" & tmp_date & "# and ap_START_time = #" &
tmp_time & "# " , dbOpenForwardOnly)
If Appointment.EOF = True Then
Exit Sub
End If
Do While Not Appointment.EOF


I know I'd have to do multiple DLOOKUP for each of the columns in the select
statement. In queries of only one or two columns, I always use DLOOKUP but
only if I'm sure there will only be one row returned.

Which method is preferred in a performance standard?

 

[John W. Vinson]

I have several of the following throughout my database. I had a thought: is
it more efficient to use the recordset listed below or to do multiple
DLOOKUP's? In this example, I know for sure that only one row of data will
always be returned.

Dim dbMDS As Database
Dim Appointment As Recordset
Set dbMDS = Application.CurrentDb

Set Appointment = dbMDS.OpenRecordset _
("select ap_start_time, ap_end_time, ap_type, ap_comments,
ap_with_whom, ap_date, apremindertime " _
& " FROM Appointments " _
& " WHERE AP_Date = #" & tmp_date & "# and ap_START_time = #" &
tmp_time & "# " , dbOpenForwardOnly)
If Appointment.EOF = True Then
Exit Sub
End If
Do While Not Appointment.EOF


I know I'd have to do multiple DLOOKUP for each of the columns in the select
statement. In queries of only one or two columns, I always use DLOOKUP but
only if I'm sure there will only be one row returned.

Which method is preferred in a performance standard?

If you're retrieving multiple fields, I'd guess that the recordset would win
hands down: it will need to hit the table only once. Multiple DLookUps will
have to open a connection, do the search, retrieve the field, close the
connection for every single field.

This is even more apropos if you might retrieve multiple records as well -
with a recordset you can move through it, with DLookUp it's much more work.

 

[PeterM]

Thanks for your insight John.

If you're retrieving multiple fields, I'd guess that the recordset would win
hands down: it will need to hit the table only once. Multiple DLookUps will
have to open a connection, do the search, retrieve the field, close the
connection for every single field.

This is even more apropos if you might retrieve multiple records as well -
with a recordset you can move through it, with DLookUp it's much more work.

 

[David Jennings]

REMOVE

 

[David Jennings]

REMOVE