Appendix E - Upsizing to SQL Server

3 4

At some point you might want to upgrade your Access database to SQL Server, either because of its size or so that you can run it as a client-server database. You can use the Upsizing Wizard to partially automate this task. The Upsizing Wizard will do the basic conversion of the Access (MDB file) database into a SQL Server database with a project (ADP file) front end. The wizard will also convert most queries into stored procedures and views. You will still have some work to do to make your new SQL Server application more efficient, but the wizard does a good part of the basic work for you.

Why Upsize?

There are several reasons to upgrade an Access database to SQL Server:

Size Access databases have a size limit of 2 GB; SQL Server databases can be as large as a terabyte.

Reliability SQL Server offers better performance than Access in many situations. When you run SQL Server on Windows 2000, it can process queries in parallel, using multiple threads, minimizing additional memory requirements when more users are added.

Availability You can back up a SQL Server database while it is running, allowing the database to be available all the time.

Security With a trusted connection, SQL Server integrates with the Windows 2000 system security to provide a single secured access to the network and the database.

Recoverability SQL Server has a recovery feature that automatically recovers a database to the last state of consistency in the event of a crash or power outage.

Reliable Distributed Data and Batch Transactions SQL Server supports atomic transactions with transaction logging, guaranteeing that all changes in a transaction are either committed or rolled back.

Server-based Processing SQL was designed from the beginning as a client-server database, with data and indexes residing on a single server for more efficient processing.

Using the Upsizing Wizard

Before you run the Upsizing Wizard, back up your database and ensure that you have enough disk space for the new SQL Server database—you’ll need at least twice the size of the Access database, preferably more. Make sure that each table has a unique index: SQL Server can upsize an existing index, but it can’t create one, and a table without a unique index won’t be updatable in SQL Server. If you have hidden tables that you want to upsize, make them visible, because hidden tables aren’t upsized.

note - Make a Hidden Table Visible


To make a hidden table visible, select Tools, Options and check the Hidden objects check box on the View page; this lets you see hidden tables in the Database window (though their icons appear dimmed). Then right-click the hidden table to be made visible, open its properties sheet, and clear the Hidden check box.

tip


See the Help topic "About Upsizing a Microsoft Access Database" for further considerations in upsizing secured Access databases.

To convert the Northwind sample database to SQL Server, follow these steps:

  1. Copy Northwind to another folder.
  2. Open the Northwind copy, and select Tools, Database Utilities, Upsizing Wizard, as shown in Figure E-1.

    figure e-1.start the upsizing wizard.

    Figure E-1. Start the Upsizing Wizard.

  3. Choose to use an existing SQL Server database or create a new one, as shown in Figure E-2. Click Next.

    figure e-2.select whether to use an existing sql server database or create a new one.

    Figure E-2. Select whether to use an existing SQL Server database or create a new one.

  4. Choose a server for the new SQL Server database, and give the database a name, as shown in Figure E-3. Click Next.

    figure e-3.specify the server and database name for the new sql server database.

    Figure E-3. Specify the server and database name for the new SQL Server database.

    You can also set up an explicit connection by clearing the Use Trusted Connection check box and entering the login ID and password for a connection.

  5. Select the tables to export to SQL Server, as shown in Figure E-4. Click Next.

    figure e-4.select the tables to export.

    Figure E-4. Select the tables to export.

  6. Select which table attributes to upsize, as shown in Figure E-5. You also have several options for adding timestamp fields to tables. Click Next.

    figure e-5.you have several choices for table attributes to export to sql server tables.

    Figure E-5. You have several choices for table attributes to export to SQL Server tables.

    note


    The Help button on the screens of the Upsizing Wizard doesn’t open Help topics appropriate for this wizard; it opens a Help topic about the Link Table Wizard.

  7. Choose to create a new Access client/server application (an Access project with a SQL Server back end), link SQL Server tables to an existing application, or make no application changes, as shown in Figure E-6. Click Next.

    figure e-6.choose to create a new application or link to an existing application.

    Figure E-6. Choose to create a new application or link to an existing application.

  8. Before the wizard finishes, you can choose to open the new ADP file or return to the MDB file, as shown in Figure E-7. Click Finish.

tip - Download SQL Server Books Online


SQL Server Books Online documents SQL Server extensively; it is installed with SQL Server 2000. If you don’t have either of these programs, you can download this help file from the SQL Server Books Online Web site at http://www.microsoft.com/SQL/techinfo/productdoc/2000/books.asp. Alternatively, you can purchase the SQL Server 2000 180-day trial CD; it also installs this useful information resource for SQL Server.

figure e-7.the final screen of the wizard offers you a choice of opening the adp or staying in the mdb.

Figure E-7. The final screen of the wizard offers you a choice of opening the ADP or staying in the MDB.

After you click Finish, a progress dialog box appears, showing the progress of the creation of the new SQL Server database and Access project, as shown in Figure E-8.

figure e-8.the upsizing wizard dialog box shows the progress of creating the new sql server database and access project front end.

Figure E-8. The Upsizing Wizard dialog box shows the progress of creating the new SQL Server database and Access project front end.

When the process is complete, an Upsizing Wizard report opens in print preview, as shown in Figure E-9.

figure e-9.the upsizing wizard report looks like this in print preview.

Figure E-9. The Upsizing Wizard report looks like this in print preview.

The report gives full details about the conversion, down to the data type of each field in each SQL Server table. Any problems will be reported in this document. Figure E-10 shows that some of the Northwind queries could not be upsized because they were DISTINCTROW queries.

figure e-10. problems upsizing distinctrow queries appear in the upsizing wizard report.

Figure E-10. Problems upsizing DISTINCTROW queries appear in the Upsizing Wizard report.

note


Even though you select opening the ADP on the final wizard screen, you may be returned to the Access (MDB) database. In that case, just close the database, and open the project (ADP) file.

The project front end looks much like the Access database (see Figure E-11); however, all the tables are actually SQL Server tables, linked to the ADP front end.

figure e-11. the suppliers form in the new access project (adp) file looks like this.

Figure E-11. The Suppliers form in the new Access project (ADP) file looks like this.

Fine-Tuning after Upsizing

While generally the Upsizing Wizard does a good job of upsizing Access tables to SQL Server tables, you might encounter problems, particularly with tables that don’t have a unique index. (They will be omitted.) Certain field properties that aren’t supported in SQL Server will be dropped (such as AllowZeroLength, InputMask, and Format), and hidden tables will be skipped.

Queries are more likely to cause problems when upsizing. Some Access query types don’t have corresponding views or stored procedures in projects, and others are changed in various ways. In the preceding example, the Northwind Quarterly Orders by Product crosstab query wasn’t converted at all, with an error message "Crosstab queries cannot be upsized." The Sales By Year query failed to upsize because a function (Format) was used in a column expression. However, the union query Customers And Suppliers By City was upsized correctly (something the Access 2000 Upsizing Wizard couldn’t handle).

After you upsize a database, check all the database objects and make any needed changes, including recreating the queries that didn’t make it and modifying forms and reports as needed for efficient functioning in a project. A full discussion of optimizing projects with SQL Server back ends is beyond the scope of this book; for more details on working with projects and SQL Server tables, you might want to look at a book like Inside Microsoft SQL Server 2000 by Kalen Delaney based on the first edition by Ron Soukup (Microsoft Press, 2000).



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

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