How to optimize Adodb read/writes

[hjehan]

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

[Zeb]

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

[hjehan]

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.

[Patzer]

Just curious:

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

[hjehan]

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!

[Pinky98]

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.