The real trick in optimizing your code is to NOT execute any sql over and
over in the loop.
That means you want to open the table up ONCE, and then do your searching.
Further, you want to make sure you can take advantage of high speed
indexing.
41,000 records is a very small file. However, we do have 266 searches, so in
fact we are searching
266 x 41,000 = 10,906,000
That gives us 10 million records. However, you can still get this time down
to about 1 second!
(yes, you read this correct...about 1 second!!!!)
Actually, on my SLOW pc, 260 searches on the tiny file of 40,000 records
happens in LESS then 1 tenth of a second (yes, you read that correct!).
Try somting like the follwing:
Dim rstSearchFor As DAO.Recordset
Dim rstMainTable As DAO.Recordset
Dim colResult As New Collection
Dim strSearch As String
Dim i As Long
Dim t As Single
' load up the 265 values we are going to seach for
Set rstSearchFor = CurrentDb.OpenRecordset("tableOf266GuysToSearch")
Set rstMainTable = CurrentDb.OpenRecordset("MainTableOfData")
' set index to search on
rstMainTable.Index = "FirstName"
t = Timer
Do While rstSearchFor.EOF = False
strSearch = rstSearchFor!SearchFor
' find all occuranges of given value in main table
rstMainTable.MoveFirst
rstMainTable.Seek "=", strSearch
If rstMainTable.NoMatch = False Then
Do While (rstMainTable!FirstName = strSearch)
colResult.Add rstMainTable!id.Value
' look for more values
rstMainTable.MoveNext
Loop
End If
' done looking for all of one search value... move to next
rstSearchFor.MoveNext
Loop
rstSearchFor.Close
Set rstSearchFor = Nothing
rstMainTable.Close
Set rstMainTable = Nothing
MsgBox "time = " & Timer - t
' now disply all matches
For i = 1 To colResult.Count
Debug.Print colResult(i)
Next i
End Sub
If you don't need the time down to 1 second, and can tolerate about 15 to 30
seconds, then you can use two recordsets, and a find first (ado is going to
be MUCH FASTER, as it can keep the index opened, where as DAO cannot in this
case).
And, perhaps a real simple approach is to make take the 266 values in the
table, and do a join to all the matches in the main table. This mean you can
execute one sql statement, and get a resulting query that can be used in a
report. Further, this approach might be more useful then the above
code..since you DO NOT need any code to do this!!. A simple query can match
all the values for you..and again this query will execute is WELL UNDER 1
second.
Remember, a 40,000 record table is small..even if you have to search it 250
times...
The query would look like:
SELECT TableSearchItems.SearchFor, TableMainData.ID,
TableMainData.FirstName,
TableMainData.LastName, TableMainData.City
FROM TableSearchItems
INNER JOIN
TableMainData ON TableSearchItems.SearchFor = Table1.FirstName;
The above actually produces the resulting data and fields ready for a
report..and no code is needed!!! (and, it VERY fast...266 records searching
is
again well under one second!).
Further, you could add another 3, or 4 lines of code to the above
seek code example to to send the results of the collection out to
a new table. Which of the above choices you do is
going to depend on what you need to do with the results..
And, note that you can't use seek on a linked table...but you can open an
new new instance of the database like:
************ Code Start ***************
'This code was originally written by Michel Walsh.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Michel Walsh
Public Function OpenForSeek(TableName As String) As Recordset
' Assume MS-ACCESS table
Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _
(Mid(CurrentDb().TableDefs(TableName).Connect, _
11), False, False, "").OpenRecordset(TableName, _
dbOpenTable)
End Function
'************ Code End ***************
Just use:
Dim rst as Recordset
set rst=OpenForSeek("TableName")
