CODEWITHSUNDEEP
excelms-accessvba
harsha kazama
harsha kazama

Reputation: 189

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

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.

Upvotes: 2

Views: 1951

Answers (1)

David Zemens
David Zemens

Reputation: 53663

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

Upvotes: 3

Related Questions