Run a MakeTable query in Access from Excel

Question

I have an Excel file that I need to automate. When the user opens the Excel Report, it will prompt if they would like to refresh the data. If they say yes, then I need it to run a MakeTable query that is in an Access database to refresh all the underlying numbers for the report.

I have searched and haven't been able to find anything that actually works.

I tried to even create VBA code inside of Access that I could run that would handle the query, but I can't even get that to run from Excel VBA.

Here is the current code I have along with some of what I have tried that didn't work:

Sub RunAccessMTQuery(ByVal DBLocation As String)
  Dim db As Object

  Set db = CreateObject("Access.Application")
  With db
    .OpenCurrentDatabase DBLocation
    .Visible = True
'    .Run "Main.RunMTJobBond"
'    .docmd.runmacro "Main.RunMTJobBond"   '<---Module.Proc Name
'    .docmd.runmacro "mMTJobBond"          '<---Macro name that calls the Proc
    .Application.Run "mMTJobBond"
    .CloseCurrentDatabase
    .Quit
  End With

'Below was found on https://support.microsoft.com/en-us/help/131921/running-a-microsoft-access-macro-from-microsoft-excel
'  Dim Chan as Long

'Opens Microsoft Access and the file nwind.mdb
'  Shell("""C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE"" """ & DBLocation & """")
'Initiates a DDE channel to Microsoft Access
'  Chan = DDEInitiate("MSACCESS", "system")
'Activates Microsoft Access
'  Application.ActivateMicrosoftApp xlMicrosoftAccess
'Runs the macro 
'  Application.DDEExecute Chan, "RunMTJobBond"
'Terminates the DDE channel
'  Application.DDETerminate Chan

End Sub

Either I need to be able to run the MakeTable query from Excel, or launch Access and have it run the query. Nothing I have tried has worked...I have gotten numerous errors such as:

Runtime error 5 - Invalid procedure call or arguement

Runtime error 2517 - Application-defined or object-defined error

Others as well, but I don't recall them as I was able to work through those, and these are some of the ones I'm stuck on. Any help would be great, thanks.

C Perkins C Perkins · Accepted Answer · 2017-04-28T20:46:40

The Application.Run() command runs a function or sub procedure, not a macro. That's why the attempts to run the macro do not work.

As for running the function or sub directly, the expected parameter format for Run() is "projectname.procedurename", NOT "modulename.procedurename". Unless you have multiple VBA projects within your database (which I assume you do not), then you don't even need projectname. See documentation here.

Try

Set dbApp = CreateObject("Access.Application")
With dbApp
  ...
  .Run "RunMTJobBond"

This assume that RunMTJobBond is a public function or sub in a standard module.

Run a MakeTable query in Access from Excel

1 Answers