如何使用 VBA 清除 Access 中的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/762813/
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 do I clear a table in Access with VBA?
提问by Mitch Wheat
What I'm trying to do is, while in Excel, use VBA to push data to an existing Access table. I've been able to do this, but am having one small hiccup. Before I push the data to access, I want to clear the current data on the Access table, so when the new data from Excel comes in, it is the only data in the Access table. I really don't know how to write code for Access since the class has been on VBA for Excel. I've tried several different approaches and each time it doesn't work. For example, the code that seemed like it should work is
我想要做的是，在 Excel 中，使用 VBA 将数据推送到现有的 Access 表。我已经能够做到这一点，但有一个小问题。在我推送数据访问之前，我想清除Access表上的当前数据，所以当来自Excel的新数据进来时，它是Access表中唯一的数据。我真的不知道如何为 Access 编写代码，因为该课程一直在使用 VBA for Excel。我尝试了几种不同的方法，但每次都不起作用。例如，看起来应该工作的代码是
DoCmd.RunSQL "DELETE tblName.* FROM CoversheetTableFourthAttempt
but I get an error telling me to define an object.
但是我收到一个错误，告诉我要定义一个对象。
If you could help me with this, I would really appricate it
如果你能帮我解决这个问题，我真的很乐意
I've put my code below for reference.
我把我的代码放在下面以供参考。
Sub AccessFourthMonth()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Users\Kent\Documents\MBA\Winter 2009 Semester\MBA 614\Final Project\shilded\testdatabase.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "CoversheetTableFourthAttempt", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Project") = Range("A" & r).Value
.Fields("Description") = Range("B" & r).Value
.Fields("Amount") = Range("C" & r).Value
.Fields("Date") = Range("D" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
回答by Mitch Wheat
Try
尝试
DoCmd.RunSQL "DELETE * FROM TableName"
This article might be of interest: Executing SQL Statements in VBA Code
这篇文章可能很有趣：在 VBA 代码中执行 SQL 语句
回答by dsteele
Try the following from Excel:
从 Excel 中尝试以下操作：
dim cn as adodb.connection
dim cmd as adodb.command
set cn = new adodb.connection
cn.open "put your connection string here"
set cmd = new adodb.command
cmd.commandtype = adcmdtext
cmd.commandtext = "Delete * from myTable"
cmd.activeconnection = cn.connectionstring
cmd.execute
回答by HansUp
Your DoCmdapproach has two problems. You used a quote to start a string, but didn't include a closing quote. But even with proper quoting, your DoCmdwon't work because Excel does not know that CoversheetTableFourthAttemptis the name of a table in an Access database.
你的DoCmd方法有两个问题。您使用引号开始字符串，但没有包含结束引号。但即使正确引用，您DoCmd也不会工作，因为 Excel 不知道CoversheetTableFourthAttempt是 Access 数据库中表的名称。
You showed that you can successfully create an ADO connection to your Access database. So my suggestion is to use the Executemethod of the connection object to execute your SQL statment:
您展示了您可以成功创建到 Access 数据库的 ADO 连接。所以我的建议是使用Executeconnection对象的方法来执行你的SQL语句：
cn.Execute "DELETE FROM CoversheetTableFourthAttempt;"
Finally, visit Problem names and reserved words in Accessto understand why Date, Description, and Projectare not great choices for Access field names.
最后，访问Access 中的问题名称和保留字，了解为什么Date、Description和Project不是 Access 字段名称的最佳选择。
回答by Adarsha
DoCmd is internal to Access application and not recognized by Excel application. Simple approach to your problem is to fire the delete query from Excel itself.
DoCmd 是 Access 应用程序内部的，Excel 应用程序无法识别。解决您的问题的简单方法是从 Excel 本身触发删除查询。
Add this part after your cn.Open "Provider.. line
在你的 cn.Open "Provider.. 行之后添加这部分
cn.Execute "DELETE * FROM CoversheetTableFourthAttempt"
This should clear the table before next part which fills the data runs.
这应该在填充数据运行的下一部分之前清除表格。
