running an ms access macro from excel vba

[UNDPC]

hello,
I am trying to find some sample code to call and run a macro in an ms access database from an excel vba module. the access macro has already been created and all it does is delete the data in a table and import the excel worksheet into the current table.
I'd appreciate any help with the syntax of creating the db connection and calling the macro. Thanks.

Patrick

 

[AnonGod]

Here's a little one we use.

Sub UploadToAccess()

    Dim appAcc As Object
    Set appAcc = CreateObject("Access.Application")
    appAcc.Visible = False
    
    appAcc.OpenCurrentDatabase("C:\database.mdb")
    appAcc.DoCmd.RunMacro "DB Macro Name"

    appAcc.Close, Quit, something like that - forgot :)
End Sub

-Andrew

[TAG]
... If you find my posts helpful, rate me up! ...
anongod@hotmail.com
'Drawing on my fine command of language, I said nothing.'

 

[UNDPC]

Andrew,
Thanks for the code. One more question for you. I did not realize that the access db in which the macro is stored is a secured access db with a username and password. Would I need to insert code to read a username and password to get the macro to kick off and would you possibly have the syntax for that or a related article explaining how to accomplish it? Thanks in advance.

Patrick