Choosing a ClientServer Migration Strategy

Choosing a Client/Server Migration Strategy

Prior 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 Wizard

Access 2003 supports the following three automated migration called upsizing scenarios from conventional Jet applications to SQL Server:

  • Splitting, upsizing, and linking a single-user Jet database If your .mdb file contains application objects (queries, forms, pages, reports, modules, macros, or any combination) and data objects (tables), you can split and upsize the tables, and link the application objects to the server tables in a single process. An example of this scenario is upsizing Northwind.mdb.

  • Upsizing and linking a multiuser Jet application If you've used the Database Splitter utility to segregate application and data objects into front-end and back-end .mdb files, respectively, you upsize only the front-end .mdb file. Upsizing the back-end .mdb doesn't work directly; you receive a "Can't find TableName" error message when you attempt to open the upsized linked table in the front-end application's Datasheet view or in a form or report.

  • Upsizing a Jet application to an Access Data Project This scenario moves your Jet tables to SQL Server and attempts to update your queries to T-SQL stored procedures. Chapter 22, "Upsizing Jet Applications to Access Data Projects," describes this upsizing method and how to overcome problems with Jet queries that T-SQL can't handle.

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

graphics/globe.gif

Access 2003 doesn't include SQL Server 2000's Books Online documentation. Microsoft has published an online version of updated SQL Server 2000 documentation at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp.


Exporting Tables to Other RDBMSs

Access 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:

  1. You or your organization's database administrator (DBA) must create the database, and you must have permissions to create, read, and write to objects in the database.

  2. Back up the Jet database, and verify the integrity of the backup.

  3. Use Access's File, Export command to export the Jet tables to the new database.

  4. Use the RDBMS's management tools to add indexes, default values, and validation rules, and enforce referential integrity between the tables. Add cascading updates and deletions, if the RDBMS supports them (most do).

  5. Rename the existing Jet tables, and use the File, Get Existing Data, Link command to establish links to the database tables.

  6. Delete the existing Jet tables after you confirm that the linked tables operate properly and have been backed up on the server.

Tip

graphics/globe.gif

Microsoft has reported many issues with exporting and linking Oracle tables to Access 2002 applications, many of which also apply to Access 2003. To review the known problems, search the Microsoft Knowledge Base (KB) with Access 2002 selected in the My Search Is About list and type Oracle in the My Question Is text box; then repeat the process with Access 2003 as the product. You can reach the Knowledge Base Search page quickly by opening the Support menu and choosing Knowledge Base on the Microsoft home page (http://www.microsoft.com).




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net