WklLinkManager

WklLinkManager keeps the linked ODBC tables of an Access front end in line with a configuration stored in the front end itself, per profile such as DEV, TEST and PROD. The application calls it at start to link its tables to the right sources; developers maintain the configuration in a designer that the add-in WKL Extensions opens from the Access ribbon.

• Link the tables of a front end per profile

• How to: Move all connections to another server
• How to: Take over the links a front end already has
• How to: Choose how linked tables log in

• Configuration in two local tables: connections in USysConnection, tables in USysLinkedTable; Access hides both in the navigation pane, and they travel with the front end into the ACCDE.
• Profiles: the same connection name has one row per profile; a row without profile serves every profile. One call switches all tables to another profile.
• Check before change: Check lists every difference (missing, other source, other remote table, local table in the way, no connection, unmanaged link) without changing anything.
• Repair at start: EnsureLinks creates missing links and repairs wrong ones; tables that already match are not touched. RelinkAll and Relink link anew on request.
• Links keep their properties: a new connection is set on the existing link, so descriptions, formats and lookups of the linked table stay.
• Never destroys data: a local table with a configured name is never replaced; a link that needs to be recreated is first created under a temporary name.
• Credentials as in Access: Windows login, a stored password or a prompt by the driver, through the connection's parameters. A stored password is clear text in USysConnection and in every link, also in an ACCDE; prefer the Windows login.
• Without dialogs: connection tests and the list of remote tables never show the login dialog of a driver.
• Designer with the pages Connections (“Apply to all” for the driver, server, database, and schema), Tables, Profiles, and Check and synchronize; opened from the add-in or with ShowDesigner.
• Existing front ends: ImportLinks (in the designer, Take over existing) takes the ODBC links a front end already has into the configuration.

The configuration is data of the front end. The tables to link and their sources are rows, not code. A deployment changes rows (or only the profile the application names), never VBA. The fields of both tables are described in the class remarks of the reference.

A connection is a name; the profile picks the row. USysLinkedTable.ConnectionID points to a connection row, but what counts is its Name. For profile TEST the manager uses the row of that name with Environment = TEST, or the row of that name without profile. So adding a profile means adding connection rows, not touching the tables.

Which profile. An operation uses the profile passed as argument, else the Environment property, else the profile the tables are linked with now (DetectEnvironment). When there are profiles and the tables fit none of them clearly, the manager raises an error instead of guessing, so an application never links to production by accident.

Compare, then change only what differs. Every operation first builds the wanted connect string of each enabled table and compares it with the link in the front end. Keywords that drivers or Access add on their own are ignored; a stored password counts. Only differing tables are written, so EnsureLinks at every start costs one comparison.

The designer works on a copy. Edits stay in the window until OK or Apply; then the configuration is checked and written in one transaction. Linking always uses the saved configuration; the Check and synchronize page saves pending edits first.

Runtime. The class works in ACCDB, ACCDE and the Access Runtime. The runtime has no add-ins and no designer menu; applications call the class from their own code there.

• How to: Install the released package
• How to: Deploy an application with WKL controls to user PCs
• What the per-user registration writes
• How to: Let operators move the connections of a delivered application

• The Linked Tables Manager can't tell the profile
• Tables aren't linked at startup
• The add-in entry is missing

• API reference: WklLinkManager
• API reference: WklLinkDifferences
• API reference: WklLinkDifference

None