WKL Forms Object Library
Color scheme: Dark
Riviera
Light
Dark
You are here: start » en » Documentation » Microsoft Access development tools » WKL Forms Object Library » WKL Forms Object Library how-to guides » How to: Move all connections to another server

How to: Move all connections to another server

Point every connection in a profile to a new server (or database, driver, or schema) at once, for example after a database has moved, and relink the tables.

Requires WKL Forms Object Library 1.23 or later.

Solution overview

The designer's Connections page has the Apply to all bar: it sets one field (Driver, Server, Database, or Schema) to one value for the selected rows or for all rows the profile filter shows. Apply saves; the Check and synchronize page then shows every table as linked to another source, and Refresh all relinks them. In VBA, the same is an UPDATE of USysConnection followed by EnsureLinks.

Implementation

In the designer:

  1. On the Connections page, choose the profile in the Profile filter (for example, TEST).
  2. In Apply to all, choose Server, type the new server, choose all rows shown, and click Apply.
  3. Click Apply in the designer, open Check and synchronize, choose the same profile, and click Refresh all.

In VBA, for example in a maintenance routine: 

Public Sub MoveTestToNewServer()
    Dim links As New WklLinkManager
    CurrentDb.Execute "UPDATE USysConnection SET Server = 'sql02' " & _
                      "WHERE Environment = 'TEST' AND Server = 'sql01'", dbFailOnError
    Debug.Print links.EnsureLinks("TEST") & " tables relinked"
End Sub

Remarks

  • A profile filter shows only the rows of that profile. Rows without a profile serve every profile and are changed only when they're shown, for example with the All profiles filter, which shows every row; check the filter before you apply a value to all rows shown.
  • Relinking sets the new connection on the existing links; descriptions, formats, and lookups of the linked tables are kept.
  • If the change affects the profile the application uses at startup, nothing else is needed: the next EnsureLinks at startup relinks the tables that still point to the old server.
  • Test connection on one row checks the new server before you relink everything.
en/wiki/msaccess/comctl/how-to/move-connections-to-another-server.txt · Last modified: by Wolfgang van der Stille