Speeding up updating records using ADO

[Ken Valenti]

I have this code - which seems to work OK for a few records,
but is painfully slow when many (more than 20 or 30) records are updated.

It works on a standard Export table from Access to Excel (Assuming ID in
column 1)

'reference to ADO 2.8
Dim rs As ADODB.Recordset
Dim RowCounter As Long
Dim ColumnCounter As Integer
Sub UpdateRecords()
Const TheDBname = "C:\Access_FrontEnd.mdb"
For RowCounter = 2 To Cells(1, 1).CurrentRegion.Rows.Count
For ColumnCounter = 2 To Cells(1, 1).CurrentRegion.Columns.Count
rs.Open "SELECT * FROM [TheQry]WHERE ((([TheQry].ID)=" & Cells(RowCounter,
1).Value & "));", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TheDBname
& ";", adOpenDynamic, adLockOptimistic, adCmdText
rs.fields(Cells(1, ColumnCounter).Value).Value = Cells(RowCounter,
ColumnCounter)
Next ColumnCounter
rs.Update
rs.Close
Next RowCounter
Set rs = Nothing
End Sub

Question 1 -
TheDBname - is an Access query - (linked to another Access database)
Would it be faster to do a SQL statement on the database instead of using a
linked access query?

Question 2
Instead of opening a recordset for each record (row),
would it be faster to open all of the records with one query?

Queston 3
Sometimes my database gets corrupt. Is there a better approach (using ADO)?
Is it worth while to switch to DAO? If so - an example of code?

Thanks in advance your your help.

Any insight would be greatly appreciated.

 

[Lonnie M.]

I have this code  - which seems to work OK for a few records,
but is painfully slow when many (more than 20 or 30) records are updated.

It works on a standard Export table from Access to Excel (Assuming ID in
column 1)

'reference to ADO 2.8
Dim rs As ADODB.Recordset
Dim RowCounter As Long
Dim ColumnCounter As Integer
Sub UpdateRecords()
Const TheDBname = "C:\Access_FrontEnd.mdb"
For RowCounter = 2 To Cells(1, 1).CurrentRegion.Rows.Count
For ColumnCounter = 2 To Cells(1, 1).CurrentRegion.Columns.Count
rs.Open "SELECT * FROM [TheQry]WHERE ((([TheQry].ID)=" & Cells(RowCounter,
1).Value & "));", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TheDBname
& ";", adOpenDynamic, adLockOptimistic, adCmdText
rs.fields(Cells(1, ColumnCounter).Value).Value = Cells(RowCounter,
ColumnCounter)
Next ColumnCounter
rs.Update
rs.Close
Next RowCounter
Set rs = Nothing
End Sub

Question 1 -
TheDBname - is an Access query  - (linked to another Access database)
Would it be faster to do a SQL statement on the database instead of using a
linked access query?

Question 2
Instead of opening a recordset for each record (row),
would it be faster to open all of the records with one query?

Queston 3
Sometimes my database gets corrupt.  Is there a better approach (using ADO)?
Is it worth while to switch to DAO?  If so - an example of code?

Thanks in advance your your help.

Any insight would be greatly appreciated.

Your questions are really suited for Access--the ADODB object library
is essentially the same, but folks in an access user group are
probably more versed in access. I don't consider myself an expert, but
you shouldn't have to close and open your recordset each time within
your loop. Look into how to navigate using the ADO cursors, how to
test for begining and end of file (BOF & EOF), Find, and other ActiveX
Data Objects within Access. I also highly recomend "Access 2002
Desktop Developer's Handbook", ISBN# 0-7821-4009-2, see chapter 6
"ActiveX Data Objects"-- I bought it used(really new) for about $20.
This should answer your questions--time permitting.

HTH--Lonnie M.