Run a macro in Access from excel

[Vigneshrs87]

Hi,

I have some code in Module 1 of the vba editor of access as follows.

sub Hi ()
code here....
end sub

I need to call this from excel how am i supposed to do that.

Thanks for your help.

 

[boblarson]

Dim objAcc As Object
 
Set objAcc = CreateObject("Access.Application")
 
objAcc.OpenCurrentDatabase("C:\Temp\MyDb.mdb") 
 
objAcc.Run("Hi")
 
objAcc.Quit
Set objAcc = Nothing

 

[Vigneshrs87]

Hi Boblarson,

Thank you so much for your reply, The code is working fine.

Is there a way to call the macro without creating an object im uding an ADODB connection to connect to Access so i thought there might be some other way like using the Command.execute funtion or something.

Im a newbie to access any advice would be appreciated.

 

[boblarson]

No, you must create the object, but if you need it to stay open you can release the object by using

objAcc.UserControl = True

then the code is completely released from it and you can set it to nothing and not affect it. If you don't need that but need it to finish the macro before closing, you can probably use DoEvents to let it do it first.

 

[Vigneshrs87]

Thanks Boblarson.