About upsizing a Microsoft Access database - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

About upsizing a Microsoft Access database

Applies to: Microsoft Office Access 2003

Upsizing is the process of migrating some or all database objects (database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.) from a Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) (.mdb) to a new or existing Microsoft SQL Server database or new Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.) (.adp).

Show 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 (front-end/back-end application: An application consisting of a "back-end" database file that contains tables, and copies of a "front-end" database file that contain all other database objects with links to the "back-end" tables.). This approach requires very little application modification since the code is still using the Jet database engine (Microsoft Jet database engine: The part of the Access database system that retrieves and stores data in user and system databases. It can be thought of as a data manager upon which database systems, such as Access, are built.).

Show If the wizard doesn't start

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later, at http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010489351033.

For more information about sandbox mode, see the topic About Microsoft Jet Expression Service sandbox mode.

Show 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 (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) application, consider upsizing to SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability.

Show 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 or later 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.

Show 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.

Show Improved security

Using a trusted connection, SQL Server can integrate with the Windows 2000 or later system security to provide a single access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes. A SQL Server database on a server also employs innovative security features, which helps prevent unauthorized users from getting to the database file directly, but rather they must access the server first.

Show 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.

Show 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 (user-defined function: A query that takes input parameters and returns a result like a stored procedure. Types: scalar (multistatement; returns one value), inline (one statement; an updateable table value), and table (multistatement; table value).), stored procedures (stored procedure: A precompiled collection of SQL statements and optional control-of-flow statements that is stored under a name and processed as a unit. The collection is stored in an SQL database and can be run with one call from an application.), and triggers (trigger: A special form of a stored procedure that is carried out automatically when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.) 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.

Show Before you upsize an Access database

Before you upsize your Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) to a SQL Server database or Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects such as tables and views.), 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 (report snapshot: A file (.snp extension) that contains a high-fidelity copy of each page of an Access report. It preserves the two-dimensional layout, graphics, and other embedded objects of the report.) 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.

Show 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 (report snapshot: A file (.snp extension) that contains a high-fidelity copy of each page of an Access report. It preserves the two-dimensional layout, graphics, and other embedded objects of the report.) with the same name as the Access database (for example, Northwind.snp), and stores it in the default database folder.

Security This report may contain private, confidential, or sensitive information. Make sure this file is protected to help prevent it from being accessed by a malicious user.

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 (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) and record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) conversion errors in forms and reports.

Show How database objects get upsized

The following data and database objects get upsized:

Show 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.

Show 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 crosstab queries. You may also have to manually upsize queries that were nested too deeply.

Show 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.

Show 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.

Show 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

Show 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) (Data Access Objects (DAO): A data access interface that communicates with Microsoft Jet and ODBC-compliant data sources to connect to, retrieve, manipulate, and update data and the database structure.) to ActiveX Data Objects (ADO) (ActiveX Data Objects (ADO): A data access interface that communicates with OLE DB-compliant data sources to connect to, retrieve, manipulate, and update data.) in your modules. You should also revise any table and query design code (The Upsizing Wizard does not upsize SQL Data Definition Language).