CODEWITHSUNDEEP
excelms-access-2010vba
Recycle_Bin28
Recycle_Bin28

Reputation: 55

Using Excel VBA to SQL delete in MS Access

I am using VBA in Excel to remove rows from a MS Access database. I am encountering

"Error 3704 - Operation is not allowed when the object is open"

although when using similar code I was able to add information to the DB. When trying to delete, its giving me errors. Please assist!

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

Upvotes: 3

Views: 13993

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You are deleting, so you have not got a recordset.

 '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

For the most part, it is better to use DAO with MS Access

More notes on Execute.

Upvotes: 2

Related Questions