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

Question

I want to run recursively through a directory of *.mdb files and search them to see which ones have a specific linked table.

These files are secured using several *.mdw files. I did not write any of them, but I am their maintainer.

I've found a way to do this, but it's too interactive; I need it to be non-interactive; since some of these *.mdbs I'm searching use an Autoexec macro.

From what I understand executing an Autoexec macro can be avoided if one holds the SHIFT key while opening them; however I use the command line in my macro to open these files, and there doesn't seem to be a way to hold the shift key.

I've found another example, which does allow you to hold down the shift key to avoid the Autoexec macro (see Bypassing Startup Settings When Opening a Database), but which does not allow you to unlock the database with an *.mdw file, because the OpenCurrentDatabase() method does not have a parameter for an *.mdw file

Answer 1

If your goal is simply to check whether a db file contains a specific linked table, you can use the ADO OpenSchema method. With that approach, you don't need to open the db file in an Access application session, so the AutoExec macro does not run.

Below is an example using late binding. I left comment notes in case you prefer early binding. Change the Provider if your Access version is older than 2007.

Since you're using Access user-level security, you will also have to adapt the connection string to include the path to your MDW and supply the Access security user name and password. Here is an example connection string (using the Jet 4 provider) from ConnectionStrings.com. I split the single-line string on the semicolons for readability:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\mydatabase.mdb;
Jet OLEDB:System Database=system.mdw;
User ID=myUsername;
Password=myPassword;

Public Function HasLinkedTable(ByVal pDb As String, _
        ByVal pTable As String) As Boolean

    Const adSchemaTables = 20&
    Dim cn As Object ' ADODB.Connection
    Dim rs As Object ' ADODB.Recordset
    Dim strConnect As String
    Dim blnReturn As Boolean

    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & pDb & ";"
    'Set cn = New ADODB.Connection
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strConnect

    Set rs = cn.OpenSchema(adSchemaTables)
    With rs
        Do While Not .EOF
            If !TABLE_NAME = pTable And !TABLE_TYPE = "LINK" Then
                'Debug.Print !TABLE_NAME, !TABLE_TYPE
                blnReturn = True
                Exit Do
            End If
            .MoveNext
        Loop
        .Close
    End With
    cn.Close
    Set cn = Nothing
    HasLinkedTable = blnReturn
End Function