Which is more efficient?

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?

SORRY... posted this in the wrong group.

"PeterM" wrote:

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?

Why don't you play with the various alternatives you have in mind and test
how much time each one takes?

--
Best regards
Michael Bauer - MVP Outlook
Category Manager - Manage and share your categories:
SAM - The Sending Account Manager:
http://www.vboffice.net/product.html?lang=en


Am Sat, 3 Apr 2010 11:16:01 -0700 schrieb 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?