Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform for updating records | Excel Discussion (Misc queries) | |||
Updating old records | Excel Discussion (Misc queries) | |||
Updating Master Log records | Excel Worksheet Functions | |||
Updating records in another workbook | Excel Programming | |||
Appending/Updating records | Excel Programming |