CODEWITHSUNDEEP
vbams-accessexcel-2010ms-access-2010
user2183177
user2183177

Reputation: 11

calling Access query from Excel macro

I have a VBA macro in Excel that calls a query in an Access database (.mdb). However, the Access has been updated to a .accdb file and I don't know how to change up the macro (and/or include any libraries as I don't truly understand that part yet) so that the code will work.

Here is the current "header" code:

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset


'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase("C:\Users\Ben\Google Drive\Database\Production\FOREAL PROD.mdb")

Upvotes: 1

Views: 1569

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

If you are getting an "unrecognizable database format" error then your Excel project may be using an older DAO (Data Access Objects) reference that does not know how to deal with .accdb files.

In the VBA window, choose Tools > References.... If you see an old DAO reference like...

"Microsoft DAO 3.6 Object Library"

...then that could explain the problem.

You'll want to upgrade that old DAO reference to this one...

"Microsoft Office 14.0 Access Database Engine Object Library"

...and to do that you'll need to download and install the Microsoft Access Database Engine 2010 Redistributable from here.

Upvotes: 1

Related Questions