How to: Choose how linked tables log in

Decide for each connection whether linked tables use the Windows sign-in or a stored password, or whether they ask the user: the same three options Access offers when you link a table by hand.

Requires WKL Forms Object Library 1.23 or later.

The link manager has no fields of its own for credentials. The Login column (UID) holds the login name, and Parameter takes further key=value; parts of the connection string, including the credentials. The link manager passes them to the driver and, when Parameter holds a password, stores it in the link, as Access does with “Save password”.

' A connection row for PROD with Windows login.
CurrentDb.Execute "INSERT INTO USysConnection ([Name], Driver, Server, [Database], [Schema], Parameter, Environment) " & _
    "VALUES ('Sales', 'ODBC Driver 18 for SQL Server', 'sql01', 'Sales', 'dbo', 'Trusted_Connection=Yes', 'PROD')", dbFailOnError

• A stored password is clear text in USysConnection and in every link, as with “Save password” in Access. Anyone who can open the front end can read it, even in an ACCDE file. Prefer the Windows sign-in.
• Adding or removing a password relinks the tables from scratch, because Access can't change this on an existing link.
• Ask the user: the driver asks for the login when a table is linked, and Access asks again when the front end opens a linked table in a new session. TestConnection and Test connection never show this dialog box; for such a connection, they report the driver's login error.
• Other options of the driver (Encrypt, TrustServerCertificate, ApplicationIntent …) also go into Parameter. Encrypt=No sends credentials and data unprotected; TrustServerCertificate=Yes accepts any server certificate, so the client no longer checks which server it talks to.

• WklLinkManager class remarks
• How to: Take over the links a front end already has
• WklLinkManager overview