VLoopUp/ DLoopUp Vs Ado.recordset Pros & Cons

[edisonl via AccessMonster.com]

Hi,

Anyone got any idea whats the difference between VLoopUp, DLoopUp & Ado.
Recordset pros, cons, best usage scenerio ?

Regards, Edison

 

[Klatuu]

There is no VLookup in Access. The DLookup is used to return the value of a
specific field in a specific record.

ADO is a recordset processing method; however, I prefer DAO because it is
not an ActiveX method and is more natural to Access and Jet.

But, comparing DLookup to ADO is not really a comparison at all. Its like
compare a screwdriver to a hammer. They are different. DLookup should be
used when you want to find the value of a specific field in a specific
record. To use ADO for this purpose adds a lot of overhead and extra code.

 

[edisonl via AccessMonster.com]

Dear Klatuu,

Thanks a lot.. Well will you be guiding me how to use DLoopUp ?

Eg: Product_Table, Field ProductName

Problem: To compare user input (through inputbox) with Data in ProductName
Field to check if it exists.

Regards, Edison

 

[Douglas J. Steele]

Dim strProductName As String

strProductName = InputBox("What product?")
If Len(strProductName) > 0 Then
If IsNull(DLookup("ProductName", "Product_Table", _
"ProductName = """ & strProductName & """")) Then
MsgBox strProductName & " does not exist."
Else
MsgBox strProductName " exists."
End If
End If

 

[edisonl via AccessMonster.com]

My Dear Douglas,

Thats absolutely awesome.. !

Guess you guys are right, I could have save so much line of coding and run
time using this ..
adodb(15 lines), Dlookup(10 lines)

dim rd as new adodb.recordset
dim conn as adodb.connection

set rd = new adodb.recordset
set conn = currentproject.connection

rd.open...
rd.filter...
do until(rd.eof)
if()
endif
rd.movenext
loop
rd.close
conn.close
set rd = nothing
set conn = nothing

Regards, Edison

Dim strProductName As String

strProductName = InputBox("What product?")
If Len(strProductName) > 0 Then
If IsNull(DLookup("ProductName", "Product_Table", _
"ProductName = """ & strProductName & """")) Then
MsgBox strProductName & " does not exist."
Else
MsgBox strProductName " exists."
End If
End If

Dear Klatuu,

[quoted text clipped - 25 lines]