About upsizing a Microsoft Access database

Upsizing is the process of migrating some or all database objects from a Microsoft Access database (.mdb) to a new or existing Microsoft SQL Server database or new Microsoft Access project (.adp).

Ways to upsize

The Upsizing Wizard upsizes an Access database to a new or existing SQL Server 2000, 7.0, and 6.5 database or new Access project by migrating data and data definitions, and by moving database objects to the new database structure. There are three ways to use the Upsizing Wizard:

Upsize all database objects from an Access database to an Access project so that you can create a client/server application. This approach requires some additional application changes and modification to code and complex queries.
Upsize only data or data definitions from an Access database to an SQL Server database.
Create an Access database front-end to an SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Jet database engine.

When to upsize an Access database to SQL Server

Over time, most database applications grow, become more complex, and need to support more users. At some point in the life of your Access database application, consider upsizing to SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability.

High performance and scalability

In many situations, SQL Server offers better performance than an Access database. SQL Server also provides support for very large, terabyte-sized databases, which is much larger than the current limit for an Access database of two gigabytes. Finally, SQL Server works very efficiently on Microsoft Windows 2000 by processing queries in parallel (using multiple native threads within a single process to handle user requests) and minimizing additional memory requirements when more users are added.

Increased availability

SQL Server allows you to do a dynamic backup, either incremental or complete, of the database while it's in use. Consequently, you do not have to force users to exit the database to back up data. This means your database can be running up to 24 hours a day, seven days a week.

Improved security

Using a trusted connection, SQL Server can integrate with the Windows 2000 system security to provide a single secured access to the network and the database. This makes it much easier to administer complex security schemes. A SQL Server database on a server is also better protected because unauthorized users can't get to the database file directly but must access the server first.

Immediate recoverability

In case of system failure (such as an operating system crash or power outage), SQL Server has an automatic recovery mechanism that recovers a database to the last state of consistency in a matter of minutes, with no database administrator intervention. Critical applications can be up and running again right away.

Reliable distributed data and batch transactions

Transaction processing is a vital requirement for a system that is designed to support critical applications such as banking and online order entry. SQL Server supports atomic transactions with transaction logging, which guarantees that all changes performed within a transaction are either committed or rolled back.

Consistency and recoverability of a database transaction are guaranteed even in the case of system failure in the middle of complex updates by more than one user. SQL Server treats all database changes inside a transaction as a single unit of work. By definition, either an entire transaction is completed safely and all resulting changes are reflected in the database, or the transaction is rolled backùand all changes to the database are undone.

Using a two-phase commit protocol, SQL Server can even support synchronized transactions that span more than one serverùensuring that all servers on the network are maintained in a consistent state.

Server-based processing

Microsoft designed SQL Server from the beginning as a client/server database, where data and indexes reside on a single server computer that is often accessed over the network by many client computers. SQL Server reduces network traffic by processing database queries on the server before sending results to the client. Thus, your client/server application can do processing where it's done best, on the server.

Your application can also use user-defined functions, stored procedures, and triggers to centralize and share application logic, business rules and policies, complex queries, and data validation and referential integrity code on the server, rather than on the client.

Before you upsize an Access database

Before you upsize your Access database to a SQL Server database or Access project, consider doing the following:

Back up your database Although the Upsizing Wizard doesn't remove any data or database objects from your Access database, it's a good idea to create a backup copy of your Access database before you upsize it.
Ensure you have adequate disk space You must have adequate disk space on the device that will contain the upsized SQL Server database. The Upsizing Wizard works best when there is plenty of disk space available.
For a SQL Server 7.0 or later database, SQL Server automatically grows your database for you as it's being created.

For a SQL Server 6.5 database, multiply the size of your Access database by two and reserve that amount of space on your disk. This ensures that the Upsizing Wizard has enough space to upsize your database and also leave it some room to grow. If you expect to add a lot of data to the database, make the multiple larger.

Create unique indexes A linked table must have a unique index to be updateable in Microsoft Access. The Upsizing Wizard can upsize an existing unique index, but can't create one where none exists. If you want to be able to update your tables, make sure you add a unique index to each Access table before upsizing.
Set a default printer You must set a default printer to use the Upsizing Wizard if you want it it to automatically create a report snapshot of the Upsizing Wizard report.
Assign yourself appropriate permissions on the Access database You need READ and DESIGN permission on all database objects to upsize them.
Assign yourself appropriate permissions on the SQL Server database
- To upsize to an existing database, you need CREATE TABLE and CREATE DEFAULT permissions.
- To build a new database, you need CREATE DATABASE permission, and SELECT permissions on the system tables in the Master database.
- To create new devices, you must be a system administrator.
If desired, create multiple disk devices If you are upsizing to a SQL Server version 6.5 database, you may want to create devices before running the Upsizing Wizard. The Upsizing Wizard creates all new devices on the same physical disk where the Master database device resides. If your server has multiple disks, you can place your database on one disk and the transaction log on a different disk. In the event of a disk failure, you can recover your database. Make sure that you have devices that are big enough on both disks. Create new devices if necessary.
SQL Server version 6.5 also allows databases and transaction logs to span several devices. However, the Upsizing Wizard only allows you to specify one device for your database and one device for the transaction log. To specify multiple devices for a database or transaction log, make those devices (and no other devices) the default devices. Then run the Upsizing Wizard, and select the default database or transaction log device.

