DAO.DBEngine Class No Longer Registered in MS Access 2016 using Windows 10

A client recently upgraded from Windows 7 to 10 and moved from Access 2013 to 2016 (Included with Office 365).

A VBA macro in Excel now generates the following error:

"Run-time error '-2147221164 (80040154)' Class Not Registered." at the line:

 Set myEngine = New DAO.DBEngine 

I verified that DAO 3.6 is included in Resources. One site suggested "fixing" the Office install, which I did to no effect.

This response suggests moving to ADO, which I might be willing if I could find some examples of how to do so.

Here is the relevant code:

Option Base 1 Sub importPLCDataFromAccess(monthToImport As Date) 'This sub imports Influent and Effluent Data from the Access Database PLC_Data.mdb ' This database reads records from the PLC board on a daily basis and was created ' using Automation Direct's PointOfView software for interfacing with PLC Boards Dim myDbLocation As String myDbLocation = "K:\Users\WWTP Computer\Documents\POV_Projects\PLC Interface\PLC_Data.mdb" Dim myWorkbook As Workbook 'Skip spurious stuff ... Dim myEngine As DAO.DBEngine Dim myDB As DAO.Database Dim myRecordSet As DAO.Recordset Dim myWorkSpace As DAO.Workspace 'Skip more spurious stuff ... Set myEngine = New DAO.DBEngine ' This is the offending line Set myDB = myEngine.OpenDatabase(myDbLocation) 

It seems like I am missing something elementary here. Any help is appreciated.

1 Answer

I would recommend using late binding for code portability. As soon as you get this working you'll just find it fails later on a different computer. Declare everything as object and then pull in the references as you need with the CreateObject command.

EXAMPLE:

Public Function GetDBEngine() As Object On Error Resume Next 'try 120 Set GetDBEngine = CreateObject("DAO.DBEngine.120") If Err.Number <> 0 Then 'try 36 Err.Clear Set GetDBEngine = CreateObject("DAO.DBEngine.36") If Err.Number <> 0 Then Set GetDBEngine = CreateObject("DAO.DBEngine.35") Err.Clear End If End If On Error Goto 0 End Function Sub importPLCDataFromAccess(monthToImport As Date) 'This sub imports Influent and Effluent Data from the Access Database PLC_Data.mdb ' This database reads records from the PLC board on a daily basis and was created ' using Automation Direct's PointOfView software for interfacing with PLC Boards Dim myDbLocation As String myDbLocation = "K:\Users\WWTP Computer\Documents\POV_Projects\PLC Interface\PLC_Data.mdb" Dim myWorkbook As Workbook 'Skip spurious stuff ... Dim myEngine As Object Dim myDB As Object Dim myRecordSet As Object Dim myWorkSpace As Object 'Skip more spurious stuff ... Set myEngine = GetDBEngine Set myDB = myEngine.OpenDatabase(myDbLocation) 

FOOTNOTE:

While we are here, can I talk you out of the Option Base 1 ? Surely, there are other ways of making your code start at 1 that don't violate the spacetime continuum.