Is it possible to start an Access macro from Excel using an ADODB.connection?

[doasidont]

From Excel 2007, I'm connected to an Access database via ADODB.Connection. From here, I've retrieved some data from the database into a worksheet. Now I'd like to issue a command to the database to start one if its macros. This macro calls a function that is a VBA module. Can this be done? I know I can issue a DoCmd if I do a 'CreateObject("Access.Application") and a 'OpenCurrentDatabase', but I'm already connected.

Thanks.

[vbfbryce]

If I have the database open already, this seems to do it:

Public Sub getAcc()
    Dim oAcc As Access.Application
    
    Set oAcc = GetObject("c:\yourPath\vbmain.accdb")   'change to your path
    oAcc.DoCmd.RunMacro "macRanFromExcel"   'my macro name
    Set oAcc = Nothing
End Sub

I opened it manually first, not through code. Not sure if that will make a difference or not.

PS: Add reference to Microsoft Access XX.X Object Library first.

[doasidont]

Thanks. Your code is similar to what I mentioned in my initial post (Get/CreateObject) and it works standalone, but it doesn't work for me if I am connected to database via ADODB.Connection. I'm trying to find out if it's possible to start a database macro while I am connected to the database; i.e., I am not starting Access and I am not actually opening the file that is the database.

[TnTinMN]

I believe the solution proposed by vbfbryce is your only avenue to invoke a macro. The ADODB connection will be using the provider you specify in your connection string; Jet or more likely ACE. These providers are designed only for database functionality and are not intended to provide application specific functionality like macros.

You can view the functionality provided by the ACE provider (ACEDAO.DLL) at:

Microsoft Data Access Objects Reference

[doasidont]

Thanks. That's what I am beginning to understand.