Run Access Macros From Excel Or Run A Macro In MS-Access From Excel VBA

[bobdole22]

I am trying to run this code, I got from another thread on this forum that is closed. It is supposed to link to an Access file and run it's macro.I have 2007, so my db files are ".accdb" if that changes anything. Here is the code:

VB:

Dim strDatabasePath As String
Dim appAccess As Access.Application

strDatabasePath = "C:\Users\zachk\Desktop\Strats 2011.01.accdb"
Set appAccess = New Access.Application
With appAccess
Application.DisplayAlerts = False
.OpenCurrentDatabase strDatabasePath
.DoCmd.RunMacro "qry_MakeTrustTable_Adhoc"
.Quit
End With
Set appAccess = Nothing

I am getting the error
User-defined Type not defined

on line:

Set appAccess = New Access.Application

[bobdole22]

Actually, it only does that the second time I run it. The first time it simply reports a Blank MessageBox " ". Doesn't have any info whatsoever. Is it possible I'm getting a warning because we usually runs it with warnings off in Access? Is that possible in Excel.

Also, even stranger is that I have a MsgBox "Finished" at the end of the sub that doesn't get run. This makes me think that the code doesn't go all the way though.

[rollis13]

Cross-post:
Run Access Macros in Excel (http://www.mrexcel.com/forum/excel-questions/723177-run-access-macros-excel.html)
http://www.vbaexpress.com/forum/showthread.php?47345-Run-Access-Macros-in-Excel

[bakerman]

Dear bobdole22,

Please read the forum rules regarding cross-posting
http://www.excelfox.com/forum/f25/message-to-cross-posters-1172/#post5326

As an answer to your question, you need to set a reference to Microsoft Access 12.0 Object Library

[bobdole22]

Apologiez for the cross-post. Won't happen again. Although even after adding the reference the code still won't run all the way through. It stops right at

appAccess.DoCmd.RunMacro "qry_MakeTrustTable_Adhoc"

and displays a blank pop up box. Could it be a warning? It also doesn't do any of the code afterward. I double check the macro name and that is not the problem. He is a screen shot of the pop up I get.

[Excel Fox]

Are you sure
qry_MakeTrustTable_Adhoc exists as a Visual Basic macro within that database? If yes, try this



Dim strDatabasePath As String
Dim appAccess As Access.Application

strDatabasePath = "C:\Users\zachk\Desktop\Strats 2011.01.accdb"
Set appAccess = New Access.Application
With appAccess
.OpenCurrentDatabase strDatabasePath
.Run "qry_MakeTrustTable_Adhoc"
.Quit
End With
Set appAccess = Nothing

[edwards142]

Well dear, I can help you out in knowing how to create macros in Access with 3 different ways and how to run it. I have read this article when I was stuck in some problem with my Access macros coding. it’s really very helpful post. So, I thought you might also get some help from it.

SO TRY IT…!
3 Quick Ways To Create Macro In MS Access 2010/2013/2016/2019 Database (http://www.accessrepairnrecovery.com/blog/ms-access-macros)