Quit running a command after a certain time has elapsed

[dicepackage]

I have a function below to search for the last name of someone and go to the most recent ID with that name. The problem is that these searches can take a long time since I have so many records (around 700k and growing).

I would like to run this function but automatically quit after 20 seconds if it is not found within that time. Typically if it doesn't find something right away it will not find anything at all. Is there anyway to run a piece of code and automatically break if the time limit is exceeded?


Dim rsForm As DAO.Recordset
Set rsForm = Forms!Main.RecordsetClone
With rsForm
.FindLast "[LASTNAME] = " & Chr(34) & Me.TextSearch & Chr(34)
End With
If rsForm.NoMatch = True Then
MsgBox "Not found", vbExclamation, "Error"
Else
Forms!Main.Bookmark = rsForm.Bookmark

[orange]

If the recordset is based on a table, does the table have an index? Finding an entry in 700000 should not take 20 sec.

You might want to watch the free video tutorial at this site to see what an index can do to performance.
http://www.datapigtechnologies.com/flashfiles/searchform.html

[dicepackage]

Yes, the table is indexed. I have an ID field as my primary key as well as LASTNAME which is also indexed. Despite the indexing it can take five or more minutes to complete searching if the record is not found or found at the end of the table.

[dicepackage]

I can also use ADO to get it going very fast where it actually uses the index. The problem is seek will only work with the first or last value. I want to know if there is anyway to have a message box pop up all values where the last name is equal to smith and yet still use my index.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection ' Open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:\Database.mdb"
End With
Set rst = New ADODB.Recordset
With rst
.Index = "LASTNAME" ' Select the index used to order the data in the Recordset object.
' Open the table by using a scrolling Recordset object.
.Open Source:="MYTABLE", _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
.Seek "Smith", SeekOption:=adSeekLastEQ ' Find LASTNAME= Smith
If Not .EOF Then ' If a match is found, print the value of the specified field.
MsgBox .Fields("ID").value
Else
MsgBox "NOT FOUND"
End If
.Close ' Close the Recordset object.
End With
cnn.Close ' Close connection and destroy object variables.
Set rst = Nothing
Set cnn = Nothing