4

Is there a way to open an MS-Access 2003 database from the command line without running any of the startup vba code or displaying any errors?

I looked at the command line arguments for MS Access and there doesn't seem to be one for specifying that you want none of the vba code to execute on startup.

I'm using the following code to open up a database in a separate vba database: 

Sub test()


Dim accObj As Access.application, Msg As String
Dim application As String, dbs As String, workgroup As String
Dim user As String, password As String, cTries As Integer
Dim x

Dim theDB As Database

' This is the default location of Access
application = "C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE"

' Use the path and name of a secured MDB on your system
dbs = "C:\ucpdatas\awashic-pc\APLReporting.mdb"

' This is the default working group
workgroup = "E:\Tickets\CSN_NotSure\Secured.mdw"
user = "aleer"
password = "****"

Debug.Print application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbMinimizedFocus

x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbMinimizedFocus)


On Error GoTo WAITFORACCESS
Set accObj = GetObject(, "Access.Application")

' Turn off error handling
On Error GoTo 0

' You an now use the accObj reference to automate Access
Debug.Print "Access is now open."

' Do Stuff...

accObj.CloseCurrentDatabase
accObj.Quit

' Close it out...
Set accObj = Nothing
Debug.Print "Closed and complete."

Exit Sub

WAITFORACCESS: ' <--- this line must be left-aligned.
' Access isn't registered in the Running Object Table yet, so call
' SetFocus to take focus from Access, wait half a second, and try again.
' If you try five times and fail, then something has probably gone wrong,
' so warn the user and exit.

'SetFocus

If cTries < 5 Then
   cTries = cTries + 1
   Sleep 500 ' wait 1/2 seconds
   Resume
Else
   Debug.Print "It didn't work"
End If

End Sub

This line...
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbMinimizedFocus)
Turns out to be...
C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE "C:\ucpdatas\awashic-pc\APLReporting.mdb" /nostartup /user aleer /pwd *** /wrkgrp "E:\Tickets\CSN_NotSure\Secured.mdw" 2 ... at the command line.

But when the database opens it executes a bunch of vba codes and displays error messages.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
leeand00
  • 25,510
  • 39
  • 140
  • 297
  • 2
    Are you able to tweak the AutoExec macro in the database you're trying to open? Perhaps have it look for a registry value or environment variable that you can set before you open it "non-interactively", and abort the AutoExec macro if that value exists? – Gord Thompson May 20 '13 at 20:27
  • @GordThompson You mean like this? http://office.microsoft.com/en-us/access-help/create-an-access-user-profile-HP005188988.aspx – leeand00 May 21 '13 at 02:12
  • Something along those lines, yes. I wasn't thinking specifically of an "Access Profile", just some value that your code could create before using `Shell()` to launch Access. The AutoExec macro in Access could run some VBA that checks for the value to determine whether it was launched by a human, or launched by your code. – Gord Thompson May 21 '13 at 09:44
  • Consider the `/cmd` command-line switch. Have the AutoExec macro check the value returned by the `Command()` function to see whether `/cmd` was used. Abort AutoExec as appropriate based on `Command()`. – HansUp May 21 '13 at 14:04
  • @HansUp I don't want to modify each and every file I need to go through, it would defeat the purpose of writing the script. – leeand00 May 21 '13 at 15:41

2 Answers2

3

There is no way for Access to open without running the AutoExec macro associated with that database. The only solution would be to have the AutoExec contain conditional arguments that determined how the database was opened, and not run the commands if the database was shell'd. This would require editing every database to include this logic.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
1

Technically, yes there is a way to open an MS-Access 2003 database from the command line without running any of the startup macros, although it does not involve the command line arguments: If you hold down the Shift key while the database opens, it will not run the AutoExec script (and suppresses a few other things). This also assumes the AllowBypassKey property has not been set to False.

See Ignore startup options

GlennFromIowa
  • 1,616
  • 1
  • 14
  • 19
  • The OP asked if you can open the database **using the command line** and bypass AutoExec. Your answer is relevant for opening a DB from Windows Explorer, not from a command line. – Johnny Bones Jan 06 '15 at 23:39
  • @Johnny Yes, this method also works while using the command line (testing before posting helps). The OP stated, "I don't want to modify each and every file I need to go through." Since a solution had not been proposed using command line arguments, this method, though not what he originally asked, would be a less time-consuming way to accomplish the task than modifying all the files. It _is_ a solution **using the command line**, and it _does_ accomplish what the OP asked. Your _solution_ doesn't directly involve the command line either, but I didn't down vote your answer. – GlennFromIowa Jan 07 '15 at 07:47