Note If the size of the new SQL Server database or the size of the transaction log doesn't require using all the devices set as default, SQL Server uses only the devices necessary to accommodate the database or transaction log.

The Upsizing Wizard report

The Upsizing Wizard creates a report that provides a detailed description of all objects created, and reports any errors encountered during the process. The Upsizing Wizard automatically creates this report as a report snapshot with the same name as the Access database (for example, Northwind.snp), and stores it in the default database folder.

The Upsizing Wizard report contains information about the following:

Database details, including database size, as well as transaction logs and device names and sizes for a SQL Server 6.5 database.
Upsizing parameters, including what table attributes you chose to upsize and how you upsized.
Table information, including a comparison of Access and SQL Server values for names, data types, indexes, validation rules, defaults, triggers, and whether or not time stamps were added.
Any errors encountered, such as database or transaction log full, inadequate permissions, device or database not created, table, default, or validation rule skipped, relationship not enforced, query skipped (because it cannot be translated to SQL Server syntax), and control and record source conversion errors in forms and reports.

How database objects get upsized

The following data and database objects get upsized:

Data and data types

For SQL Server version 7.0 or later databases, the Upsizing Wizard converts Access database text to Unicode by adding the Unicode string identifier to all string values and by adding the Unicode n prefix to all data types.

All Access database data types are converted to their equivalent in SQL Server.

Queries

For SQL Server 2000 databases:

Select queries that don't have an ORDER BY clause or parameters are converted to views.
Action queries are converted to stored procedure action queries. Access adds SET NOCOUNT ON after the parameter declaration code to make sure the stored procedure runs.
Select queries that only reference tables (also called base queries) that use either parameters or an ORDER BY clause are converted to user-defined functions. If necessary, the TOP 100 PERCENT clause is added to a query that contains an ORDER BY clause.
Parameter queries that use named parameters maintain the original text name used in the Access database and are converted either to stored procedures or inline user-defined functions.

For SQL server 7.0 and 6.5 databases:

Select queries are converted to views.
Sorted queries are converted to a combination of views and stored procedures to allow for nesting and sorting (Views can be nested but can't contain ORDER BY clauses; stored procedures can contain ORDER BY clauses but can't be nested).
Parameter queries, queries that depend on a parameter query, and action queries are converted to stored procedures.

Note For all SQL Server databases, you may need to manually convert queries that did not upsize, such as SQL Passthrough queries, data definition queries, and union queries. You may also have to manually upsize queries that were nested too deeply.

Forms, reports, and controls

For all SQL Server databases, SQL statements in RecordSource, ControlsSource and RowSource properties for forms, reports, or controls are kept in place and not converted to a stored procedure or user-defined function.

Data access pages

The Upsizing Wizard changes the OLE DB connection and the data binding information in the Microsoft Office data source control to work with the new SQL Server database, and copies the page's corresponding HTML file to the same location as the Access project, renaming the HTML file with a "_CS" suffix. The new pages in the Access project retain the original name, so that hyperlinks between the Access project Pages continue to work.

Customized command bars and startup properties

For all versions of SQL Server, the Upsizing Wizard upsizes any custom command bars as well as the following startup properties:

StartUpShowDBWindow
StartUpShowStatusBar
AllowShortcutMenus
AllowFullMenus
AllowBuiltInToolbars
AllowToolbarChanges
AllowSpecialKeys
UseAppIconForFrmRpt
AppIcon
AppTitle
StartUpForm
StartUpMenuBar
StartupShortcutMenuBar

Modules and macros

The Upsizing Wizard doesn't make any changes to modules and macro. Designing and building an optimized client/server application is different from designing and building an optimized file server application. Therefore, you will probably need to retrofit your application to take full advantage of SQL Server and an Access project. After you upsize, you need to manually convert code that uses recordsets from Data Access Objects (DAO) to ActiveX Data Objects (ADO) in your modules. You should also revise any table and query design code (The Upsizing Wizard does not upsize an SQL Data Definition Language). For more information on developing optimized client/server applications, see the Microsoft Office 2000/Visual Basic Programmer's Guide.