Function to check if a VALUE is in a TABLE... Optimization needed...

G

Greg Kaufman

I have a function that checks to see if a value exists somewhere in a table

The code that I'm using currently is below - basically I create a recordset which consists of a SELECT statement WHERE the value is true. If the record count of the recordset is zero, I know the value doesn't exist

What I'm wondering is if there's a better way to do this - a faster way. Say without using a recordset - some function that exists that I don't know about. My program will be doing this type of thing a lot, so even a marginal time savings would be beneficial to me.

Thanks in advance!

Gre

CURRENT CODE:

Public Function IsInTable(TestString As Variant) As Boolea
Dim rstRecordset As Recordse

IsInTable= Fals

Set rstRecordset = CurrentDb.OpenRecordset("select FIELDNAME from TABLE where FIELDNAME = " & Chr(34) & TestString & Chr(34)
If rstRecordset.RecordCount = 0 The
IsInTable= Fals
Els
IsInTable= Tru
End I

rstRecordset.Clos
Set rstRecordset = Nothin

End Function
 
T

Tim Ferguson

What I'm wondering is if there's a better way to do this - a faster way.
Click to expand...

It's functionally the same, but is probably easier to maintain:

IsInTable = (0 < _
DCount("*", "SomeTable", "SomeField = """ & strSomeValue & """") _
)



Hope that helps

Tim F
 
M

Matthias Klaey

I have a function that checks to see if a value exists somewhere in a table.

The code that I'm using currently is below - basically I create a recordset which consists of a SELECT statement WHERE the value is true. If the record count of the recordset is zero, I know the value doesn't exist.

What I'm wondering is if there's a better way to do this - a faster way. Say without using a recordset - some function that exists that I don't know about. My program will be doing this type of thing a lot, so even a marginal time savings would be beneficial to me.

Thanks in advance!

Greg

CURRENT CODE:

Public Function IsInTable(TestString As Variant) As Boolean
Dim rstRecordset As Recordset

IsInTable= False

Set rstRecordset = CurrentDb.OpenRecordset("select FIELDNAME from TABLE where FIELDNAME = " & Chr(34) & TestString & Chr(34))
If rstRecordset.RecordCount = 0 Then
IsInTable= False
Else
IsInTable= True
End If

rstRecordset.Close
Set rstRecordset = Nothing

End Function
Click to expand...

You probably can make things faster if you create an index on
FIELDNAME. Some minor points: I would use dbOpenForwardOnly and
modify the code and thus the execution time a little bit:

Public Function IsInTable(TestString As Variant) As Boolean
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Select FIELDNAME from TABLE" & _
" where FIELDNAME = '" & TestString & "'", dbOpenForwardOnly)
IsInTable = NOT rst.EOF
rst.Close
Set rst = Nothing
End Function

For an alternative, check out the DLookup function in the online help,
but this ist probably slower.

HTH
Matthias Kläy
 
S

Sigurd Bruteig

You probably can make things faster if you create an index on
FIELDNAME. Some minor points: I would use dbOpenForwardOnly and
modify the code and thus the execution time a little bit:

Public Function IsInTable(TestString As Variant) As Boolean
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Select FIELDNAME from TABLE" & _
" where FIELDNAME = '" & TestString & "'", dbOpenForwardOnly)
IsInTable = NOT rst.EOF
rst.Close
Set rst = Nothing
End Function

For an alternative, check out the DLookup function in the online help,
but this ist probably slower.

HTH
Matthias Kläy
--
Click to expand...

Hi
This code from Matthias looks great. DLookup is r e a l y slow, don't even
consider it. Remember to index the actual field in the table.

sigurd
 
J

John Spencer (MVP)

Using DbEngine(0)(0) vice CurrentDb. That is usually a bit faster. So you can
do this all in one line.

IsInTable = DbEngine(0)(0).OpenRecordset _
("SELECT FIELDNAME from TABLE where FIELDNAME = " & _
Chr(34) & TestString & Chr(34)).Fields(0) > 0
 
Top