If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Which is more efficient?
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? |
#3
|
|||
|
|||
Which is more efficient?
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? |
Thread Tools | |
Display Modes | |
|
|