Using VBA to initiate Access macro

[volley1999]

I would like to run an Access macro from an Excel/VBA app. I'm able to run Access update queries from VBA using database.Execute queryname. Is there a similar way to run an Access macro?

 

[TonyJollans]

Hi volley1999,

Assuming database is your Access Application, use database.DoCmd.RunMacro "Macroname".

Enjoy,
Tony

 

[volley1999]

Tony,

Thanks for the quick response.

I get a VBA compile error "Method or data member not defined" when I enter

 database.DoCmd.RunMacro "MacroName"

. In my subroutine, I've dimensioned my database (EC.mdb) as a database, and opened it using

 DBEngine.OpenDatabase

. I'm sure I'm overlooking something simple. Any thoughts?

Thanks again!

 

[TonyJollans]

Sorry, I should think a bit more before I type. DoCmd is a method of the Access Application not the Database. I'm not familiar with using DBEngine from Excel - do you have an access application, or do you use it somehow else? I will play a bit and post back if I find anything.

Enjoy,
Tony

 

[TonyJollans]

Well, I've had a bit of a play and I can't see how to do it. I don't really understand the relationships between Access, DAO and Jet, but as far as I can tell you can only run macros within an Access application and if you open your database with DAO under Excel it doesn't seem possible. Perhaps one of the experts out there can explain or help. If nobody else posts here it might be worth starting another thread - and I'll keep out of it!!

Sorry not to be more help,
Tony