Running Macro through DAO connection

[MrBread]

Hey Everyone,

I'm working in Access 2000 trying to automate the creation of some data between two databases. I'm trying to execute a macro in another Access database when a user clicks a command button. My DAO knowledge is limited at best but I've searched all over this forum to gather as much knowledge as possible. I believe once you establish a DAO connection between the two databases you should be able to execute the macro as if it were in the current database but that's not working out for me.

Here's my code I'm using:

Dim dbExt As DAO.Database
Dim qdf As DAO.QueryDef
Dim strDbName As String

strDbName = "Mark To Market.mdb"
Set dbExt = OpenDatabase(strDbName, False, False, ";PWD=password")
Set qdf = dbExt.QueryDefs("MTM - CDSdata - 10 - All CDS after given date")
qdf.Parameters("anchordate") = Me.Text12.Value
qdf.Execute
DoCmd.RunMacro ("MTM - Get CDScurve")

After clicking the button a make-table query is executed in the other database and this works fine but when I comment out that section just to try the macro execution I get the following error:

Run-time error '2485':
Microsoft Office Access can't find the macro 'MTM - Get CDScurve.'

The macro does exist and I've tried another macro but same error. I'm kind of at lost to why it's not working.

Thanks for any help!

Ryan

 

[ddiamond]

DoCmd always runs from the current database. I'm not sure how to do what you want through DAO. But you can do it with the shell command:
ret = Shell(strPath & strDBName & " /X MTMGetCDScurve")
I don't think the macro name can have spaces. Also you would need to add quit to the end of your macro.

 

[Remou]

Perhaps something like:

    ' Initialize string to database path.
    strDB = _
        "C:\Docs\Tek-Tips.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB
    ' RunMacro
    appAccess.DoCmd.RunMacro ("MTM - Get CDScurve")
'Unhide, if required
    'appAccess.Visible = True

 

[MrBread]

Thanks for the help. That did the trick.

 

[AvGuy]

MrBread:

Any idea how to do this in a new workspace with a runtime version of Access which won't support the CreateObject method?

AvGuy