CODEWITHSUNDEEP
vbams-accessautoexec
JNS
JNS

Reputation: 19

How to bypass MSAccss AutoExec macro and bypass Startup form

How do I bypass the MSAccess autoexec macro and startup form for a deep-legacy code upgrade of a large MS Access 2003 application with hundreds of forms and reports?

It is an upgrade from Access 2003 to Access 2016, 2019 or 365.

This is a mission critical system kept alive and on crutches for 15 years without any VBA code updates.

The most difficult part is that many of the web pages and nearly all Microsoft pages related to this have been deleted from the web.

Current status:

Here are possible solutions based on Google searching broken out by Access version since the code/database settings in question could be specific to any Access version from 95 to 2010.

Access 2007: Opening an MS-Access database from the command line without running any of the startup vba code? Hold down shift key when opening MDB database

Access XP Open access database without executing scripts or forms

Access 2007: Emulating a SHIFT key press when using VBA to open an ms-access database secured by an mdw file?

Access XP/2003/2007? How to skip Autoexec macro when opening MSAccess from MSAccess?

MS Access keyboard short cuts for getting at the VBA code or objects in an Access database. From https://support.microsoft.com/en-us/office/keyboard-shortcuts-for-access-70a673e4-4f7b-4300-b8e5-3320fa6606e2 I haven't tried the MSAccess keyboard short cuts to see if they let me open and view the Access VBA code, toolbars, table/form dedign viewer, or switch to code editing mode. I've included them here for completeness.

Show or hide the MSAccess ribbon toolbar in VBA code. Included here for completeness. This application hides the ribbon bar on application startup. MSAccess - Minimize the Toolbar Ribbon OnLoad()?

Special case: You may get an error on the Access startup form if it has a record source which has an error. this is not the case for my application but included here completeness difficulty tracing microsoft access VBA code

Special case: You get an infinite loop of dialog prompts or errors from the startup form. Hold down the "Control-Break" key while clicking on OK for the error message to break out of the loop of errors. https://bettersolutions.com/vba/debugging/index.htm

It may be possible to break out of the main startup form to the MS Access object explorer by right clicking on the startup form's title bar or right click on the startup form's body.

Upvotes: -1

Views: 6793

Answers (6)

JNS
JNS

Reputation: 19

Access 2010?

Fourth way as mentioned above

A more complete example from the same page.

Public Sub GetCBs()
Dim db As DAO.Database
Dim strPath As String
Dim startUpform As String
Dim app As Access.Application
Dim custBars As Collection
Dim custShortCutBars As Collection
Dim custNonShortCutBars As Collection
Dim i As Integer
Dim blnAutoexec As Boolean
strPath = GetOpenFile()
'Get the db without opening in application
Set db = getDb(strPath)
'Get startupform
startUpform = getStartUp(db)
'Turn off the start up form
TurnOffStartUp db
'Check for and auto exec. If exists import and replace
If hasAutoexec(db) Then
blnAutoexec = True
ImportAutoExec (strPath)
End If
Set app = New Access.Application
'Open safely
app.OpenCurrentDatabase (strPath)
'Read command bars
Set custBars = getCustBars(app)
Set custShortCutBars = getCustShortCutBars(app)
Set custNonShortCutBars = getCustNonShortCutBars(app)
app.CloseCurrentDatabase
Set db = app.CurrentDb
Set db = getDb(strPath)
'Return start up form
TurnOnStartUp db, startUpform
db.Close
'Return auto exec
If blnAutoexec Then
    ReturnAutoExec (strPath)
End If
Debug.Print "all custom bars:"
'All bars
For i = 1 To custBars.Count
    Debug.Print custBars(i)
Next i
'Do something with the command bars
Debug.Print "all shortcut bars:"
'Short cut only
For i = 1 To custShortCutBars.Count
    Debug.Print custShortCutBars(i)
Next i
'Not short cut
Debug.Print "Non shortCut"
For i = 1 To custNonShortCutBars.Count
    Debug.Print custNonShortCutBars(i)
Next i
End Sub

Public Function getDb(strPath As String) As DAO.Database
Set getDb = DBEngine(0).OpenDatabase(strPath)
End Function

Public Function getCustBars(app As Access.Application) As Collection
' all bars
Dim col As New Collection
Dim cb As Object
For Each cb In app.CommandBars
    If cb.BuiltIn = False Then
        col.Add (cb.Name)
    End If
Next cb
Set getCustBars = col
End Function

Public Function getCustShortCutBars(app As Access.Application) As Collection
' only short cut bars
Dim col As New Collection
Dim cb As commandbar
For Each cb In app.CommandBars
    If cb.BuiltIn = False Then
    If cb.Type = msoBarTypePopup Then
        col.Add (cb.Name)
    End If
    End If
Next cb
Set getCustShortCutBars = col
End Function

Public Function getCustNonShortCutBars(app As Access.Application) As Collection
' Menu bars that are not shortcut bars
Dim col As New Collection
Dim cb As commandbar
For Each cb In app.CommandBars
    If cb.BuiltIn = False Then
    If cb.Type <> msoBarTypePopup Then
        col.Add (cb.Name)
    End If
    End If
Next cb
Set getCustNonShortCutBars = col
End Function

Public Function getStartUp(db As DAO.Database) As String
Dim prp As DAO.Property
For Each prp In db.Properties
    If prp.Name = "startupform" Then
    getStartUp = prp.Value
    Exit For
    End If
Next
End Function

Public Sub TurnOffStartUp(db As DAO.Database)
Dim prp As DAO.Property
For Each prp In db.Properties
    If prp.Name = "startupform" Then
        prp.Value = "(None)"
    Exit For
    End If
Next
End Sub

