How to Run a Macro from a different db

[PatSalmon]

I have my system split, data and system. I would like to run a macro that is in the "data" db from the "system" db. How do I reference the "data" db?

Thanks

 

[rgbanse]

Pat,
There are a couple of options:
From a macro in your "Local" DB
Select RunApp in the Action column
the enter the command line as below.
Keep in mind that my paths are not going to be the same as yours.
"PathToMSAccess" PathToRemoteDB /x RemoteMacroName
Example:
"C:\Program Files\Microsoft Office\Office\msaccess.exe" \\Srv01001\User_Apps\MRP2024_Ver_011700_MPS.mdb /x MPS2004_start

The other method would be in a module

Function RunRemoteMacro()
Path requirements would be the same.

Call Shell("""C:\Program Files\Microsoft Office\Office\msaccess.exe"" \\Srv01001\User_Apps\MRP2024_Ver_011700_MPS.mdb /x MPS2004_start", 1)

End Function

RGB

 

[PatSalmon]

I have copied my command line from the macro in my local db.

"C:\Program Files\Microsoft Office\Office\msaccess.exe" \\zhast011\hast\dept\Project Management Finance\Projects dB\ProjectsData.mdb /x mcrImportOpShop

When I run this I get the following message 3 times:

"The command line you used to start Microsoft Access contains an option that Microsoft Access doesn't recognize. Exit and restart Microsoft Access using valid command line options." "OK" or "Help"

I click OK, the message comes back again. After the third OK Access opens but another error.

Microsoft Access can't find the database file:
"\\zhast011\hast\dept\project.mdb"

Do you see anything I'v done wrong??

Thanks
Pat

 

[rgbanse]

Pat,
I suspect it might be the DB path
The \\Srv01001\ is a network server.
If your DB is local try
"C:\Program Files\Microsoft Office\Office\msaccess.exe" DriveLetter:\YourPath\DBName /x MacroName
Hope this is it
RGB

 

[PatSalmon]

My dB is on the network. Will this not work if is residing on the network?


Thanks
Pat

 

[rgbanse]

Pat,
You CL looks good - yes it does work on a network.
That's where most of mine run.
With RunApp action in the macro, try going in sections to ident the problem. ie:
RunApp - Command Line:"C:\Program Files\Microsoft Office\Office\msaccess.exe"
This should open Access - if it doesn't verify path
Next add the DB path and name.
This should open you DB - if it doesn't verify path
Finally add the /x MacroName
Hope it works - that's all I can offer.
RGB

 

[DougP]

try putting the database name in double quotes
"\zhast011\hast\dept\Project Management Finance\Projects dB\ProjectsData.mdb"
Anytime there is a space in a folder name you must enclose the whole thing in double quotes

Also I break apart the command like so
Dim ProgramName , DatabaseName , MacroName as string
Dim x as variant
ProgramName = "C:\Program Files\Microsoft Office\Office\msaccess.exe"
DatabaseName = "\zhast011\hast\dept\Project Management Finance\Projects dB\ProjectsData.mdb"
MacroName = "MyMacro"

x= Shell(ProgramName & " " & DatabaseName & " /X " & MacroName,1)

Also very important when using Access databases on a network:
See my 2 FAQ's on MDE'e
faq181-91 What is an .MDE and why do I need it?
faq181-90 How do I make an .MDE file DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[PatSalmon]

The double quotes did the trick. Thanks so much. One last question, how do I close this db after the macro is finished?

Thanks
Pat