Simplest way of using ADO Recordset to replace DLookup command

J

J

Hello all! Two simple questions I have not been able to find the
answer to online. (1) Is ADO better in this case? (2) How is my ADO
syntax?



ORIGINAL DLOOKUP METHOD:
Dim SearchFor as Integer
SearchFor = 23 'This is just an example
txtControl1 = Dlookup("[MyTableReturnValue1]","MyTable","[MyTableKey]="
& SearchFor )
txtControl2 = Dlookup("[MyTableReturnValue2]","MyTable","[MyTableKey]="
& SearchFor )
txtControl3 = Dlookup("[MyTableReturnValue3]","MyTable","[MyTableKey]="
& SearchFor )
'...
txtControl7= Dlookup("[MyTableReturnValue7]","MyTable","[MyTableKey]="
& SearchFor )


NEW ADO RECORDSET METHOD:
Dim SearchFor as Integer
SearchFor = 23

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "SELECT * FROM [MyTable]", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic
objRecordSet.Find "[MyTableKey]=" & SearchFor
'I realize I could add a WHERE to my SQL statement but let's ignore
that for now


txtControl1 = objRecordSet.Fields.Item("MyTableReturnValue1")
txtControl2 = objRecordSet.Fields.Item("MyTableReturnValue2")
txtControl3 = objRecordSet.Fields.Item("MyTableReturnValue3")
'...
txtControl7 = objRecordSet.Fields.Item("MyTableReturnValue7")




That's it! So to review:
(1) Is ADO better in this case?
(2a) How is my ADO syntax?
(2b) Was I suppose to 'close' or 'release' the recordset when I'm done?

Thanks in advance for your insight,
~J
 
B

Barry Gilbert

ORIGINAL DLOOKUP METHOD:
Dim SearchFor as Integer
SearchFor = 23 'This is just an example
txtControl1 = Dlookup("[MyTableReturnValue1]","MyTable","[MyTableKey]="
& SearchFor )
txtControl2 = Dlookup("[MyTableReturnValue2]","MyTable","[MyTableKey]="
& SearchFor )
txtControl3 = Dlookup("[MyTableReturnValue3]","MyTable","[MyTableKey]="
& SearchFor )
'...
txtControl7= Dlookup("[MyTableReturnValue7]","MyTable","[MyTableKey]="
& SearchFor )


NEW ADO RECORDSET METHOD:
Dim SearchFor as Integer
SearchFor = 23

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "SELECT * FROM [MyTable]", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic
objRecordSet.Find "[MyTableKey]=" & SearchFor
'I realize I could add a WHERE to my SQL statement but let's ignore
that for now


txtControl1 = objRecordSet.Fields.Item("MyTableReturnValue1")
txtControl2 = objRecordSet.Fields.Item("MyTableReturnValue2")
txtControl3 = objRecordSet.Fields.Item("MyTableReturnValue3")
'...
txtControl7 = objRecordSet.Fields.Item("MyTableReturnValue7")

That's it! So to review:
(1) Is ADO better in this case?
(2a) How is my ADO syntax?
(2b) Was I suppose to 'close' or 'release' the recordset when I'm done?
Click to expand...

1) Better or worse is purely your call. Firstly, if either solution gives
you the result you're looking for then either one is ok. Do you notice any
performance difference? I would suspect that the ADO solution would be
slightly faster, but it's probably not noticable unless you have a fairly
large table.

2a) The only simplification I would suggest is this:
txtControl1 = objRecordSet("MyTableReturnValue1")

Since the "fields" collection is the default property of a recordset, you
can skip the other qualifications.

2b)Yes, you should absolutely close and dispose when you're done.
If objRecordSet.State = adStateOpen Then
objRecordSet.Close
Set objRecordSet = Nothing
End If

It's usually best to put the under your err resume resume label in your
error handler so they will be closed even if there's an error.

Barry
 
D

Dirk Goldgar

J said:
Hello all! Two simple questions I have not been able to find the
answer to online. (1) Is ADO better in this case? (2) How is my ADO
syntax?



ORIGINAL DLOOKUP METHOD:
Dim SearchFor as Integer
SearchFor = 23 'This is just an example
txtControl1 =
Dlookup("[MyTableReturnValue1]","MyTable","[MyTableKey]=" & SearchFor
)
txtControl2 =
Dlookup("[MyTableReturnValue2]","MyTable","[MyTableKey]=" & SearchFor
)
txtControl3 =
Dlookup("[MyTableReturnValue3]","MyTable","[MyTableKey]=" & SearchFor
) '...
txtControl7= Dlookup("[MyTableReturnValue7]","MyTable","[MyTableKey]="
& SearchFor )


