One can easily delete many records from a MS Access DB table with a standard SQL DELETE command in the query window.
However, when trying to execute that same command thru VBA, the app can/does choke when there are more than 20,000 records to delete.
I temporarily worked around the issue by running a loop and doing the DELETE jobe in smaller 'chunks',
but I would like to know if there is a better way to handle this situation.
BTW, No, there is not another option for a more robust Database. This job is specced out for MS Access, period.
You want ADO DB's
adAsyncExecute to run your command asynchronously.
Then use code like the following:
Dim sSql As String Dim oCmd As ADODB.Command sSql = "DELETE FROM TableName WHERE 1=1" Set oCmd = New ADODB.Command With oCmd .ActiveConnection = CurrentProject.Connection .CommandType = adCmdText .CommandText = sSql .Execute adAsyncExecute End With
Edit: Another option is to run the query in another form, just make sure the form isn't always on top, you can execute the query in the other form's load event.