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>

 

[RoryA]

I'm just wondering if it's worth trying to reregister the dll with admin rights.

 

[jasmith4]

I tried that: I ran the Command Prompt with Privilege Guard (to get admin rights), and I ran REGSVR32 MSJET40.DLL from C:\Windows\SYsWOW64. Nothing.

Should I un-register first? Should I shut down all Office apps first? Should I re-start my machine after un-registering and registering?

 

[RoryA]

you need to register dao360.dll rather than the Jet engine's dll.

 

[jasmith4]

I shut down all applications. I ran Command Prompt with Privilege Guard. I went to C:\Program Files (x86)\Common Files\Microsoft Shared\DAO and un-registered and registered all DLLs there (DAO350 and DAO360). All REGSVR32 messages showed success. I went to C:\Windows\SysWOW64 and un-registered and registered MSJetoledb40 and msjet40. Again all REGSVR32 messages showed success. Then I re-started my machine and tried my Excel experiment. Same error message.

 

[jasmith4]

New wrinkle: it works fine on other machines with the same environment: Windows 7 and Office 2010. I didn't make any changes you suggested: it's still the same Excel-2003 spreadsheet with macros referencing VBA, Excel 14.0 object library, OLE automation, Office 14.0 object library, Forms 2.0 object library, DAO 3.6 object library and Windows Script Host Object Model.