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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
Back
Top