vba 如何通过 OpenRecordset (DAO) 删除所有数据库行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1421263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to delete all DB rows via OpenRecordset (DAO)
提问by Tom
How to delete all rows from table before starting inserting them?
如何在开始插入之前删除表中的所有行？
回答by Oorang
Two things. First, DAO vs ADO is almost irrelevant. They are both available to Access up through Windows 7 and AFAIK there are no plans to remove them. Either should be OK to use in Access. Secondly, you can just do this:
两件事情。首先，DAO 与 ADO 几乎无关。它们都可以通过 Windows 7 访问，AFAIK 没有删除它们的计划。应该可以在 Access 中使用。其次，你可以这样做：
Public Sub Example()
With Access.CurrentDb
.Execute "DELETE Table2.* FROM Table2;"
.Execute "INSERT INTO Table2 ( fld1, fld2 ) SELECT Table1.ID, Table1.MyField FROM Table1;"
End With
End Sub
You could just to this:
你可以这样：
Public Sub Example()
With Access.DoCmd
.RunSQL "DELETE Table2.* FROM Table2;"
.RunSQL "INSERT INTO Table2 ( fld1, fld2 ) SELECT Table1.ID, Table1.MyField FROM Table1;"
End With
End Sub
But the Execute method throws more informative error messages (and if you still care, is DAO).
但是 Execute 方法会抛出更多信息丰富的错误消息（如果您仍然关心，那就是 DAO）。
回答by Beatles1692
If you are programming vba I assume that you are working with Adodb or simply ADO. Therefore in order to delete a table records you can use a command object to do that.
如果您正在编写 vba，我假设您正在使用 Adodb 或只是 ADO。因此，为了删除表记录，您可以使用命令对象来执行此操作。
Dim cnn as Connection
Dim cmd as Command
Set cnn=new Connection()
cnn.ConnectionString="ConnectionString"
cnn.Open()
Set cmd=new Command()
cmd.ActiveConnection=cnn
cmd.CommandText="DELETE FROM MyTable"
cmd.Execute()
cnn.Close()
Updated: In order to use ADO objects you should add a reference to ADODB library
更新：为了使用 ADO 对象，您应该添加对 ADODB 库的引用
回答by Smandoli
DAO Recordsets don't have an effective way to empty the table, as far as I know. If it's a small table, you can probably delete record-by-record, but I would find that silly. I just use DoCmd.RunSQL to run a DELETE query. I would typically set SetWarnings to False temporarily, of course.
据我所知，DAO 记录集没有一种有效的方法来清空表。如果它是一个小表，您可能可以逐条删除记录，但我会觉得这很愚蠢。我只是使用 DoCmd.RunSQL 来运行 DELETE 查询。当然，我通常会暂时将 SetWarnings 设置为 False。
Since this is separate from DAO, I would see that operation through before opening the recordset properly.
由于这与 DAO 是分开的，我会在正确打开记录集之前看到该操作。
回答by hhaseli
Please try this:
请试试这个：
Do While Not rs.EOF
rs.Delete
rs.MoveNext
Loop
