Connect to Access Vba object model from Excel

Question

I can't see any option to enable the Vba object model in Microsoft Access as on this print screen:

From Excel I can see in the same option dialogue Window:

I know the following works fine to work with Access vba object model

Public strAllCode As String

Public Sub StringAllLines()

    Dim accObj As AccessObject  'Each module/form/report.
    Dim bWasOpen As Boolean     'Flag to leave form/report open if it was open.
    Dim strDoc As String        'Name of each form/report

    'Stand-alone modules.
    For Each accObj In CurrentProject.AllModules
        Call GetModuleLines(accObj.Name, True)
    Next

    'Modules behind forms.
    For Each accObj In CurrentProject.AllForms
        strDoc = accObj.Name
    bWasOpen = accObj.IsLoaded

    If Not bWasOpen Then
        DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
    End If

    If Forms(strDoc).HasModule Then
        Call GetModuleLines("Form_" & accObj.Name, False)
    End If

    If Not bWasOpen Then
        DoCmd.Close acForm, strDoc, acSaveNo
    End If

    Next

    'Modules behind reports.
    For Each accObj In CurrentProject.AllReports
    strDoc = accObj.Name
    bWasOpen = accObj.IsLoaded

    If Not bWasOpen Then
    'In Access 2000, remove the ", WindowMode:=acHidden" from the next line.
        DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
    End If

    If Reports(strDoc).HasModule Then
       Call GetModuleLines("Report_" & accObj.Name, False)
    End If

    If Not bWasOpen Then
       DoCmd.Close acReport, strDoc, acSaveNo
    End If

    Next

    Dim intFile As Integer

    '*** Set to next free open number ***
    intFile = FreeFile()
    Open "c:\temp\AllCode.txt" For Output As #intFile
    Print #intFile, strAllCode
    Close #intFile

End Sub

Private Function GetModuleLines(strModule As String, bIsStandAlone As Boolean)

    Dim bWasOpen As Boolean     'Flag applies to standalone modules only.
    Dim lngLineNo As Long

    If bIsStandAlone Then
        bWasOpen = CurrentProject.AllModules(strModule).IsLoaded
    End If

    If Not bWasOpen Then
        DoCmd.OpenModule strModule
    End If

    strAllCode = strAllCode & "**********" & vbCrLf
    strAllCode = strAllCode & strModule & vbCrLf
    strAllCode = strAllCode & "**********" & vbCrLf

    For lngLineNo = 1 To Modules(strModule).CountOfLines
    strAllCode = strAllCode & Right("     " & lngLineNo, 5) & ": " & Modules(strModule).Lines(lngLineNo, 1) & vbCrLf
    Next

    strAllCode = strAllCode & vbCrLf & vbCrLf & vbCrLf

    If Not bWasOpen Then
        On Error Resume Next
        DoCmd.Close acModule, strModule, acSaveNo
    End If

    Debug.Print strModule & " complete"

    DoEvents

End Function

This above is just a code sample that allows to export all modules and object names to a text file from within Access.

I would like to do it from Excel, in other words, the vba macro in Excel opens the Access database and processes each object name and type (table, form, query, report, vba modules...) into a text file and then closes the database. Can this be done?

The reference for the code posted

Answer 1

You could just write the code you have that you know works within access and run the macro from excel?

Not very dynamic but would serve your purpose

Set oAcc = CreateObject("Access.Application")

oacc.opencurrentdatabase "C:\yourdb.accdb", ,"password"
oacc.Run "Your_Macro"

Answer 2

Adding the Microsoft Access XX.X Object Reference library in the Excel VBE will expose the methods, parameters, and functions/subs in Access which you can call from within Excel.

After the reference is added, add an object of the type Access.Application and then use the Set command to set it equal to a new instance of the Access.Application object.

You can then bind variables to datasets, query/table definitions, and pretty much do anything you can do in Access. This is known as early binding.

Alternatively, you can create an unbound object variable (Dim foo As Object) and then Set it equal to the object reference (Set foo = New Access.Application).

You can create and manipulate the same structures as before, but you lose the Intellisense feature. This is known as late-binding.

Answer 3

ok I was quite lazy to experiment this before asking but with little adjustments from the code first posted above, it's possible to entirerly take control of the access vba object model. See below the code I code (first try) I came up with and it works:

Public strAllCode As String
Public appAccess As Object

Public Sub StringAllLines()

    'create new access object
    Set appAccess = CreateObject("Access.Application")
    'open the acces project
    Call appAccess.OpenCurrentDatabase( _
    "D:\ShF3\vba_list_module_proj\access northw modules _4_ok\Northwind 2007z.accdb")
    appAccess.Visible = False

    Dim accObj As Object  'Each module/form/report.
    Dim bWasOpen As Boolean     'Flag to leave form/report open if it was open.
    Dim strDoc As String        'Name of each form/report
    'Stand-alone modules.
    For Each accObj In appAccess.CurrentProject.AllModules
        Call GetModuleLines(accObj.Name, True)
    Next
    'Modules behind forms.
    For Each accObj In appAccess.CurrentProject.AllForms
        strDoc = accObj.Name
        bWasOpen = accObj.IsLoaded
        If Not bWasOpen Then
            'DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
        End If

    Next

    'Modules behind reports.
    Dim intFile As Integer
    '*** Set to next free open number ***
    intFile = FreeFile()
    Open "c:\temp\AllCode.txt" For Output As #intFile
    Print #intFile, strAllCode
    Close #intFile

End Sub


Private Function GetModuleLines(strModule As String, bIsStandAlone As Boolean)

    Dim bWasOpen As Boolean     'Flag applies to standalone modules only.
    Dim lngLineNo As Long
    If bIsStandAlone Then
        bWasOpen = appAccess.CurrentProject.AllModules(strModule).IsLoaded
    End If
    If Not bWasOpen Then
        appAccess.DoCmd.OpenModule strModule
    End If
    strAllCode = strAllCode & "**********" & vbCrLf
    strAllCode = strAllCode & strModule & vbCrLf
    strAllCode = strAllCode & "**********" & vbCrLf

    For lngLineNo = 1 To appAccess.Modules(strModule).CountOfLines
        strAllCode = strAllCode & Right("     " & lngLineNo, 5) & ": " & appAccess.Modules(strModule).Lines(lngLineNo, 1) & vbCrLf
    Next

    strAllCode = strAllCode & vbCrLf & vbCrLf & vbCrLf

    If Not bWasOpen Then
        On Error Resume Next
        'appAccess.DoCmd.Close acModule, strModule, acSaveNo
    End If

    Debug.Print strModule & " complete"
    DoEvents

End Function

I just need to properly close the access database that remains open and do some adjustment to the information reported in the text file. In the end, I will not need the text file as an intermediary step but have everything in excel. Pascal