Linking Client/Server Tables Manually As mentioned in the earlier "Examining the ODBC Table Connection String" section, you must create an ODBC data source when you use the Upsizing Wizard with an existing SQL Server database. You also must create a DSN when you manually export Jet tables to an RDBMS other than SQL Server, and then link the tables to your Jet front end. The number and appearance of the dialogs varies according to the ODBC driver you use to make the connection to the existing database on the RDBMS. After you create the DSN, you can use the upsizing Wizard to add new tables to an SQL Server database. For other RDBMSs, you must manually export your Jet tables to the database. You use the same DSN to export the data from and attach the tables to your Jet front end. Creating the ODBC Data Source To create a DSN for any RDBMS for which you've installed an ODBC 2.x or 3.x driver, do the following: Launch Control Panel's ODBC Data Source Administrator tool. Under Windows XP and 2000, the Data Sources (ODBC) icon is in Control Panel's Administrative Tools subfolder. The Administator opens with the User DSN page active. Note If you select the Use Existing Database option in the first Upsizing Wizard dialog, the Wizard opens the Select Data Source dialog. If you're preparing a temporary data source for addition of tables to an SQL Server database you created with the Upsizing Wizard, you can create a User or System DSN on your workstation. Otherwise, click the File DSN tab and navigate to a server share for which users of your application have at least read access. Click the Add button to open the Create New Data Source dialog, and select the driver for the RDBMS with the database for your application (see Figure 19.21). This example uses the SQL Server driver installed by Office 2003 running under Windows XP. Figure 19.21. Select the ODBC driver for your RDBMS in the Create New Data Source dialog. Don't set Advanced properties, unless the driver vendor instructs otherwise.  Click Next to open the second Create New Data Source dialog, and type the UNC path and name of the data source file, \\OAKLEAF-MS10\Northwind\NWDataSQL.dsn for this example. The standard extension for DSN files is, not surprisingly, dsn. Tip The default location for file DSN's is the \Program Files\Common Files\ODBC\Data Sources on your computer. The best location for the file DSN for an upsized multiuser application is the share in which you placed the Jet back-end database. Use UNC's \\ServerName\ShareName network path format, not a mapped drive, to specify the file location. Click Next to confirm your initial settings and then Click Finish to open the first driver-specific dialog Create a New Data Source to SQL Server in this case. Type a description of the DSN, and open the Server list to select the RDBMS server, OAKLEAF-MS10 for this example (see Figure 19.22). Figure 19.22. The first driver-specific dialog for the SQL Server driver lets you add a description of the DSN and specify the server name.  Click Next to open the second driver-specific dialog. For SQL Server, accept the default Windows NT Authentication option (see Figure 19.23). Alternatively, select With SQL Server Authentication..., which requires a user account and password having at least CREATE DATABASE privileges. Figure 19.23. The SQL Server driver's second dialog lets you select the authentication method. The default client configuration is TCP/IP. Click the Client Configuration button to open a dialog that lets you add additional network protocols, such as IPX/SPX or Named Pipes, if you need them.  Click Next to make a temporary connection to the server. Mark the Change the Default Database To check box, open the drop-down list, and select the database name, NwindSQL for this example. Accept the default Use ANSI... settings (see Figure 19.24). Figure 19.24. Select the database for the DSN in the third SQL Server-specific dialog.  Click Next to open the fourth SQL Server dialog. The single default option, Perform Translation for Character Data, is satisfactory for most DSNs. Specify logging options only if you need to debug performance problems when using the DSN. Click Finish to display a summary of your settings, and click Test Data Source to confirm connectivity to the database on the server (see Figure 19.25). Figure 19.25. The final step when configuring an SQL Server ODBC data source is to test connectivity to the specified database.  Click OK twice to save the file DSN, and then click OK to close the ODBC Administrator tool.
Note If you use a temporary user or system DSN to add table(s) to a new SQL Server database you created with the Upsizing Wizard, run the ChangeServer subprocedure, described in the earlier "Changing the Link Connection String with a VBA Subprocedure" section, to change to DSN-less connections for added tables.
Exporting Jet Table Data to the RDBMS Manually exporting Jet tables to an ODBC-connected client/server database is a straightforward process, but the manual export procedure creates only the basic table structure with a simple CREATE TABLE statement and then populates the table with an INSERT statement for each row. Unlike the Upsizing Wizard, exporting a table doesn't establish primary keys, add indexes, or enforce referential integrity with DRI. You or the DBA must handle these tasks after exporting all tables. Following are the steps to export Jet tables to RDBMSs other than SQL Server: Open the .mdb file containing the Jet tables to export, Northwind.mdb for this example. Choose File, Export to open the Export 'TableName' To dialog. Select ODBC Databases() in the Files of Type list, which closes the Export dialog and opens the Export dialog with TableName in the Export TableName To text box (see Figure 19.26, top). Figure 19.26. When you select ODBC Databases() in the Export dialog's Files of Type list, the Export dialog (top) opens. Clicking OK opens the ODBC Administrator's Select Data Source dialog.  Click OK to open the Select Data Source dialog with the File DSN page active. If you specified a default folder for file DNSs in step 3 of the preceding section, the file you created appears in the list. If not, navigate to the server share in which you stored the DSN. Select the file (see Figure 19.26, bottom), and click OK to close the dialog and start the export process. Repeat steps 2 4 for each table to export. You don't need to wait for the export process to complete before selecting another table to export. Use the RDBMSs toolset to specify primary-key fields, add indexes, and establish referential integrity to emulate as closely as possible your original Jet database.
DBAs use SQL Server Enterprise Manager (commonly called EntMan) to perform step 6's operations for SQL Server databases. Alternatively, you can add SQL Server indexes and create relationships (called constraints) by opening an Access data project for the database. The process for adding indexes and other table accouterments is tedious when upsizing many tables, so use the Upsizing Wizard for all Jet export operations to SQL Server. Attaching the Exported Tables Attaching the tables to your front-end application with the file DSN follows the same process as that described for FoxPro databases in Chapter 8, "Linking, Importing, and Exporting Data." Unfortunately, you can't use the Linked Table Manager to change front-end links from an .mdb file to a .dsn file. To attach the client/server tables you exported, do this: Open the front-end .mdb, and choose File, Get External Data, Link Tables to open the Link dialog. Select ODBC Databases() in the Files of Type list to open the File DSN page of the Select Data Source dialog. Navigate to and double-click the FileName.dsn file to open the Link Tables dialog. Multi-select the tables to attach to the front-end .mdb file. Figure 19.27 shows the eight upsized SQL Server Northwind tables and a tblOrders table selected. Figure 19.27. Select in the Link Tables dialog each exported table to attach to the front end.  If you specified Windows authentication when you created the DSN, the Save Password check box should be cleared. Most Windows NT versions of client/server RDBMSs accommodate Windows authentication. For RDBMS-based security, you can mark the Save Password check box if you didn't use sa (or its equivalent) as the account when you created the DSN. Click OK to begin the linking process. If Jet can't determine the primary-key field(s) of linked tables, the Select Unique Record Identifier dialog opens for each table (see Figure 19.28). Select the key field(s) for the table; if you click cancel, the table won't be updatable. Figure 19.28. Specify the name(s) of the primary-key field(s) if Jet can't detect a table's primary key.  The prefix of the attached table names depend on the RDBMS's table naming conventions. As mentioned earlier in the chapter, SQL Server tables gain a dbo_ prefix. Temporarily rename the original tables or links, and then rename the new ODBC links to remove the prefix.  Open the Relationships windows to verify all tables are present and that every table has key field(s) identified by a bold font. Reestablish the relationships between the primary- and foreign-key fields of the tables.
Check all queries for proper execution, and make sure your forms and reports operate as before.
After you've verified that all's well with the attached tables, you can delete the renamed Jet tables or their links. |
