WklLinkManager

Dim myWklLinkManager As New WklLinkManager

Keeps the linked ODBC tables of an Access front end in line with its configuration: connection parameters per profile in USysConnection, the tables to link in USysLinkedTable.

None

The configuration lives in two local tables of the front end, which CreateTables creates (Access hides tables whose names start with USys):

• USysConnection: ID, Name, Driver, Server, Database, Schema, UID, Parameter, Environment. Rows of the same Name are the same connection in different profiles; Environment is the profile (DEV, TEST, PROD …), empty for a row that serves every profile. For the driver Wkl OData, Server is the address of the service; for other drivers it is the server and Database the database.
• USysLinkedTable: ID, LocalName, RemoteName, ConnectionID, Schema, Enabled. ConnectionID names a connection row; its Name names the connection, and the profile picks the row of that name. An empty Schema takes the schema of the connection.

Credentials are handled as in Access: Parameter takes further key=value parts of the connection string, such as Trusted_Connection=Yes (Windows login with SQL Server drivers), Auth=Windows (Wkl OData) or PWD=…. A password in Parameter is stored in clear text and the link saves it, as Access does with “Save password”; without a password and without Windows login, the ODBC driver asks for the login when a table is linked.

The profile of an operation is the Environment argument when given, else the Environment property, else the profile the front end is linked with now. When the configuration has profiles and the tables fit none of them, the operation raises wkeNotConfigured instead of guessing one.

Changing the connection of a link keeps the table's own properties (description, formats, lookups): only Connect is set and the link refreshed. A changed remote table, or a password that is added or removed, needs a new link; it is created under a temporary name first and replaces the old link only when Access accepted it. A local table of a configured name is never replaced.

TestConnection and RemoteTables never show a dialog of the driver; a source that needs a password the configuration does not hold fails with the driver's message.

The object needs the Access instance it works on. Created in VBA of a front end, it finds the Access it runs in; Application sets another one.

Created with New WklLinkManager or CreateObject.

' AutoExec: RunCode StartUp()
Public Function StartUp()
    Dim links As New WklLinkManager
    links.EnsureLinks "PROD"
End Function

None

Property Get Application() As Object
Property Set Application(RHS As Object)

Gets or sets the Access application whose tables the object manages.

None

Property value: Object – The Access.Application; Nothing (when set) goes back to the default.

Without a value set, the object uses the Access instance whose VBA created it. Set it only when the object is used from outside that Access, for example from a script that automates Access.

Read/write.

None

Declared by WklLinkManager.

Property Get Environment() As String
Property Let Environment(RHS As String)

Gets or sets the profile used when a method is called without an Environment argument.

None

Property value: String – The profile, for example "PROD"; an empty string removes the setting.

Without a value set, reading the property returns the profile the front end is linked with now (DetectEnvironment), or an empty string.

Read/write.

None

Declared by WklLinkManager.

Property Get Environments() As Variant

Returns the profiles named in USysConnection.

None

Property value: Variant – A 0-based String array in the order the profiles first appear; empty when there are none.

Read-only.

None

Declared by WklLinkManager.

Function DetectEnvironment() As String

Returns the profile the front end is linked with now.

None

Return value: String – The profile whose configured links the most tables match; an empty string when no table matches or two profiles match equally well.

None

None

Declared by WklLinkManager.

Function Check(Optional ByVal Environment As Variant) As WklLinkDifferences

Compares the configuration with the tables of the front end without changing anything.

Return value: `WklLinkDifferences` – The differences; empty when every enabled table is linked as configured.

Besides the configured tables, Check lists ODBC links that are not in USysLinkedTable (wldUnmanaged); they are information only and are never changed.

None

Declared by WklLinkManager.

Function EnsureLinks(Optional ByVal Environment As Variant) As Long

Creates missing links and repairs those that differ from the configuration.

Return value: Long – The number of tables linked or relinked; 0 when everything matched.

Tables that already match are not touched, so calling EnsureLinks at every start costs one comparison. When a table cannot be linked (a local table in the way, no connection for the profile, a source that refuses), the other tables are linked anyway; then the method raises wkeSourceError with one line per table that is still wrong, and LastMessage holds the same lines.

None

Declared by WklLinkManager.

Function RelinkAll(Optional ByVal Environment As Variant) As Long

Links every enabled table of the configuration anew, also those that already match.

Return value: Long – The number of tables linked.

Errors are handled as by EnsureLinks.

None

Declared by WklLinkManager.

Sub Relink(ByVal LocalName As String, Optional ByVal Environment As Variant)

Links one table of the configuration anew.

Raises wkeKeyNotFound when the table is not an enabled row of the configuration.

None

Declared by WklLinkManager.

Function TestConnection(ByVal Connection As String, Optional ByVal Environment As Variant) As Boolean

Opens a connection of the configuration to check that it works.

Return value: Boolean – True when the source opened; False otherwise, with the driver's message in LastMessage.

None

None

Declared by WklLinkManager.

Function RemoteTables(ByVal Connection As String, Optional ByVal Environment As Variant) As Variant

Returns the tables and views a connection offers.

Return value: Variant

System objects (MSys tables, INFORMATION_SCHEMA and sys views) are left out. Raises wkeSourceError with the driver's message when the source does not open, and wkeKeyNotFound for an unknown connection.

None

Declared by WklLinkManager.

Function ImportLinks(Optional ByVal Environment As Variant) As Long

Takes the ODBC links of the front end that the configuration does not list yet into USysConnection and USysLinkedTable.

Return value: Long – The number of tables taken over.

Links to the same source share one connection row; an existing row that builds the same connection is used. Keywords that drivers or Access add on their own (APP, WSID) are left out. A password the link stores is taken into Parameter. Creates the configuration tables when they are missing.

None

Declared by WklLinkManager.

Sub CreateTables()

Creates USysConnection and USysLinkedTable when they are missing.

None

Existing tables are left as they are. LocalName in USysLinkedTable has a unique index.

None

Declared by WklLinkManager.

Property Get LastMessage() As String

Returns the message of the last TestConnection, or the tables the last EnsureLinks, RelinkAll or Relink could not link.

None

Property value: String – The message; one line per table; an empty string after success.

Read-only.

None

Declared by WklLinkManager.

Function ShowDesigner(Optional ByVal Page As String = "") As Boolean

Opens the designer of the configuration, modal to the Access window.

Return value: Boolean – True when the designer was closed with OK.

The designer edits a copy of both configuration tables; OK and “Apply” save it in one transaction, after checking that every connection has a name, a driver and valid parameters and every table a unique local name and a connection. “Auf alle anwenden” on the page Verbindungen sets the driver, server, database or schema of the selected rows, or of all rows of a profile, at once. The Check and synchronize page shows the differences for a profile and runs EnsureLinks, Relink, RelinkAll and ImportLinks; unsaved edits are saved first. Raises wkeInvalidValue for an unknown page.

None

Declared by WklLinkManager.