Let's start out by saying I'm a ADODB novice. I have some code that works but is very slow at accessing my Access database. Can someone recommend a better method? My database at the moment is teeny - maybe 5 records at 7 columns per.

Here's what I've got:


Dim ADODB_Conn As New ADODB.Connection
Dim ADODB_RecSet As New ADODB.Recordset
Dim ConnectString As String 'Holds the information needed to access the DSN
Dim SelectString As String 'SQL command to send to database

ConnectString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=dbfile.mdb;" + _
"Persist Security Info=False;" + _
"User ID = Admin"

'Read Condition 1

SelectString = "SELECT * FROM Table Where Status = 1"
Set ADODB_Conn = New ADODB.Connection
ADODB_Conn.Open ConnectString
Set ADODB_RecSet = New ADODB.Recordset
ADODB_RecSet.Open SelectString, ADODB_Conn, adOpenStatic, adLockOptimistic
If ADODB_RecSet.RecordCount > 0 Then
ADODB_RecSet.MoveFirst
'pull in all the data for display later
For I = 1 To ADODB_RecSet.RecordCount
A(I) = ADODB_RecSet.Fields("A")
B(I) = ADODB_RecSet.Fields("B")
C(I) = ADODB_RecSet.Fields("C")
ADODB_RecSet.MoveNext
Next I
End If

'Read Condition 2, 3, 4, 5...
'I repeat the above code and just change the SelectString. E.G. ..

SelectString = "SELECT * FROM Table Where Status = 2"
Set ADODB_Conn = New ADODB.Connection
ADODB_Conn.Open ConnectString 'Open a connection to the database
Set ADODB_RecSet = New ADODB.Recordset
ADODB_RecSet.Open SelectString, ADODB_Conn, adOpenStatic, adLockOptimistic
If ADODB_RecSet.RecordCount > 0 Then
....
End If

'Now save some stuff into the DB

'Create an SQL query to populate a recordset
SelectString = "SELECT * FROM Table Where Status = 3"
Set ADODB_Conn = New ADODB.Connection 'instantiate our database connection object
ADODB_Conn.Open ConnectString 'Open a connection to the database
Set ADODB_RecSet = New ADODB.Recordset 'Instantiate our recordset object
ADODB_RecSet.Open SelectString, ADODB_Conn, adOpenStatic, adLockOptimistic 'Issue an SQL query that will return a recordset
ADODB_RecSet.Fields("A") = A(3)
ADODB_RecSet.Fields("B") = B(3)
ADODB_RecSet.Fields("C") = C(3)
ADODB_RecSet.Update

Set ADODB_RecSet = Nothing
Set ADODB_RecSet = Nothing



How come the above takes so long (maybe 3 seconds)? I suspect it be quicker if I read in & send out data in one line... can someone show me how?

Thanks...