check item query, performance comparison

[davyre]

Hi,
I have a procedure to check itemName if it is exist in a table called tblItem. If it does exist, then set its ID to variable X, if not, then create that new item. I have two codes that roughly do the job, but performance-wise, which one is better?

code 1 : Using dlookup

dim check as string
dim X as long
check=dlookup("ID","TblItem","ItemName='" & itemName & "'")
if isnull(check) then
  'create new item
else
  X=check
end if

code 2 : Using Recordset

dim dbs as database
dim rst as DAO.Recordset
dim qry as string
dim X as long
set dbs=currentDb

qry="SELECT * FROM TblItem WHERE ItemName='" & itemName & "'"
set rst=dbs.OpenRecordset(qry)
if rst.EOF=false then
   X=rst("ID")
else
   'create new item
end if

The reason I consider using Recordset is that the performance is fast (CMIIW).
any input? Thanks

**the codes above are just rough codes, so pardon me if some variables are not defined first or some syntax errors exist

 

[PHV]

Why select all fields ?
qry="SELECT [!]ID[/!] FROM TblItem WHERE ItemName='" & itemName & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

Hi PHV,
I guess that's a habit for me to select ALL. I know I can also use SELECT ID for the qry. But that is not the main question,
the question is which is better, between using recordset and using dlookup to do the job(find matching Item).
Thanks!

 

[PHV]

The faster should be the recordset selecting a single field.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davyre]

ok thanks!

 

[PHV]

BTW, to get max performance create an index on ItemName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886