Public Sub TurnOnStartUp(db As DAO.Database, strFrm As String)
Dim prp As DAO.Property
For Each prp In db.Properties
    If prp.Name = "startupform" Then
    prp.Value = strFrm
    Exit For
    End If
Next
End Sub

Public Sub ImportAutoExec(strPath As String)
    On Error GoTo errLbl
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acMacro, "AutoExec", "AutoExecBackup"
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acMacro, "TempAutoExec", "AutoExec"
    Exit Sub
errLbl:
If Err.Number = 7874 Then
        Debug.Print "Auto Exec macro does not exist"
    Else
    MsgBox Err.Number & " " & Err.Description
    End If
End Sub

Public Sub ReturnAutoExec(strPath As String)
On Error GoTo errLbl
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acMacro, "AutoExecBackup", "AutoExec"
    DoCmd.DeleteObject acMacro, "AutoExecBackup"
    Exit Sub
errLbl:
If Err.Number = 7874 Then
        Debug.Print "Auto Exec macro does not exist"
    Else
    MsgBox Err.Number & " " & Err.Description
    End If
End Sub

Public Function hasAutoexec(db As DAO.Database) As Boolean
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name = 'AutoExec' AND MSysObjects.Type = -32766"
Set rs = db.OpenRecordset(strSql)
If Not (rs.EOF And rs.BOF) Then
    hasAutoexec = True
End If
End Function

Upvotes: 0

JNS
JNS

Reputation: 19

I have not tried to import the Access objects into a new database. (Thanks Albert Kallal for the information)

This would allow me to look at the VBA code. It may not work as a replacement for the original database with all of the settings internal to the database.

How to import the Access objects from another Access database: https://support.microsoft.com/en-us/office/import-database-objects-into-the-current-access-database-23aea08b-7487-499d-bdce-0c76bedacfdd

  • Access 365 steps (likely works for Access 2016)
  • External Data tab in ribbon
  • Click New Data Source -> From Database -> Access in the Import & Link ribbon group
  • Get External Data - Access Database window is shown
  • Browse for the MSAccess database MDB or ACCDB file in the File Name Field
  • The Import Objects window is shown
  • Select the tables, queries, forms, reports macros, modules to import
  • In the Options button dialog, you can select menus, toolbars, etc. to import
  • Click on OK
  • For Names duplicated, Access will append a 1,2,3 to the end of an imported object's name

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 48954

the other thing to check? Are you using a shortcut? if it has the /runtime swtich in it, then the shift key will be ignored NO MATTER what you do, and even if no shfit key by-pass code (to disable) shift key means the shift key will STILL be ignored. So, you want to ensure that you not launching/using a shortcut.

you also want to check/ensure/find out/be aware if the application has workgroup security. Again, in 99 out of 100 cases, the shortcut will show this.

next up: is this a mdb, or mde file? The mde file is a compiled version. No source code exists, and you can't modify the mde. So, again, ensure that you have a mdb file for the front end, not a mde. If you don't have that mdb, then you are in big trouble - you don't have the source code.

You have all this info in your post, but you leave out the most important issues. So, is this a mde, or mdb? You need to know this. Is there a worgroup security file (mdw) specifed in the link that is typical used to launch the application. If workgroup secuirty is involed, then the logon id you use might get you past shift key, but then that user might not have been given design rights, so at that point, shify key by-pass will be of zero use to get into the code.

I mean, launch your copy of access 2016 or whatever. Then try to import the objects from that database. This way you don't have to use or ever worry about shfit key, but are doing a simple import of the forms, reports and code into a brand new fresh database.

So, another question: Don't bother launching the application - create a blank new database, and then import from the existing - can you do this? (doing this does NOT copy the shift key setting of the original database).

Upvotes: 2

JNS
JNS

Reputation: 19

MSAccess command line lets you tell it what macro to execute on startup.

I ran the following cmd.exe command line which generates multiple errors and allows you to get into the Access database with the navigator and get into the VBA code. Not the best solution but one possibility.

MSAccess.exe DB /X ADEEERETDEREAR

DB is the full path to the Access database ADEERETDEREAR is a macro which does not exist

Upvotes: 1

JNS
JNS

Reputation: 19

Access 2010?

  • Reset startup form to nothing in VBA code

  • Code from 2012 is here: https://www.tek-tips.com/viewthread.cfm?qid=1673392

  • First way

    Dim strOriginalForm as String
Dim db as Database

Sub RemoveStartup()
  Set db = OpenDatabase(yourdatabase)
  strOriginalForm = db.Properties("StartUpForm") 
  db.Properties("StartUpForm") = "(none)"
  db.Close
  set db = Nothing
End Sub

Sub ResetStartup()
  Set db = OpenDatabase(yourdatabase)
  db.Properties("StartUpForm") = strOriginalForm
  db.Close
  Set db = Nothing
End Sub

Second way

Set prp = db.CreateProperty("AllowByPassKey", dbBoolean, True)
db.Properties.Append prp

Third way Delete the property using - database.properties.delete propertyname

A more complete example from the same page exists.

Upvotes: 0

JNS
JNS

Reputation: 19

Access 2007? How to disable Macro and Start-Up values while opening the MS Access DB

Access 2003?

Access 2007:

Access 2010?

Access ?

Access ?

  • https://bytes.com/topic/access/answers/211664-programatically-set-startup-form
  • A guess that you could use VBA in one Access database to open the target database
  • Get the name of the startup form
  • Change the startup form's name or maybe blank out the startup form's name
  • VBA code similar to CurrentDB.Properties("StartupForm") = "MyForm"
  • Another guess would be to blank out the startup form's name in the database properties
  • Same may work for the autoexec macro

Upvotes: 0

Related Questions