What can I do to speed the process of deleting records from an ADO recordset created in VB from an Access database source?
I have a project that I recently converted from DAO to ADO (VB6 using MDAC 2.1). The purpose of the project is to cycle through multiple Access 97 databases for maintenance - such as archiving/deleting old records in tables and repairing/compacting the databases. The parameters are all table-driven (database names and paths, tables to archive, days to retain/delete, etc). I use SQL statements to create the recordsets to process.
The record delete process is taking a very long time to complete in ADO. The logic for both versions of the project is the same - loop through all records in the recordset and delete them one by one. In DAO this was the most efficient way, avoiding other problems like locking issues in Access with append/delete queries with several thousand records. My logic using DAO would process even recordsets as large as 75,000 records in several seconds. The ADO code takes nearly 2 seconds per record to delete, and on large recordsets it hangs in mid-process.
My ADO object statements:
Set dbAge = New ADODB.Connection
dbAge.CursorLocation = adUseClient
dbAge.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & strDBPath & strDBName
recAge.Open strSQL, dbAge, adOpenForwardOnly, adLockOptimistic, adCmdText
The loop to delete the records (with some record counting, messaging and error trapping removed for simplicity):
Do While Not recAge.EOF
I am using client-side cursors and low-overhead cursors. The maintenance is done after business hours and requires users be disconnected from the Access databases.
I've unsuccessfully tried more updated versions of MDAC. I've unsuccessfully tried using other types of cursors and cursors with adLockBatchOptimistic to flag a smaller group of records for delete and then UpdateBatch to process them. I've unsuccessfully tried deleting the reference to the DAO Compatibility Library (which I had left in the project to do the repair/compact using DBEngine) thinking there may have been some type of conflict.
What else can I try to get the performance of ADO to an acceptable, comparable level?