| Upsizing a Jet application to an SQL Server and an Access project is a drastic operation. The Upsizing Wizard banishes all vestiges of Jet from your application. As a general rule, the probability of initial upsizing success is inversely proportional to the size and complexity of your Jet-based Access application. If you've designed your Jet applications with upsizing in mind, the likelihood of upsizing success greatly increases. It's a safe bet, however, that only a small percentage of existing Jet applications were designed with ease of upsizing as a design parameter. Following are the basic steps that the Upsizing Wizard takes when you select the New Database option in the first Wizard dialog and the Create a New Access Client/Server Application option in the fifth dialog: Create a new SQL Server database. By default, the new database is the name of the existing database with an "SQL" suffix. Copy the structure of Jet tables to new tables in the database, and add the data to the tables. Add indexes to the tables. Add extended properties to the tables and set the values of properties. Create a view for each Jet SELECT QueryDef object that doesn't have a GROUP BY clause in the database window's Queries page. Create an in-line, table-valued function for each Jet SELECT QueryDef object that has a GROUP BY clause in the Queries page. Create an in-line, table-valued function for parameterized SELECT queries. Create a stored procedure for each action QueryDef object in the Queries page. Create a new project (.adp) file with a connection to the new SQL Database. Copy all Jet forms, subforms, reports, subreports, pages, macros, and modules in the .mdb file to the new .adp file. The source files for Data access pages (DAP) must be local (not deployed on a Web server) in order for the Wizard to change the connection string in the .htm file. The Wizard makes no changes to VBA code behind forms or reports, or in modules. DAP that use relative connection strings, such as Northwind.mdb, won't upsize if you're running Windows XP Service Pack (SP) 1 Internet Explorer 6.0 SP1.
Upsizing tables, queries, and Jet SQL statements is subject to the limitations described in sections later in this chapter. This chapter covers upsizing conventional single-file and front-end/back-end Jet applications. Chapter 24, "Designing and Deploying Data Access Pages," discusses issues with upsizing DAP to SQL Server data sources. |
