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

Question

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.

Answer 1

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.