Speeding up updating records using ADO
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. |
Speeding up updating records using ADO
On Jun 9, 12:53*pm, Ken Valenti
wrote: 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. |
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com