在MS Access中使用Excel VBA进行SQL删除

Recycle_Bin28 发表于 Dev

Recycle_Bin28

我在Excel中使用VBA从MS Access数据库中删除行。我遇到

“错误3704-打开对象时，不允许进行操作”

尽管使用类似的代码时，我能够向数据库添加信息。尝试删除时，它给了我错误。请协助！

Sub DeleteOldValues()

'--------------
'DIM STATEMENTS

Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String, StrQuery As String

'instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

'--------------
'THE CONNECTION OBJECT

strDBName = "Test.accdb"
strMyPath = "Y:"
strDB = strMyPath & "\" & strDBName

 'Connect to a data source:
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB

StrQuery = "DELETE * FROM Table WHERE ProjectName Like '*Project 1*'"

'Performs the actual query
adoRecSet.Open StrQuery, connDB

'--------------
'close the objects
adoRecSet.Close
connDB.Close

'destroy the variables
Set adoRecSet = Nothing  <-error occurs at this point
Set connDB = Nothing

End Sub

菲奥努阿拉

您正在删除，所以您没有记录集。

 'Connect to a data source:
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB
StrQuery = "DELETE * FROM Table WHERE ProjectName Like '*Project 1*'"

'Performs the actual query
connDB.Execute strQuery

在大多数情况下，最好将DAO与MS Access一起使用

有关执行的更多说明。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。