NEW ADO RECORDSET METHOD:
Dim SearchFor as Integer
SearchFor = 23

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "SELECT * FROM [MyTable]", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic
objRecordSet.Find "[MyTableKey]=" & SearchFor
'I realize I could add a WHERE to my SQL statement but let's ignore
that for now


txtControl1 = objRecordSet.Fields.Item("MyTableReturnValue1")
txtControl2 = objRecordSet.Fields.Item("MyTableReturnValue2")
txtControl3 = objRecordSet.Fields.Item("MyTableReturnValue3")
'...
txtControl7 = objRecordSet.Fields.Item("MyTableReturnValue7")




That's it! So to review:
(1) Is ADO better in this case?
(2a) How is my ADO syntax?
(2b) Was I suppose to 'close' or 'release' the recordset when I'm
done?

Thanks in advance for your insight,
~J
Click to expand...

The ADO *would* be better, except for this:
objRecordSet.Find "[MyTableKey]=" & SearchFor
'I realize I could add a WHERE to my SQL statement but let's ignore
that for now
Click to expand...

To get the real advantage of reading the record only once, instead of 7
times -- which is the real point of the exercise -- you should not read
all the records into your recordset. Instead, you *should* add the
WHERE clause to your SQL statement so that the database engine can use
any index on [MyTableKey], quickly locate the matching record(s), and
send back only that record.

Your syntax is okay, though (as Barry Gilbert) pointed out,
unnecessarily verbose.
 
J

J

Thank you gentlemen!

Dirk said:
J said:
Hello all! Two simple questions I have not been able to find the
answer to online. (1) Is ADO better in this case? (2) How is my ADO
syntax?



ORIGINAL DLOOKUP METHOD:
Dim SearchFor as Integer
SearchFor = 23 'This is just an example
txtControl1 =
Dlookup("[MyTableReturnValue1]","MyTable","[MyTableKey]=" & SearchFor
)
txtControl2 =
Dlookup("[MyTableReturnValue2]","MyTable","[MyTableKey]=" & SearchFor
)
txtControl3 =
Dlookup("[MyTableReturnValue3]","MyTable","[MyTableKey]=" & SearchFor
) '...
txtControl7= Dlookup("[MyTableReturnValue7]","MyTable","[MyTableKey]="
& SearchFor )


NEW ADO RECORDSET METHOD:
Dim SearchFor as Integer
SearchFor = 23

Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "SELECT * FROM [MyTable]", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic
objRecordSet.Find "[MyTableKey]=" & SearchFor
'I realize I could add a WHERE to my SQL statement but let's ignore
that for now


txtControl1 = objRecordSet.Fields.Item("MyTableReturnValue1")
txtControl2 = objRecordSet.Fields.Item("MyTableReturnValue2")
txtControl3 = objRecordSet.Fields.Item("MyTableReturnValue3")
'...
txtControl7 = objRecordSet.Fields.Item("MyTableReturnValue7")




That's it! So to review:
(1) Is ADO better in this case?
(2a) How is my ADO syntax?
(2b) Was I suppose to 'close' or 'release' the recordset when I'm
done?

Thanks in advance for your insight,
~J
Click to expand...

The ADO *would* be better, except for this:
objRecordSet.Find "[MyTableKey]=" & SearchFor
'I realize I could add a WHERE to my SQL statement but let's ignore
that for now
Click to expand...

To get the real advantage of reading the record only once, instead of 7
times -- which is the real point of the exercise -- you should not read
all the records into your recordset. Instead, you *should* add the
WHERE clause to your SQL statement so that the database engine can use
any index on [MyTableKey], quickly locate the matching record(s), and
send back only that record.

Your syntax is okay, though (as Barry Gilbert) pointed out,
unnecessarily verbose.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
V

Van T. Dinh

.... in addition:

* You should *always* check for empty Recordset before trying to access the
Field values. Your code will error out if the Recordset is empty.

* I tend to use adOpenForwardOnly rather than adOpenStatic for this sort of
"read-only" Recordset. I read in one of the ADO books that
open-forward-only Recordset is the most efficient. OTOH, I don't think we
would notice the difference in speed between adOpenStatic &
adOpenForwardOnly unless in a loop with lots of iterations.
 
Top