Moving the Upsized Database to Another Server

If you upsize the database on a local instance of MSDE 2000 and then decide to move the database to another server, be prepared to add a substantial amount of VBA code to your project to regenerate the links. There's no Access wizard or utility to automatically change the SERVER= SERVERNAME element of a DSN-less connection string for each linked table.

Caution

Don't try to use the Linked Table Manager database utility to change the server name in a DSN-less connection string. The Linked Table Manager requires an ODBC user or system DSN, or a file data source, instead of modifying the current DSN-less connection string. If you use the Linked Table manager to change the link, you must set up a machine DSN on each user's computer or create a file data source on the server and specify the Uniform Naming Convention (UNC) path to the file in the connection string. The ChangeServer procedure requires at least one DSN-less connection to change DSN connections.

Moving or Copying the SQL Server Database Files

You can move or copy an SQL Server database from one machine to another by any of the following methods:

• Create a temporary New Project (Existing Data), connect to the SQL Server database, and choose Tools, Database Utilities, Transfer Database to install the database on the new SQL Server 2000 instance. The original database is retained. This is the simplest and most foolproof method.

  For an example of using the Transfer Database command to move an SQL Server database, see "Transferring the Project's Database to the Server," p. 839.]

  
  

• Use SQL Enterprise Manager's Copy Database Wizard to copy or move the database to the new SQL Server instance. (Access 2003 uses the Copy Database Wizard to transfer the database.) This approach requires a licensed copy of Enterprise Manager.

• Close all connections to the database, stop SQL Server on the source computer, and use Explorer to copy the DatabaseName.mdf (database) and DatabaseName.ldf (log file) from the \Program Files\Microsoft SQL Server\MSSQL\Data folder to the same folder on the new server. After copying the files, create a temporary New Project (Existing Data). In the DataLink Properties dialog, select the Attach a Database File as a Database Name option, specify the database name and the DatabaseName.mdf file, which must be on the same machine as the SQL Server instance you specify.

Changing the Link Connection String with a VBA Subprocedure

After you've moved the linked tables to the new server, you face the challenge of changing the SERVER= SERVERNAME element of the DSN-less connection string to the new server name. Properties of linked table definitions, which Jet calls TableDefs, are read-only. You can't persist changes to the Description property value of a linked table. If you alter the server name in the connection string of the Description property, and close and save your changes to the table design, the connection string doesn't change.

Nwind19.mdb in the \Seua11\Chapt19 of the accompanying CD-ROM contains a modChangeServer module with a single VBA subprocedure, ChangeServers. You can use this procedure to change the connection string to point to the new server or change a DSN connection string to the DSN-less type. To add the modChangeServer and its subprocedure to your front-end .mdb, choose File, Get External Data, Import, and import the module from Nwind19.mdb. The following example uses NWClient.mdb, upsized to your local computer in the earlier "Upsizing an Application with Linked Tables" section. Running the example requires you to have a networked computer running SQL Server.

To review the process for working with VBA modules, see "Using the Immediate Window," p. 366.

To run the ChangeServer subprocedure in an application that has tables linked to SQL Server and the modChangeServer module installed, do this:

1. Click the Modules shortcut inNWClient.mdb's Database window, and double-click modChangeServer to open the VBA editor with the ChangeServer subprocedure active.

2. Press Ctrl+G to open the Immediate window. Type Call ChangeServer(" CurrentServerName", "NewServerName").For this example, the procedure call is Call ChangeServer("OAKLEAF-XP1", "OAKLEAF-MS10") (see Figure 19.17). If you haven't copied or moved the tables to another server, use the current workstation or server name as the value of both arguments.

   Figure 19.17. Call the ChangeServer subprocedure with two literal string arguments: the current server name followed by the destination server.

   

3. Press Enter to execute the procedure. The first stage of the procedure creates an array of the new connection data for each linked table, and displays a message asking you to confirm the change (see Figure 19.18, top).

   Figure 19.18. You see one of these three messages, depending on your argument values and whether you've added a link with a DSN data source.

   

   Note

   If you type the wrong value for the CurrentServerName argument, you receive the middle message shown in Figure 19.18. If you've added links with a DSN connection, you see the bottom message. If you only want to change DSN to DSN-less connections, type the current server name as the value of both ChangeServer arguments.

4. After a second or more, depending on the speed of your machine and the network, a Debug.Print statement confirms all new connection strings in the Immediate window (see Figure 19.19).

   Figure 19.19. The Immediate window displays the new connection strings for the links.

   

If you type a nonexistent server name as the second argument value, or the database isn't present on the destination server you specify, you receive the two error messages shown in Figure 19.20. The upper message from SQL Server appears after about 30 seconds of inactivity. Clicking OK opens an SQL Server Login dialog. Click Cancel to dismiss the dialog, and display the procedure's error message (see Figure 19.20, bottom). Click OK to cancel execution and leave the connection strings unaffected.

Figure 19.20. These two error messages appear in sequence if you type an invalid destination server name.

Note

ChangeServer creates a test connection to the destination server to prevent deleting the first existing link. The properties of linked TableDef objects are read-only, so the existing link must be deleted before adding the new TableDef.