HI Everyone,
I am running a big delete query through some code and it is TAKING AGES. It took about 20-30 seconds on a test database but when i tried it on our production database (that has to remain in use) it took about 4 hours.
All up this query cycles through only about 400 records (the ones in the deleteme table) so i dont understand why it would take so long except to assume that it is due to the fact that the database is in production and therefore being used concurrently.
The code actually worked fine and did exactly what i wanted but it is just too slow to run.
It is MS Access 2000 sitting on Win2k server and i connected to it by linking the tables in another MS Access 2000 db on my PC (win2k).
Any ideas...
Function bigdelete()
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String
Set objConn = CurrentProject.Connection
Set objRS = New ADODB.Recordset
strSQL = "SELECT DeleteMe.id FROM DeleteMe;"
objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic
'accurate record count
objRS.MoveLast
objRS.MoveFirst
'suppress warnings while we do all the deletes
DoCmd.SetWarnings False
'Loop through recordset performing three queries on each ID number
For A = 1 To objRS.RecordCount
DoCmd.RunSQL "DELETE table1.* FROM table1 WHERE table1.ID = '" & objRS!id & "';", False
DoCmd.RunSQL "DELETE table2.* FROM table2 WHERE table2.ID = '" & objRS!id & "';", False
DoCmd.RunSQL "DELETE table3.* FROM table3 WHERE table3.ID = '" & objRS!id & "';", False
objRS.MoveNext
Next
'Set warnings back on
DoCmd.SetWarnings True
'Close recordset
Set objRS = Nothing
Set objConn = Nothing
End Function
I am running a big delete query through some code and it is TAKING AGES. It took about 20-30 seconds on a test database but when i tried it on our production database (that has to remain in use) it took about 4 hours.
All up this query cycles through only about 400 records (the ones in the deleteme table) so i dont understand why it would take so long except to assume that it is due to the fact that the database is in production and therefore being used concurrently.
The code actually worked fine and did exactly what i wanted but it is just too slow to run.
It is MS Access 2000 sitting on Win2k server and i connected to it by linking the tables in another MS Access 2000 db on my PC (win2k).
Any ideas...
Function bigdelete()
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String
Set objConn = CurrentProject.Connection
Set objRS = New ADODB.Recordset
strSQL = "SELECT DeleteMe.id FROM DeleteMe;"
objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic
'accurate record count
objRS.MoveLast
objRS.MoveFirst
'suppress warnings while we do all the deletes
DoCmd.SetWarnings False
'Loop through recordset performing three queries on each ID number
For A = 1 To objRS.RecordCount
DoCmd.RunSQL "DELETE table1.* FROM table1 WHERE table1.ID = '" & objRS!id & "';", False
DoCmd.RunSQL "DELETE table2.* FROM table2 WHERE table2.ID = '" & objRS!id & "';", False
DoCmd.RunSQL "DELETE table3.* FROM table3 WHERE table3.ID = '" & objRS!id & "';", False
objRS.MoveNext
Next
'Set warnings back on
DoCmd.SetWarnings True
'Close recordset
Set objRS = Nothing
Set objConn = Nothing
End Function