Need to Change Linked Tables in MS Access DB with Username, Password and System DB

Question

I have a front end DB, which needs to link to different back end DBs. To give you perspective, it relates to stand alone MDB files. The software in question builds a DB per company.

At the moment I am writing code within one of these MDB files.

For scalability I am now creating a new DB which will link to each MDB via code, and therefore my questions are as follows

1. How do I change the linked table location via code / VB so that the user can select the company / DB they want to work on

2. How do I do this with passing a username and password which is the same for all of the companies / DBs

3. And as per below we need to verify the username and password via the systemDB for it to open successfully.

As an FYI, this is how we open the DB on a standalone basis- "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\temp\SAMPLE.mdb" /WRKGRP "C:\ProgramData\SOFTWARE\SYSTEM.mdw" /user:username /pwd:password

Answer 1

This is not a problem at all and is absolutely able to be accomplished given the clarification that you are using a single MDW file.

To clarify Microsoft Access Workgroup Security is essentially a "session" security model that applies directly to the front-end MDB file as you open it.

Your example command line means that Microsoft Access will open the SAMPLE.MDB front-end file using the workgroup file you specified.

Once Microsoft Access has opened SAMPLE.MDB under that workgroup file, you cannot change to another workgroup file within that "session" without closing Microsoft Access and re-opening under the new workgroup file.

FYI - it IS possible to open via code, a table in another MDB using another workgroup file within that connection, but in this manner, the table is only usable in code as a RecordSet (for example), you can't make it a linked table.

Anyway, back to your real issue. How to link a different back-end set of tables for each Company.

My recommendation would be to add a few fields to your Company table that defines the filename and location of each back-end file. For example:

Notice that the location can be a UNC path, or a mapped drive path. Or maybe you don't need to define a location in the table explicitly. Maybe all of the back-ends are in the same folder, or in a definable dynamic location like \Dallas\Dallas.mdb, \NewYork\NewYork.mdb, etc. As long as you can determine the location of each back-end in some manner, then you are fine.

Now, since you will likely have "global" front-end tables, maybe also some "global" linked back-end tables i.e. Common.mdb, and your company-specific back-end tables, I would recommend having a front-end table that defines the name of each of the tables that is involved only in the company-specific files. That way, we can easily loop through just those table names and make the link changes.

For the linking code, let's say that you have prompted the User for which Company they want, and you pass the CompanyID to a re-linking function:

Public Function ChangeCompanyLinks(CompanyID As Long) As Boolean
    Dim db As DAO.Database
    Dim ldb As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rstCompany As DAO.Recordset
    Dim rstTables As DAO.Recordset
    Dim mssql As String
    Dim dbFullPath As String
    Dim retVal As Boolean

    Set db = CurrentDb()
    retVal = False
    mssql = "SELECT * FROM [tblCompany] WHERE [CompanyID] = " & CompanyID
    Set rstCompany = db.OpenRecordset(mssql, dbOpenSnapshot)
    If Not rstCompany.BOF Then
        dbFullPath = rstCompany("DBLocation") & "\" & rstCompany("DBName")
        If Dir(dbFullPath) = rstCompany("DBName") Then
            'NOTE: By opening a temporary constant link to the back-end during
            '      relinking, the relinking runs faster
            Set ldb = OpenDatabase(dbFullPath)
            mssql = "SELECT * FROM [tblLinkedTables] WHERE [FileType] = ""Company"""
            Set rstTables = db.OpenRecordset(mssql, dbOpenSnapshot)
            Do While Not rstTables.EOF
                Set tdf = db.TableDefs(rstTables("TableName"))
                tdf.Connect = ";DATABASE=" & dbFullPath
                tdf.RefreshLink
                rstTables.MoveNext
            Loop
            rstTables.Close
            ldb.Close
            retVal = True
        Else
            MsgBox "Unable to Locate Company File"
        End If
    End If
    rstCompany.Close

    ChangeCompanyLinks = retVal
    Set rstCompany = Nothing
    Set rstTables = Nothing
    Set ldb = Nothing
    Set tdf = Nothing
    db.Close
    Set db = Nothing

End Function

Obviously you will want to add error handling, and customize this a bit to fit your situation, but this code will re-link the specified tables to a new back-end.

Please note that if you eventually change to have your back-end tables in SQL Server (which I highly recommend), the re-linking code would need to be modified a bit. See this answer for more details on that.