How to optimize Adodb read/writes

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...

is it the update or opening the connection that is taking the time. I've found at times that access connections sometimes take a while to open. Can you open the connection once, and leave it open until everything is done?

This may not be a good move if you have lots of users, but then again, if that were the case, you shouldn't be using access ;)

Thanks. And yes, the opening takes a bit of time, but I believe the problem is more that I'm getting the data one at a time. I've just remembered that I can grab the data using:

SomeVariant=ADODB_RecSet.GetRows

This pulls in the data all in one command, and seems to work real fast. The variant becomes a 2 dimentional array sized according to the amount of data you pull in.

Just curious:

Is there a reason why you are not using the .Filter-method of Adodb.recordset?

Ok folks - I found the answer - the file I was accessing was on a real sloooow server. I had forgotten - I had thought it was on C:.

Thanks for the replies!

glad you found your problem. Something else you may want to consider... using ADO to insert into a database is not generally as efficient as calling a stored SQL statement. But now we are talking fractions of a second.