Choosing a Client/Server Migration StrategyPrior to the introduction of Access 2000, linking was the only method of migrating Access applications from Jet tables to client/server databases. The primary advantage of linking client/server tables is that the Jet 4.0 database engine running on the client processes your Jet SQL queries. Thus, crosstab queries continue to execute as expected, and you can use MSGraph objects in your forms and reports. Linked client/server tables let you take advantage of Jet passthrough queries to send Transact-SQL (T-SQL) statements directly to SQL Server, PL/SQL to Oracle, or any other SQL dialect to your RDBMS. The downside of linking is that you lose the efficiency of server-side query processing, which is one of the most important features of client/server RDBMSs. The sections that follow describe Access 2003 options for moving to SQL Server databases. Note You create passthrough queries by opening a new query in Design view, closing the Show Table dialog, and choosing Query, SQL-Specific, Pass-Through to open an empty Query1: SQL Pass-Through Query window. In the window, type the query's SQL statement in the SQL dialect of the server, and then close the window and save the query. When you execute the query, Jet sends the SQL statement to the linked database for execution. If the SQL statement returns rows, the query result set opens in Datasheet view. Migrating Jet Applications to SQL Server with the Upsizing WizardAccess 2003 supports the following three automated migration called upsizing scenarios from conventional Jet applications to SQL Server:
Tip Use MSDE 2000 as the back end for all new multiuser Access applications you create. Microsoft will continue to upgrade SQL Server; what you see now is what you get in the future with Jet 4.0. There will be no further updates or upgrades to Jet. This chapter covers use of the Database Splitter tool to create Jet back ends only to demonstrate how to convert existing Jet back ends to SQL Server 2000. You use the Access 2003 Upsizing Wizard which works only with SQL Server 6.5 (having SP5 installed), 7.0, or 2000 for the preceding three scenarios, but this chapter focuses only on the first two. The client/server examples in this book use MSDE 2000 as the server, but most examples also accommodate all SQL Server 7.0 editions, including the original version of MSDE. None of the examples have been tested with SQL Server 6.5 or 7.0. SQL Server enforces referential integrity by triggers or declarative referential integrity (DRI), if specified in the Relationships window for the Jet tables. Jet uses DRI, which conforms to ANSI-92 SQL syntax, and DRI is the preferred approach for SQL Server 2000 databases. SQL Server 2000 also supports Jet's cascading updates and deletions; SQL Server 7.0 doesn't. No version of SQL Server has a field data type that corresponds to Jet's Hyperlink data type, so the Wizard converts Hyperlink fields to plain text.
For a brief description of SQL Server 2000's feature set, see "New SQL Server 2000 Features," p. 34. Note
Exporting Tables to Other RDBMSsAccess uses the Open Database Connectivity application programming interface (ODBC API) to link conventional Access (.mdb) front ends to client/server RDBMSs. Office 2003 installs ODBC drivers for SQL Server and Oracle databases. If you're using Oracle, IBM DB2, Sybase, Informix, or another RDBMS as your application's data source, you can't use the Upsizing Wizard to automate the table export and linking process. You must manually export (copy) your Jet tables to the RDBMS and then link the RDBMS tables to your Jet front end.
For an introduction to the ODBC table-linking process, see "Linking Visual FoxPro Tables with ODBC," p. 274. Note Linking to databases of an RDBMS other than SQL Server or Oracle requires a vendor-supplied or third-party ODBC driver. You can't use an OLE DB data provider to link client/server tables to Jet front ends. An alternative to linking tables in "foreign" databases to Jet front ends is to use the linked server feature of SQL Server 2000. Linking a server other than another SQL Server instance requires an OLE DB data provider for the linked server. Linking servers to SQL Server 2000 is required to use ADP with other RDBMSs.
For more information on linking other RDBMSs to SQL Server, see "Linking Remote Servers," p. 842. Migrating tables and linking to databases other than SQL Server involve the following basic steps:
Tip
|