Link the tables of a front end per profile

In this tutorial, you configure a front end with one SQL Server connection in two profiles, DEV and TEST, add a table, link it with the designer, and make the application link its tables every time it starts. It takes about fifteen minutes.

Prerequisites

Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.23 or later installed and registered for the current Windows user (How to: Install the released package).
The WKL Extensions add-in installed, so that Database Tools › Add-Ins offers WKL Linked Tables Manager: tools\install-addin.ps1 in the release package (1.55 or later) installs addin\WklExtensions.accda for the current user. Without the add-in, WklLinkManager.ShowDesigner opens the same designer from VBA.
Microsoft ODBC Driver 18 for SQL Server, in the same bitness as Access.
Two SQL Server databases that you can open with your Windows sign-in, here SalesDev and SalesTest on the server sql01, each with a table dbo.Customer. Any server works, including SQL Server Express LocalDB ((localdb)\MSSQLLocalDB).
A front end (ACCDB file) in a trusted location.

Steps

1. Open the designer

Open the front end and choose Database Tools › Add-Ins › WKL Linked Tables Manager. The designer opens for the open database. It doesn't create anything yet; the configuration tables are created the first time you save.

2. Add the connection for DEV

On the Connections page, click New connection and fill in the row:

Column	Value
Name	`Sales`
Driver	`ODBC Driver 18 for SQL Server` (the column suggests the installed drivers)
Server	`sql01`
Database	`SalesDev`
Schema	`dbo`
Parameter	`Trusted_Connection=Yes;Encrypt=No`
Profile	`DEV`

Select the row and click Test connection. The message below the toolbar tells you whether the database opens; no sign-in dialog box appears. Click Apply to save.

3. Create the TEST profile

On the Profiles page, select the DEV row in the list, click Copy profile …, enter TEST, and click OK. The new profile has a copy of every DEV connection row. Back on the Connections page, choose TEST in the Profile filter. Then, in the Apply to all bar, set Database to SalesTest for all rows shown and click Apply. With many connections, this single step moves all of them; the same works for the driver, server, and schema. Click Apply in the designer to save.

4. Add the table

On the Tables page, click Add remote tables …, choose the Sales connection, select dbo.Customer, and click Add. A row with the local name Customer appears; change it to tblCustomer. The Status column shows that the table isn't linked yet.

5. Link the tables

On the Check and synchronize page, choose the DEV profile and click Check: the list shows tblCustomer as missing. Click Refresh all, and then click OK. The navigation pane shows tblCustomer as a linked table that reads from SalesDev.

6. Link at every startup

Create a standard module with this function:

Public Function StartUp()
    Dim links As New WklLinkManager
    On Error GoTo Failed
    ' The profile of this installation: from the command line (/cmd TEST), else DEV.
    links.EnsureLinks IIf(Len(Command()) > 0, Command(), "DEV")
    Exit Function
Failed:
    MsgBox "The tables couldn't be linked:" & vbCrLf & links.LastMessage, vbExclamation
End Function

If the WKL Forms Object Library reference isn't set, add it (Tools › References in the VBA editor). Then create a macro named AutoExec with the RunCode action and the function name StartUp().

7. Switch the profile

Close the front end and start it with /cmd TEST, for example from a shortcut:

"%ProgramFiles(x86)%\Microsoft Office\root\Office16\MSACCESS.EXE" "%LOCALAPPDATA%\Apps\Sales.accdb" /cmd TEST

Expected result

When the front end starts with /cmd TEST, tblCustomer is linked to SalesTest; without /cmd, it's linked to SalesDev again. A second start in the same profile changes nothing, because the links already match. The designer's Profiles page shows the profile the tables are linked with.