How to Run an Access Macro from Excel VBA Macro

Excel VBA Macro to Run an Access Macro

Suppose we have a MS Access Database .In this Access DB File , we have a Macro called "Clear_Tables" , this Macro is designed in the MS Access file , which will run the some Queries internally designed in MS Access File, as shown below :

Now If  you want to Run the above Macro "Clear_Tables"  in above Access File , you can simply provide the Details of  the Access File , Macro Name in the Excel Sheet from where you are running that Macro, as shown below :

Finally Run the following Macro from Excel , which will Run an Access Macro 

Sub Clean_DB_Files()

Dim SrcDB_Name As String

Dim SrcFolderPath As String

Dim SrcFilExt As String

Dim SourceDB_File 'As String

Dim TargetFolderPath As String

Dim WS As Worksheet

Dim Acc_DB As Object

Dim FSO As Object

Set WS = ThisWorkbook.Sheets("Clean_DB_Files")

Set Acc_DB = CreateObject("Access.Application")

Set FSO = CreateObject("Scripting.FileSystemObject")

For X = 2 To 100

If WS.Cells(X, 2) = "" Then Exit For

SrcDB_Name = WS.Cells(X, 1)

SrcFilExt = WS.Cells(X, 2)

DB_Macro = WS.Cells(X, 3)

SrcFolderPath = WS.Cells(X, 4)

    If Right(SrcFolderPath, 1) <> "\" Then

        SrcFolderPath = SrcFolderPath & "\"

    

    End If

'Checking the Folder Path existence

If FSO.FolderExists(SrcFolderPath) = False Then

WS.Cells(X, 5) = "Failed"

MsgBox ("Source Folder Does Not Exist or Path Not Found")

GoTo Nxt:

End If

SourceDB_File = SrcFolderPath & SrcDB_Name & SrcFilExt

'Checking the Source DB File existence in the Path

If FSO.FileExists(SourceDB_File) = False Then

WS.Cells(X, 5) = "Failed"

MsgBox ("Source DB File Does Not Exist or Path Not Found")

GoTo Nxt:

End If

Acc_DB.Visible = False

Acc_DB.OpenCurrentDatabase (SourceDB_File)

Acc_DB.DoCmd.RunMacro DB_Macro

Acc_DB.CloseCurrentDatabase

WS.Cells(X, 5) = "Success"

Nxt:

Next X

End Sub

Note :
In the Excel File shown above you can list out all the MS-Access files , and corresponding existed Macros of each Access file , from which you want to Run the Macros.

Make sure that the Macro which you want to execute should exist in the Source Database File.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------