Pfthb

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 OfficerootOffice16MSACCESS.EXE" "C:tempSAMPLE.mdb" /WRKGRP "C:ProgramDataSOFTWARESYSTEM.mdw" /user:username /pwd:password

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 DallasDallas.mdb, NewYorkNewYork.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.