DBEngine.OpenDatabase(AccessDB) works in 2003, not 2010

[jasmith4]

My code is simple enough. I have an Excel 2003 workbook with macros, and it's just trying to open an Access database in the same folder as the workbook. It works on WinXP/Office2003, but on Win7/Office2010. Its references are (in 2010):

- Visual Basic for Applications
- Microsoft Excel 14.0 Object Library
- OLE Automation
- Microsoft Office 14.0 Object Library
- Microsoft Forms 2.0 Object Library
- Microsoft DAO 3.6 Object Library
- Windows Script Host Object Model

All these are the same as on my 2003 system, except that "14.0" is "11.0". In Win7/Office2010, when the Set statement runs, I get "Run-time error -2147024770 (8007007e) Automation error / The specified module could not be found."; in WinXP/2003 it's fine.

What's going on?

<CODE>
Public DBobj As DAO.database
Public Const Pwd$ = "MyDatabasePassword"

Public Sub OpenDatabase() ' Open the main database shared (False) and read-only (True)

If DBobj Is Nothing Then Set DBobj = DBEngine.OpenDatabase(ThisWorkbook.Path & "\MyDatabase.mdb", False, True, "MS Access;PWD=" & Pwd$)

End Sub
</CODE>

 

[jasmith4]

I've boiled it down even further. I created a blank new workbook in Excel 2010, right-clicked Sheet1 and chose View Code, added the DAO 3.6 reference and wrote the following:

<CODE>
Sub Foo
Debug.Print DBEngine.Version
End Sub
</CODE>

...and it STILL doesn't work! I even search my hard drive for DAO360.DLL, unregistered and registered it -- nothing. Just that same pesky error.

 

[RoryA]

In 2010 you should be setting a reference to
Microsoft Office Access 14.0 Database Engine Object Library
rather than DAO3.6

 

[jasmith4]

Even if it's a 2003 workbook? Then I can't have the same workbook work in both versions of Excel? Wow, that's beyond dumb, even for Microsoft...

 

[RoryA]

Sorry - missed that it was 2003 format. Is it 32bit Excel 2010?

 

[jasmith4]

Yes, 32-bit. But also, what would you have to do in 64-bit?

 

[RoryA]

I am fairly certain there is no 64bit version of the DAO3.6 library so it will not work with 64bit Office.
Is your Win7 install 32 or 64bit? I'll try and set up a similar test machine tomorrow.

 

[jasmith4]

Win7 is 64-bit, but Office 2010 is 32-bit. FYI C:\Windows\SysWOW64\MSJet40.dll is version 4.0.9756.0, vintage 7/13/2009 9:15 pm.

So I take it one Excel-2003 spreadsheet which opens an Access-2003 database won't work in all possible scenarios -- WinXP/Win7-32/Win7-64 x Office2003/2007-32-64/2010-32-64.

 

[RoryA]

OK, I ran a test and your simple debug.print version works fine for me with just DAO3.6 reference on Win7 64bit and Office 2010 32 bit. (as long as your db is mdb, I think that should be ok across versions)
So I think there may be an issue on your machine. Do you have admin rights?

 

[jasmith4]

No, but we do use this thing called Avecto Privilege Guard, which lets us right-click some executable, including Command Prompt or IE, and run it with admin privileges. I suppose I can also have someone bump up my privileges for one login session.