[COLOR=blue]Option Explicit
Private Sub Command1_Click()
Dim Cnn As ADODB.Connection
Dim RS As Recordset
Dim SQL As String
Dim rsarray
Set RS = New ADODB.Recordset
Set Cnn = New ADODB.Connection
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0" [COLOR=green]' Connecting to an MS Access 2003 (& earlier?) database[/color]
.ConnectionString = "Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;"
[COLOR=green]'.Provider = "Microsoft.ACE.OLEDB.12.0" 'Connecting to an MS ACCESS 2007-2013 database[/color]
.CursorLocation = [b]adUseClient[/b]
.Open
End With
RS.ActiveConnection = Cnn
SQL = "select * from customers"
RS.Open SQL
RS.ActiveConnection = Nothing [COLOR=green][b]' Disconnect recordset[/b][/color]
[COLOR=green]' Populate an array from disconnected recordset only when we need it[/color]
RS.MoveFirst
rsarray = RS.GetRows() [COLOR=green]' getting all rows into array. Moves rs to EOF[/color]
Debug.Print UBound(rsarray, 2) + 1 [COLOR=green]' how many records retrieved into array[/color]
Debug.Print RS.Fields(0).Name, rsarray(0, 0)
Debug.Print RS.Fields(1).Name, rsarray(0, 1)
RS.Filter = "CustomerID='CHOPS'" [COLOR=green]' Apply a filter
' Populate array with filtered data[/color]
RS.MoveFirst
rsarray = RS.GetRows()
Debug.Print UBound(rsarray, 2) + 1 [COLOR=green]'records retrieved into array, should differ from previous count to show filter is applied[/color]
Debug.Print RS.Fields(0).Name, rsarray(0, 0)
End Sub[/color]