Opening Access Db without VBA code running

Question

I am building a tool which extracts and compares VBA code modules between 2 versions of the same database. The tool is being built in Excel VBA.

Unfortunately these databases tend to have message boxes which appear on screen when opened, and usually prompts to compact the database on closure. This means that when I attempt to open the databases to pick up the VBA code the Excel VBA code hangs until the Access message boxes have been responded to.

However, strangely I have found that when I step through the Excel VBA code to open the databases the on open and close messages do not appear, and I am able to extract all VBA code modules without any problem.

Is there any way that an Access database can be opened like this without the Access VBA code firing, but without me having to step through the code in Excel? The code I am using to connect to the databases is:

Set dbLatest = New Access.Application
dbLatest.OpenCurrentDatabase LatestDatabasePath
Set projLatest = dbLatest.VBE.ActiveVBProject

Set dbPrevious = New Access.Application
dbPrevious.OpenCurrentDatabase PreviousDatabasePath
Set projPrevious = dbLatest.VBE.ActiveVBProject

score 2 · Accepted Answer · edited May 23 '17 at 11:51

Thanks to dmc2005's answer on this thread I managed to solve this by calling OpenDatabase on a DAO DBEngine, which doesn't fire any on open events, but allows me to temporarily disable the start up form though the StartUpForm property. I then extract my VBA code and re-apply the start up form.

Disabling start up forms:

' Disable start up forms, but store names so they can be re-applied
Set dbe = New DBEngine

Set db = dbe.OpenDatabase(LatestDatabasePath)
On Error Resume Next
strStartUpFormLatest = db.Properties("StartUpForm")
On Error GoTo ErrorTrap

If Not strStartUpFormLatest = "" Then _
    db.Properties("StartUpForm") = "(None)"
db.Close

Set db = dbe.OpenDatabase(PreviousDatabasePath)
On Error Resume Next
strStartUpFormPrevious = db.Properties("StartUpForm")
On Error GoTo ErrorTrap

If Not strStartUpFormPrevious = "" Then _
    db.Properties("StartUpForm") = "(None)"
db.Close

And reapplying them once I've finished:

' Reapply StartUpForms
If Not strStartUpFormLatest = "" Then
    Set db = dbe.OpenDatabase(LatestDatabasePath)
    db.Properties("StartUpForm") = strStartUpFormLatest

    db.Close
End If

If Not strStartUpFormPrevious = "" Then
    Set db = dbe.OpenDatabase(PreviousDatabasePath)
    db.Properties("StartUpForm") = strStartUpFormPrevious

    db.Close
End If

Fortunately I don't have any AutoExec macros to worry about, but it seems with a bit more effort something similar could be done with these too.

score 0 · Answer 2 · edited Oct 29 '14 at 15:29

 Dim Suffix As String
    Dim MyFileName As String


    MyConn2 = MyDBPath & "CompactDB.accdb"
    MyFileName = Left(MyDBFile, Len(MyDBFile) - 6)
    Suffix = ".laccdb"

    strLckFile = MyFileName & Suffix


'this is to compact the database

        Set objEngine = CreateObject("DAO.DBEngine.120")

        Set objFSO = CreateObject("Scripting.FileSystemObject")
        If Not (objFSO.FileExists(strLckFile)) Then
'            If (objFSO.FileExists(strBackup)) Then
'                objFSO.DeleteFile strBackup
'            End If
            If (objFSO.FileExists(MyConn2)) Then
                objFSO.DeleteFile MyConn2
            End If
            'objFSO.CopyFile strSrcName, strBackup

            ''dbVersion120 = 128
            objEngine.CompactDatabase MyConn, MyConn2, , 128

            objFSO.DeleteFile MyConn
            objFSO.MoveFile MyConn2, MyConn
        End If 'LckFile

Opening Access Db without VBA code running

2 Answers2