Starting MS Access Database from script or batch file

[Dryheat]

I'm just beging to learn how to use .bat and .vbs files to automate some processes. I can see in the Access help files that you can use command line switches to open an Access database in exlusive mode, sign on as a particular user, enter the password and compact/repair database. What I need to do is create a script or batch file that will do this automatically.

Dry

 

[alfalf]

HEre's example:

C:
CD\Program Files\Microsoft Office\Office
Msaccess.exe C:\Northwind.mdb /switch

Or

You can use desktop target syntax for desktop shortcuts like:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\folder\Your.mdb" "C:\windows\system32\system.mdw"

SWITCHES for .bats

/decompile Undocumented command line. Will sometimes remove old code and objects from the database and make it faster. Read More
/excl Opens the specified Access database for exclusive access. To open the database for shared access in a multiuser environment, omit this option. Applies to Access databases only.
/ro Opens the specified Access database or Access project for read-only access.
/user user name Starts Access by using the specified user name. Applies to Access databases only.
/pwd password Starts Access by using the specified password. Applies to Access databases only.
/profile user profile Starts Access by using the options in the specified user profile instead of the standard Windows Registry settings created when you installed Microsoft Access. This replaces the /ini option used in versions of Microsoft Access prior to Access 97 to specify an initialization file.
/compact target database or target Access project Compacts and repairs the Access database, or compacts the Access project that was specified before the /compact option, and then closes Access. If you omit a target file name following the /compact option, the file is compacted to the original name and folder. To compact to a different name, specify a target file. If you don't include a path in target database or target Access project, the target file is created in your My Documents folder by default.
In an Access project, this option compacts the Access project (.adp) file but not the Microsoft SQL Server database.

/repair Repairs the Access database that was specified before the /repair option, and then closes Microsoft Access. In Microsoft Access 2000 or later, compact and repair functionality is combined under /compact. The /repair option is supported for backward compatibility.
/convert target database Converts a previous-version Access database or Access project to the default file format, renames the new file, and then closes Access. You must specify the source database before you use the /convert option. To view the default file format, click Options on the Tools menu, and then click the Advanced tab.
/x macro Starts Access and runs the specified macro. Another way to run a macro when you open a database is to use an AutoExec macro.
/cmd Specifies that what follows on the command line is the value that will be returned by the Command function. This option must be the last option on the command line. You can use a semicolon ( as an alternative to /cmd.
Use this option to specify a command-line argument that can be used in Visual Basic code.

/nostartup Starts Access without displaying the task pane (the second dialog box that you see when you start Access).
/wrkgrp workgroup
information file Starts Access by using the specified workgroup information file. Applies to Access databases only.


Notes

To run a Visual Basic for Applications procedure when you open a database, use the RunCode action in the AutoExec macro or in the macro that you run by using the command-line option /x. You can also run a Visual Basic procedure when you open a database by creating a form with a Visual Basic procedure defined for its OnOpen event. Designate this form as the startup form by right-clicking the Database window, clicking Startup, and then entering that form in the Display Form/Page box.
To specify a forward slash (/) or semicolon ( on the command line, type the character twice. For example, to specify the password ;mjs/md on the command line, type ;;mjs//md following the /pwd command-line option.

 

[Dryheat]

I really appreciate all the helpful information. Much of this I was able to glean from the MS Access help files.
Perhaps I should have posted this on the vbScript forum and gone into a little more detai.
My goal is to able to do this from a vbscript using the object.Run method. Using this method gives me access to feedback from the application, ie.. error codes, but only if I set the third argument to True. I can get the program to startup and I can send keystrokes to navigate menus to accomplish this compact/repair database function:
-------------------------------------------------------
dim varDate
' set varDate to today's date
varDate = Date()
set WshShell = WScript.CreateObject("WScript.Shell")
' open pricing.mdb in MS Access maximized
wshshell.run "C:\scriptest\pricing.mdb", 3, False
' enter password and press enter
WshShell.SendKeys "password"
WshShell.SendKeys "~"
' send keystrokes to navigate menu, compact/repair database
WshShell.SendKeys "%"
WshShell.SendKeys "t"
WshShell.SendKeys "d"
WshShell.SendKeys "c"
' send keystrokes to navigate menu, close program
WshShell.SendKeys "%"
WshShell.SendKeys "f"
WshShell.SendKeys "x"

' create message box to inform user
MsgBox "Backing up file and renaming to " & varDate
-----------------------------------------------------------
But if I set the third argument in the object.Run method to True the script stops running before entering the keystrokes. According to the reference section on Microsoft's MSDN website I should be able to pass command line switches in the first argument of object.Run, thereby excluding the need to send keystrokes. But I haven't been able to get it to work.
Example:
-----------------------------------------------------------
dim varShell

Set varShell = CreateObject("WScript.Shell")
varShell.Run "C:\scriptest\pricing.mdb /user dave", 3, True
-----------------------------------------------------------
While the above code opens the database it does not pass the user "dave" to the program.

Dry

 

[PHV]

What happen if you type the following in a console window ?
C:\scriptest\pricing.mdb /user dave

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Sorry, hit submit to fast.
What happen if you type the following in a console window ?
C:\scriptest\pricing.mdb /user dave

You'll discover that the command line switch is not interpreted.
In order to use command line options you have to launch the msaccess executable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Dryheat]

Yes, I found this to be the case. I typed the following into a cmd prompt window and it worked:
_______________________________________________________
"C:\Program Files\Microsoft Office\Office\msaccess C:\scriptest\pricing.mdb" /user dave

Problem is when I type this into a vbscript I get a run time error of 'The system can not find the file specified'.
__________________________________________________________
dim varShell
Set varShell = CreateObject("WScript.Shell")
varShell.Run "C:\Program Files\Microsoft Office\Office\msaccess C:\scriptest\pricing.mdb /user dave", 3, False
_________________________________________________________

Dry

 

[PHV]

Try this:
varShell.Run """C:\Program Files\Microsoft Office\Office\msaccess"" C:\scriptest\pricing.mdb /user dave", 3, False
Or this:
varShell.Run Chr(34) & "C:\Program Files\Microsoft Office\Office\msaccess" & Chr(34) & " C:\scriptest\pricing.mdb /user dave", 3, False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244