Using Excel VBA to SQL delete in MS Access

Question

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

for clarity's sake the error actually occurs when you try to clear the variable declaration? Or during the delete query operation? You wrote one thing in your explanation and another in your code? — Scott Holtzman
– Scott Holtzman, Commented Nov 10, 2015 at 14:08
There is another issue waiting to trip you up. Since you're using ADO, you must use different wild cards -> Like '%Project 1%' — HansUp
– HansUp, Commented Nov 10, 2015 at 15:33
Thank you both. I am a total newbie when it comes to VBA with a DB, so the code was just modified copy and paste from the net. Scott, the line doesnt delete from the DB either, even though the SQL query works in Access. — Recycle_Bin28
– Recycle_Bin28, Commented Nov 10, 2015 at 17:12

Community · Accepted Answer · 2017-05-23 12:15:44Z

2

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.

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Nov 10, 2015 at 15:16

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Excel VBA to SQL delete in MS Access

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related