Excel VBA procedure crashes when opening a Access database connection/using Access Runtime

Question

When executing a VBA procedure from Excel to connect to an Access database using Microsoft Access Runtime 2016 on my second laptop, the VBA procedure seems to freeze processing for a few seconds, crashes and closes the Excel application without triggering the error handler in my code so there is no error message for me to decipher. What can be the issue or how can I trap the error? This VBA application works fine on my first laptop which has the full version of Access with no issues. The second laptop was working before I loaded Microsoft Access Runtime 2016. I was using a database application called "MDB Plus" which reads Access database files but now that doesn't work anymore.

I'm using the following: OS: Windows 10, MS Office: 2007, MS Access Runtime 2007-2016, Excel VBA 2007

To try to resolve this I: 1.) Uninstalled MS Access Runtime 2016 and the Excel application still crashed, 2.) Install MS Access Runtime 2007 and the Excel application still crashes.

Here is my code:

Sub TestGetTblPrimKey()
        Dim oDBConn                 As ADODB.Connection
        Dim sDBConnString           As String
        Dim moDBTblRecordSet        As ADODB.Recordset
         Const sDBTableLocPath       As String _
            = "C:\Users\kmass\AppData\Roaming\InvestManager\"
    '
        On Error GoTo ERROR_HANDLER
    '
       'Create Database connection
        Set oDBConn = New ADODB.Connection
       'Create Table Record-Set
        Set moDBTblRecordSet = New ADODB.Recordset
       'Build DB connection string
        sDBConnString = _
                    "Provider=" & "Microsoft.ACE.OLEDB.12.0" & ";" _
                    & "Data Source='" _
                    & sDBTableLocPath _
                    & "tMeta_Table_Master.accdb" & "'"
    'Open Database Table and Record-Set
        oDBConn.Open sDBConnString  '* <--CRASHES HERE
    '
    '    Call ... the rest of the code to get record key
    '
    TestGetTblPrimKeyExit:
        oDBConn.Close
       'Release Table Objects
        Set moDBTblRecordSet = Nothing
        Set oDBConn = Nothing
        Exit Sub
    '
    ERROR_HANDLER:
        Debug.Print Err.Number & vbCrLf & Err.Description
        Resume TestGetTblPrimKeyExit
    '
    End Sub

I expected the code to connect to the Access Database.

ASH ASH · Accepted Answer · 2019-09-10T12:25:52

You can easily control Access from Excel, and do all kinds of things within the Access object, from Excel. Here are three simple demos of what you can do.

1)

'Open MS Access Form, from Excel
Global oApp As Object

Sub OpenAccess()

   Dim LPath As String
   Dim LCategoryID As Long

   'Path to Access database
   LPath = "C:\your_path_here\Northwind.mdb"

   'Open Access and make visible
   Set oApp = CreateObject("Access.Application")
   oApp.Visible = True

   'Open Access database as defined by LPath variable
   oApp.OpenCurrentDatabase LPath

   'Open form of interest
   oApp.DoCmd.OpenForm "Form1"

End Sub

2)

'RUN MS ACCESS MACRO FROM EXCEL:
Sub AccessTest1()
      Dim A As Object
      Set A = CreateObject("Access.Application")
      A.Visible = False
      A.OpenCurrentDatabase ("C:\your_path_here\Northwind.mdb")
      A.Application.Run "ExportToExcelTest"
End Sub

3)

' Run a delete query in MS Access, from Excel
Sub OpDaHus01()
    Dim strDatabasePath As String
    Dim appAccess As Access.Application
    Dim strSQL As String

    strDatabasePath = "C:\your_path_here\Northwind_2012.mdb"
    strSQL = "DELETE tblTest.* FROM tblTest;"
    Set appAccess = New Access.Application
    With appAccess
        .OpenCurrentDatabase strDatabasePath
        .DoCmd.RunSQL strSQL
        .Quit
    End With
    Set appAccess = Nothing

End Sub

If you need to run the code from a Macro, it needs to be a Public Function (rather than Sub), and it needs to be in a standard module (not a Form, Report or Class module).

Excel VBA procedure crashes when opening a Access database connection/using Access Runtime

1 Answers