问题描述:

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.

  • Open your Access DB
  • Press Alt+F11 to open the VBA editor
  • From the Tools menu choose References...
  • Add "Microsoft ActiveX Data Objects 6.1 Library" or whatever the latest version is

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.

相关阅读:
Top