Error `object required` while trying to run MS Access queries in Excel VBA

Question

I'm trying to run Access DB queries in Excel, but facing problem.

Dim dbs As DAO.Database 

Set dbs = CurrentDb
dbs.Execute "DELETE FROM tblMyTable WHERE Bad", dbFailOnError

here it's getting

run time error 424 object required

exactly on the 2ndline

set dbs = CurrentDb

I already added reference DAO 3.6 object library. what to keep in place of CurrentDB. My MsAccess DB is in local disk.

You need to do something like Set dbs = OpenDatabase("C:\db1.mdb") — xidgel
– xidgel, Commented May 2, 2017 at 14:55
Hi Xidgel,Thanks for the quick response. but what if my DB is already Opened? — harsha kazama
– harsha kazama, Commented May 2, 2017 at 14:58
I used this code before declaring dbs variable Set objAccess = CreateObject("Access.Application") objAccess.OpenCurrentDatabase "C:\Users\" & sUser & "\Downloads\ICO\" & DB & ".mdb" — harsha kazama
– harsha kazama, Commented May 2, 2017 at 15:04

David Zemens · Accepted Answer · 2017-05-02 15:09:24Z

3

CurrentDb isn't recognized by Excel, so it's being treated as an empty variant, since you haven't assigned it anything. Using Option Explicit will prevent this sort of problem in the future.

If your DB is already open, try something like: Set dbs = GetObject(,"Access.Application").CurrentDb

Option Explicit '### This goes at the VERY TOP of ALL MODULES
Sub foo()

    Dim dbs As DAO.Database 
    Dim appAccess as Object '# Access.Application
    Set appAccess = GetObject(,"Access.Application") '# Get a handle on Access 
    Set dbs = appAccess.CurrentDb
    dbs.Execute "DELETE FROM tblMyTable WHERE Bad", dbFailOnError

End Sub

answered May 2, 2017 at 15:09

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

Collectives™ on Stack Overflow

Error `object required` while trying to run MS Access queries in Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related