billelev
asked on
How to pass arguments to Access through Shell command
I would like to be able to start and run an MS Access database using the Shell function, but with arguments passed to the Access database.
I am pretty sure I have done this in the past, whereby the argument passed to the database upon loading determined how the database operated, but I cannot remember how Access accepts these arguments...
I am pretty sure I have done this in the past, whereby the argument passed to the database upon loading determined how the database operated, but I cannot remember how Access accepts these arguments...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great, thanks. It's helpful to know about the macro/switches options also.
I seem to have trouble if the file path name has spaces in it. Is there a way to get around this?
For example, if I try the following:
Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe " & " " & "C:\Test Folder\AccessTestDatabaseA ndSamples. mdb /X mcrTestMacro")
the code tries to load a file at C:\Test.mdb!
I seem to have trouble if the file path name has spaces in it. Is there a way to get around this?
For example, if I try the following:
Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe
the code tries to load a file at C:\Test.mdb!
Your closing " is in the wrong place
Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe " & " " & "C:\Test Folder\AccessTestDatabaseA ndSamples. mdb" /X mcrTestMacro)
Cheers, Andrew
Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe
Cheers, Andrew
Try TR's (Andrew's) first.
Mine is kinda wacky, but it works.
:-O
Dim varApp As Variant
Dim strDB As String
strDB = """C:\Test Folder\AccessTestDatabaseA ndSamples. mdb"""
varApp = Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe " & strDB & " " & "/X mcrTestMacro" & "")
Jeff
Mine is kinda wacky, but it works.
:-O
Dim varApp As Variant
Dim strDB As String
strDB = """C:\Test Folder\AccessTestDatabaseA
varApp = Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe
Jeff
Jeff, I spotted the " in the wrong place but did not include the " in the string required by the shell from your example, my earlier example demonstrated what the string should look like not how to build it.
The other thing you can / should do is not assume MSACCESS.EXE is in a specific folder. The code below is how I would calculate it
"c:\program files\microsoft office\office12\msaccess.e xe" "c:\My Folder\My Database.accdb" /x MyMacro /CMD Phase 2
Cheers, Andrew
The other thing you can / should do is not assume MSACCESS.EXE is in a specific folder. The code below is how I would calculate it
"c:\program files\microsoft office\office12\msaccess.e
Cheers, Andrew
dim strProgram as string
dim strDB as string
dim strShell as string
dim varapp as variant
strProgram = Chr(34) & syscmd(acSysCmdAccessDir) & "MSACCESS.EXE" & Chr(34)
strDB = Chr(34) & "c:\My Folder\My Database.accdb" & Chr(34)
strShell = strProgram & " " strDB & " /x MyMacro /CMD Phase 2"
varApp = Shell(strShell)
ASKER
Is syscmd(acSysCmdAccessDir) a general VBA function? It won't compile. But I agree, some kind of code to determine the location of an *.exe file is essential for this to be flexible.
Yes it is built into the Access.Application, you can test it by typing in ?syscmd(acSysCmdAccessDir) in the immediate window. Also Check test ?Chr(34)
Cheers, Andrew
Cheers, Andrew
No sweat Andrew.
;-)
;-)
"c:\program files\microsoft office\office12\msaccess.e
The macro MyMacro can run a function that interegates COMMAND to determine what you want to do.
Cheers, Andrew