Recently I was asked to assist on a small project which used Excel to reporting on data within Microsoft Access. Simple enough, said I, I’ve done that before.
Now this project required the looping through of sales divisions with a company database and pulling together several million rows into a quite substantial report – which was ten put into Excel and pivoted. No problem, said I.
Things were going just fine. I setup the master Excel workbook and a script with copied over the template sheets into the destination workbook. But then I ran into a problem. When I opened the database file, a Access 2007 file, I got this error.
Run-time error 3343 Unrecognized Database Format
Which is very weird. This was my code
dim oDBEngine as DAO.DBEngine
set oDBEngine = new DAO.DBEngine
I hunted around wondering, is my file corrupt? But everything checked out okay.
I short bit of searching and I found the solution, well solutions really.
- Use ADO – nope sorry I ain’t going there
- Use the Access Object Library ver 12 (14 for 2010)
I will only talk about the second option because that helps retain most of a user’s code when you have been using tried and tested DAO for your Access umm errr access. The above snipped will be replaced with:
Dim oAccess As Access.Application
Dim oDB As DAO.Database
Set oAccess = New Access.Application
Set oDB = oAccess.CurrentDb
This simple change does the trick.
I just wonder if there is some intermediary layer between DAO/Jet and the accdb format. It does beg the question why is DAO incapable of opening the file directly?
That’s a mystery for a future post. I hope this helps keep someone from pulling their hair out or (heaven forbid) throwing their laptop out a window.