From: PeterM on
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?
From: PeterM on
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?
From: Michael Bauer [MVP - Outlook] on


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?