Excel 2010 VBA DAO query with Access 2010 database not working

Question

I used the below vba code in Excel 2007 to query Access 2007 accdb database successfully. Credit (Taken from "The Excel® Analyst’s Guide to Access" by Michael Alexander). However when I tried same for Excel 2010 with Access 2010 accdb database I had problem. First the system said DAO.Database is not defined in Excel 2010 VBA. So I went to references and ticked DAO 3.6. Next the system said it does not recognize the database at DBEngine.OpenDatabase("M:\SVRData\Booking 2801_BE.accdb"). Please help

Sub RunAccessQuery()

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

'Step 2:  Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase("M:\SVRData\Booking 2801_BE.accdb")
    Set MyQueryDef = MyDatabase.QueryDefs("Bookings")

'Step 3:  Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset

'Step 4:  Clear previous contents
     Sheets("Main").Select
     ActiveSheet.Range("A6:K10000").ClearContents

'Step 5:  Copy the recordset to Excel
     ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 6: Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
    ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
    Next i

End Sub

Don't use the DAO 3.6 reference - use the Microsoft Office 14.0 Access Database Engine reference. — Rory
– Rory, Commented Feb 9, 2015 at 10:28
Actually I used Microsoft Office 14.0 Access Database Engine initially but I get this error : "user defined type not defined". That is why I decided to tick the DAO 3.6 reference. — hhap
– hhap, Commented Feb 9, 2015 at 10:33
Your code compiles correctly with that Access Database Engine reference set. — Rory
– Rory, Commented Feb 9, 2015 at 10:37
NO. When I compile it highlights "MyDatabase As DAO.Database" and states that user defined not defined . Microsoft Office 14.0 Access Database Engine is already ticked — hhap
– hhap, Commented Feb 9, 2015 at 10:56
I wasn't actually asking a question, I was stating that it does compile correctly. — Rory
– Rory, Commented Feb 9, 2015 at 10:58

Tsahi Asher · Accepted Answer · 2017-08-27 13:39:01Z

1

DAO does not recognize accdb. You can:

Convert the back end to mdb.
Use ADO instead of dao.

Note if you are accessing a back end only with just tables then the conversion to mdb shouldn't pose any issues. However you may have to create a new mdb database then import your back end tables from the accdb.

I find this kind of thing frustrating especially on the deployment side.

edited Aug 27, 2017 at 13:39

Tsahi Asher

1,83417 silver badges30 bronze badges

answered Aug 27, 2017 at 10:55

JimT

111 bronze badge

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

1 Comment

Timo Over a year ago

I use Access 2016 and cannot access accdb with dao. Is there a game change for the future or is ADO the only solution?It will not help to put dbengine in front.

Collectives™ on Stack Overflow

Excel 2010 VBA DAO query with Access 2010 database not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related