Run a MS Access Procedure from Excel

[Guest]

I want to execute a subroutine in MS Access database. But I get an error that
"Microsoft Access can't find the procedure 'myModule.mySub.' "

Dim acApp As Access.Application
acApp = CreateObject("Access.Application")
acApp.Application.Visible = True
acApp.OpenCurrentDatabase("C:\myDirectory\myDB.mdb", True)
acApp.Application.Run("myModule.mySub") 'Error on this line

mySub is defined as a Public sub.

Suggestions?

Thanks, Mark

 

[Jim Cone]

Mark,

Try... Set acApp = CreateObject("Access.Application")

Jim Cone
San Francisco, USA


I want to execute a subroutine in MS Access database. But I get an error that
"Microsoft Access can't find the procedure 'myModule.mySub.' "

Dim acApp As Access.Application
acApp = CreateObject("Access.Application")
acApp.Application.Visible = True
acApp.OpenCurrentDatabase("C:\myDirectory\myDB.mdb", True)
acApp.Application.Run("myModule.mySub") 'Error on this line

mySub is defined as a Public sub.
Suggestions?
Thanks, Mark

 

[Guest]

Made change and it still bombs out. It dies on the acApp.Application.Run
line of code.

Thanks, Mark

 

[Jim Cone]

Mark,

Try your code using only your acApp qualifier and make sure you have a reference
set to the Access library in Tools | references in Excel.

Dim acApp As Access.Application
Set acApp = CreateObject("Access.Application")
acApp.Visible = True
acApp.OpenCurrentDatabase("C:\myDirectory\myDB.mdb", True)

'I am unfamiliar with Access syntax, so some experimentation is in order.
'Note: no () used...

acApp.Run "myModule.mySub"
or
acApp.Run "mySub"
or
acApp.Run "myDB.mdb.myModule.mySub"

Jim Cone
San Francisco, USA


Made change and it still bombs out.
It dies on the acApp.Application.Run line of code.
Thanks, Mark

 

[Guest]

Jim - Thanks for the comments.

And the problem was:
The module name and the procedure name were the same!!!
Once the procedures name was changed, the code
' acApp.run "newSubname" ' worked great.

Only lost a half a day on it!

Mark