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

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
How do I do this with passing a username and password which is the same for all of the companies / DBs
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

why not just removing the security from your different back-ends? It is phased out, anyway! — iDevlop, Nov 08 '18 at 15:40
we do not have the permission access to do that unfortunately. Already tried it. Thanks — Simon Levy, Nov 08 '18 at 15:42
permission: you mean business permission, or technical one ? did you loose the admin password ? — iDevlop, Nov 08 '18 at 16:20
Afaik this is plain impossible, workgroup membership is shared throughout the Access application. You can't have a database with linked tables from different workgroups. You can open up a table through VBA, though, but it requires some complex code and undocumented features, and I highly recommend you don't, since this can make the application hard-crash in certain cases. — Erik A, Nov 08 '18 at 18:01
Did i understand right, 1. you have many Back-End databases with many systemDB 2. you want one Front-End with ability to connect to any of Back-Ends 3. ALL tables in Front-End must be linked to the same Back-end ? — 4dmonster, Nov 09 '18 at 06:08
It is one system DB, with multiple back end DBs which we need the ability to repoint with a click of a button. We have the username and password but it needs to used at all times like in the example above which does open the DB. Without it it will not open and does not even ask for a password. We cant change the password as it tells us we dont have permission within access so clearly cleverly restricted. — Simon Levy, Nov 09 '18 at 09:11
@SimonLevy now it looks like all you need is to change path inside `Connect` property of every linked table to new new location. — 4dmonster, Nov 09 '18 at 10:14

score 2 · Answer 1 · answered Nov 09 '18 at 15:17

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.

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

1 Answers1