In Microsoft Access database application, always be sure to index fields that you put in the criteria section of the query design screen (on the right side of a where clause).
Try splitting a complex query into two or more queries. Restrict as many records as possible in the first query and use the first query as input to the second query.
If splitting a complex query into two or more queries is not the answer, try making the first query a make table query. A make table query will store the intermediate results in a new table for future use.
Try to use stored queries only. Do not create a form that has a select statement as the record source. If you do, the query will either not be optimized or will be optimized every time it runs.
If your queries are running slow, try using recordset operations with BeginTrans and CommitTrans.
If you have a query, other than a simple select query, such as an update or append query that takes too long to run, then the following technique will increase the speed of your query from minutes to a few seconds. The reason this works is that the record set operations are written to memory (transaction buffer) rather than directly to the hard disk.
The following is a snippet of some code we use to analyze our website hits. The key parts of the Transaction processing are bolded.
Dim wksp As DAO.Workspace
Dim special As String
Dim visitor As String
Dim visit_date As Date
Dim Engine As String
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
special = Chr(34) & “-” & Chr(34)
‘making Microsoft access databases run faster with better design
Set wksp = DBEngine.Workspaces(0) ‘ set up a transaction buffer
wksp.BeginTrans ‘ all record set changes are buffered after this
On Error GoTo roll0
Set rst2 = db.OpenRecordset(“M_Sitestats”)
Set rst = db.OpenRecordset(“select field1 from Sitestats”)
rst.MoveFirst
Do While Not rst.EOF
visitor = Left(rst!Field1, InStr(rst!Field1, ” “) – 1)
visit_date = CDate(Mid(rst!Field1, InStr(rst!Field1, “[“) + 1, 11))
If InStr(rst!Field1, “www.google”) <> 0 Then
Engine = “Google”
GoTo write_it
End If
If InStr(rst!Field1, “yahoo.com”) <> 0 Then
Engine = “Yahoo”
GoTo write_it
End If
write_it:
With rst2
.AddNew
!visitor = visitor
!visit_date = visit_date
!Engine = Engine
.Update
End With
check_next:
rst.MoveNext
Loop
wksp.CommitTrans ‘ here we write the changes to disk all at once
GoTo finish_it
roll0:
If Err.Number = 3022 Then
On Error GoTo roll0
Resume check_next
End If
MsgBox Err.Number & ” ” & Err.Description
wksp.Rollback ‘ cancel everything if unexpected error
finish_it:
rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing
End Sub
This method can speed up queries by allowing the transactions to be cached rather than written to disk after each transaction.
