I'd like to investigate if it's possible to speed up my operations. I'd like to:
1. Create a Main Recordset as a rough filter
2. Use the Main Recordset (in step 1) as source to make a finer filter. This can be repeated many times by a For Next loop
So instead of searching from a complete table every time in my For Next loop, I'd like to search from the Main Recordset instead.
My first thought was to use a Query in Navigation Pane as my Main Recordset. Query = "QMain" in eaxmple beneath. However, I am afraid that QMain is updated every time in my For Loop beneath. So I believe I won't gain any solution time.
Code:
Sub UseQueryInNavigationPane()
Dim li as Long
Dim sSQL_Fine As String
Dim sSQL_Main As String
Dim rst As ADODB.Recordset
'Change SQL to a Query stored in Navigation Pane
sSQL_Main = "SELECT * FROM TMyTable WHERE ..." 'SQL for my Main Recordset
CurrentDb.QueryDefs("QMain").SQL = sSQL_Main
'Fine Filter on "QMain"
Set rst = New ADODB.Recordset
For li = 0 To 1000
sSQL_Fine = "SELECT * FROM QMain WHERE ..." 'SQL for my Fiine Recordset. I'm afraid QMain is updated every time in my For Next loop.
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Source = sSQL_Fine
.Open
'Do something
'...
.Close
End With
Next li
Set rst = Nothing
End Sub
My 2nd thought was to use ADO recordset directly in VBA. But I have no idea how to write my 2nd SQL-code for the finer recordset beneath. I need to involve rstMain somewhere in my SQL-code.
Code:
Sub AllInVBA()
Dim li as Long
Dim sSQL_Fine As String
Dim sSQL_Main As String
Dim rstFine As ADODB.Recordset
Dim rstMain As ADODB.Recordset
Set rstMain = New ADODB.Recordset
Set rstFine = New ADODB.Recordset
'Main Filter
sSQL_Main = "SELECT * FROM TMyTable WHERE ..." 'SQL for my Main Recordset
With rstMain
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Source = sSQL_Main
.Open
end With
'Fine Filter
For li = 0 To 1000
sSQL_Fine = ? 'SQL for my Fine Recordset. I have no idea how to write this involving rstMain
With rstFine
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Source = sSQL_Fine
.Open
'Do something
'...
.Close
End With
Next li
rstMain.Close
Set rstMain = Nothing
Set rstFine = Nothing
End Sub
If someone has an idea how to use Filter on Filter, I'd appreciate your comments.
\Joakim