CODEWITHSUNDEEP
sqlexcelvbams-access
J. Doe
J. Doe

Reputation: 199

Trying to run a DELETE query using DAO in VBA

Im using Access 2013 and Excel 2013. In terms of References, I am using Microsoft Office 15.0 Access database engine Object Library.

So I am trying to run a DELETE query in VBA. Here is what I have so far

Sub UpdatePartList()
    Dim ws As DAO.Workspace
    Dim dbs As DAO.Database
    Dim rsTable As DAO.Recordset
    Dim rsQuery As DAO.Recordset
    Dim rsSql As DAO.Recordset

    Set ws = DBEngine.Workspaces(0)
    Set dbs = ws.OpenDatabase("P:\Distribution Purchasing\Kit Bids\Kit Parts Query.accdb")

    'deletes previous part numbers
    Sql = "DELETE * FROM [Kit Parts]"
    Set rsSql = dbs.OpenRecordset(Sql, dbOpenDynaset)

When I run a SELECT query for the same table, it works just fine. But when I try to DELETE, I get this error.

" Run-time error '3219': Invalid operation. "

Upvotes: 2

Views: 4741

Answers (2)

Brian M Stafford
Brian M Stafford

Reputation: 8868

You can modify your SQL as @Rahul suggested, plus change your last line of code to:

dbs.Execute sql

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

DAO's OpenRecordSet is not appropriate for Delete queries, which do not return any recordset object. Use the Execute method instead:

Change

Set rsSql = dbs.OpenRecordset(Sql, dbOpenDynaset)

to

dbs.Execute(Sql)

also there's no need for * in the Delete SQL statement. Although Access will accept it, other systems probably won't.

Upvotes: 3

Related Questions