?
Solved

How to pass arguments to Access through Shell command

Posted on 2009-02-23
10
Medium Priority
?
1,028 Views
Last Modified: 2013-11-29
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...
0
Comment
Question by:billelev
  • 5
  • 3
  • 2
10 Comments
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
ID: 23714769
Please seach the Access help for "command line arguments" (no quotes though)

You can include /CMD and retrieve it using COMMAND$
You can also run a macro with /x

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23714790
A quick example for you

"c:\program files\microsoft office\office12\msaccess.exe" "c:\My Folder\My Database.accdb" /x MyMacro /CMD Phase 2

The macro MyMacro can run a function that interegates COMMAND to determine what you want to do.

Cheers, Andrew
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 23714857
The switches are here:
http://support.microsoft.com/kb/209207


An example would be:
Dim varAccApp As Variant
varAccApp = Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe" & " " & "C:\AccessTestDatabaseAndSamples.mdb  /X mcrTestMacro")


JeffCoachman
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:billelev
ID: 23715123
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\AccessTestDatabaseAndSamples.mdb  /X mcrTestMacro")

the code tries to load a file at C:\Test.mdb!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23715289
Your closing " is in the wrong place

Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe" & " " & "C:\Test Folder\AccessTestDatabaseAndSamples.mdb"  /X mcrTestMacro)

Cheers, Andrew
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23715633
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\AccessTestDatabaseAndSamples.mdb"""

varApp = Shell("C:\Program Files\Microsoft Office 2003\Office11\msaccess.exe " & strDB & " " & "/X mcrTestMacro" & "")

Jeff
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23715758
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.exe" "c:\My Folder\My Database.accdb" /x MyMacro /CMD Phase 2

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)

Open in new window

0
 

Author Comment

by:billelev
ID: 23715906
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.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 23715932
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23717029
No sweat Andrew.
;-)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question