Move Access data to a SQL Server database by using the Upsizing Wizard

Over time, most database applications grow, become more complex, and need to support more users. At some point, you might want to upsize your Microsoft Office Access application to a Microsoft SQL Server database. This can help improve performance, scalability, availability, security, reliability, and recoverability.

In this article

• About upsizing a Microsoft Office Access database
• Use the Upsizing Wizard
• How database objects get upsized

About upsizing a Microsoft Office Access database

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

Benefits of upsizing a database to SQL Server

• High performance and scalability SQL Server often performs better than an Access database. It also supports very large databases, up to terabytes in size, which is much larger than the current Access limit of two gigabytes. SQL Server also processes queries in parallel and uses memory efficiently as more users are added.
• Increased availability SQL Server lets you create dynamic backups, either incremental or complete, while the database is in use. You don't have to ask users to exit the database before you back up data.
• Improved security By using a trusted connection, SQL Server can integrate with Windows security to provide one sign-in for both the network and the database. This makes complex security settings easier to manage.
• Immediate recoverability If a system fails, such as during an operating system crash or power outage, SQL Server can automatically recover a database to its last consistent state in minutes, without help from a database administrator.
• Server-based processing Using SQL Server in a client/server configuration reduces network traffic because the server processes queries before it sends results to the client. This is usually more efficient, especially with large data sets. Your application can also use user-defined functions, stored procedures, and triggers to centralize application logic, business rules and policies, complex queries, data validation, and referential integrity code on the server instead of on the client.

Ways to upsize

The Upsizing Wizard moves database objects and the data they contain from an Access database to a new or existing SQL Server database.

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 a SQL Server database.
• Create an Access database front-end to a 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 Access database engine (ACE).

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 enough disk space You need enough disk space on the device that will contain the upsized database. The Upsizing Wizard works best when plenty of disk space is available.

• Create unique indexes A linked table must have a unique index to be updatable in Access. The Upsizing Wizard can upsize an existing unique index, but it can't create one if none exists. If you want to update your tables, add a unique index to each Access table before you upsize.

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

The Access 2007 Upsizing Wizard is optimized to work with Microsoft SQL Server 2000 and SQL Server 2005.

Top of Page

Use the Upsizing Wizard

• On the Database Tools tab, in the Move Data group, click SQL Server. The Upsizing Wizard starts.

• Step 1: Choose to upsize to an existing database or a new database

• Step 2: Choose which tables to upsize

• Step 3: Specify the attributes and options to be upsized

• Step 4: Choose how to upsize your application

• The Upsizing Wizard report

Step 1: Choose to upsize to an existing database or a new database

On the first page of the Wizard, you specify whether you want to upsize the Access database to an existing SQL Server database or create a new SQL Server database.

• Use existing database If you select this option and then click Next, Access displays the Select Data Source dialog box so that you can create an ODBC connection to the existing SQL Server database. About ODBC data sources A data source combines the data with the connection information needed to access it. Examples include Access, SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Connection information can include the server location, database name, logon ID, password, and ODBC driver options. In the ODBC architecture, an application such as Access or a Microsoft Visual Basic program connects to the ODBC Driver Manager, which then uses a specific ODBC driver, such as the Microsoft SQL ODBC driver, to connect to a data source. In Access, you use ODBC data sources to connect to external data sources that don't have built-in drivers. To connect to these data sources, you must do the following:

  • Install the appropriate ODBC driver on the computer that contains the data source.
  • Define a data source name (DSN) by using the ODBC Data Source Administrator to store the connection information in the Windows registry, by using a DSN file, or by using a connect string in Visual Basic code to pass the connection information directly to the ODBC Driver Manager. Machine data sources Machine data sources store connection information in the Windows registry on a specific computer under a user-defined name. You can use them only on the computer where they are defined. There are two types: user and system. User data sources are available only to the current user. System data sources are available to all users on a computer and to system-wide services. A machine data source is especially useful when you want extra security because only signed-in users can view it, and a remote user can't copy it to another computer. File data sources File data sources, also called DSN files, store connection information in a text file instead of the Windows registry. They're usually more flexible than machine data sources. For example, you can copy a file data source to any computer that has the correct ODBC driver so your application can use consistent connection information on every computer. Or you can place the file data source on a single server, share it across many computers on the network, and maintain the connection information in one place. A file data source can also be unshareable. An unshareable file data source lives on one computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources. Connect strings In a module, you can define a formatted connect string that specifies connection information. A connect string passes the connection information directly to the ODBC Driver Manager. This simplifies your application because an administrator or user doesn't need to create a DSN before using the database.

• Create new database If you select this option and then click Next, Access displays a page where you enter information about the new SQL Server database.

  • What SQL Server would you like to use for this database? Type the name of the server that you want to use.
  • Use Trusted Connection You can use a trusted connection. This lets SQL Server integrate with Windows security to provide one sign-in to the network and the database.
  • Login ID and Password If you don't use a trusted connection, type the logon ID and password for an account that has CREATE DATABASE privileges on the server.
  • What do you want to name your new SQL Server database? Type the name of the new SQL Server database. Access revises the name if it conflicts with an existing database name and adds a numbered suffix, such as mydatabase 1.

Top of section

Step 2: Choose which tables to upsize

In this step, you select the Access tables that you want to upsize to the SQL Server database. Select the tables that you want to upsize, and then use the arrow buttons to move them to the Export to SQL Server list. Alternatively, you can double-click a table to move it from one list to the other.

The Available Tables list includes all linked tables except for SQL Server tables already in a SQL Server database. Linked tables that point to a SQL Server database that has been selected for upsizing automatically appear in the Export to SQL Server list box and can't be removed. Tables that are not currently visible in the Navigation Pane are also excluded, including hidden tables and system tables.

Top of section

Step 3: Specify the attributes and options to be upsized

In this step, you select which table attributes to upsize to your SQL Server database. By default, all attributes are selected for upsizing.

What table attributes do you want to upsize?

The following table lists the attributes you can upsize, and describes how the Upsizing Wizard handles each one:

Attribute	Action if selected
Indexes	The Upsizing Wizard upsizes all indexes. It converts Access primary keys to SQL Server indexes and marks them as SQL Server primary keys. If you choose to link the upsized SQL Server table to your Access database, the wizard also adds the prefix aaaaa to the index name. Access chooses the first index alphabetically as the primary key, and the aaaaa prefix helps ensure that it chooses the correct index. All other indexes keep their names, except when illegal characters are replaced with _. Unique and non-unique Access indexes become unique and non-unique SQL Server indexes. A linked table must have a unique index to be updatable in Access. The Upsizing Wizard can upsize an existing unique index, but it can't create one if none exists. If you want to update your tables after upsizing, add a unique index to each Access table before you upsize.
Validation rules	The Upsizing Wizard upsizes the following as update and insert triggers: all field Required properties, table validation rules, record validation rules, and field validation rules. A trigger is a series of Transact-SQL statements associated with a SQL Server table. A table can have three triggers: one each for the UPDATE, INSERT, and DELETE commands. The trigger runs automatically when the command runs. The Upsizing Wizard uses triggers instead of SQL Server rules to enforce field-level validation because SQL Server rules don't let you display custom error messages. Each validation rule doesn't necessarily map one-to-one with a trigger. A validation rule might become part of several triggers, and a trigger might contain code that emulates several validation rules. When you set the Required property of an Access field to True, a user can't insert a record and leave the required field null, if no default is bound to the field, or set the field to null when updating a record. Required fields are upsized to fields that don't allow Null values on SQL Server. Validation text The Access database Validation Text property is converted to the Access project Validation Text property. This lets Access display friendly error messages when a constraint violation occurs at run time.
Defaults	The Upsizing Wizard upsizes all Default Value properties to American National Standards Institute (ANSI) default objects.
Table relationships	The Upsizing Wizard upsizes all table relationships. You can choose how to upsize table relationships and referential integrity by using UPDATE, INSERT, or DELETE triggers, or by using Declared Referential Integrity (DRI). DRI works like Access referential integrity by defining primary key constraints for base tables, the "one" side of a one-to-many relationship, and foreign key constraints for foreign tables, usually the "many" side. Use DRI Select Table relationships and Use DRI to use DRI to enforce referential integrity. Access database column validation is converted to a SQL Server DRI check constraint. Validation messages map as follows: Cascade Update = ON DELETE NO ACTION ON UPDATE CASCADE, "The record cannot be deleted because the table <foreigntable> includes related records."; Cascade Delete = ON DELETE CASCADE ON UPDATE NO ACTION, "You cannot add or change a record because a related record is required in table <primarytable>."; Cascade Update and Cascade Delete = ON DELETE CASCADE ON UPDATE CASCADE, no value is set; No DRI = ON DELETE NO ACTION ON UPDATE NO ACTION, "You cannot add, change, or delete this record because of the relationship restriction between the tables <primarytable> and <foreigntable>." Use triggers If you defined cascading updates or deletes in your Access table relationships and want to preserve that behavior in the upsized tables, select Table relationships and Use triggers. This upsizes cascading updates or deletes as triggers to enforce referential integrity. A table relationship doesn't necessarily map one-to-one with a trigger. A relationship might become part of several triggers, or a trigger might contain code that emulates several referential integrity rules. Insert triggers are used on child tables, and delete triggers are used on parent tables. Access requires DRI to read the database diagram of a SQL Server database. To let Access read the database diagram while still enforcing referential integrity through triggers, the Upsizing Wizard places DRI on the relationships but turns off checking of foreign key constraints.

What data options do you want to include?

• Add timestamp fields to tables SQL Server uses a timestamp field to show that a record changed, but not when it changed. It does this by creating a unique value field and updating that field whenever the record is updated. For a linked table, Access uses the value in timestamp fields to determine whether a record changed before updating it. In general, a timestamp field provides the best performance and reliability. Without one, SQL Server must check all fields in the record to determine whether the record changed, which slows performance. The following table describes the settings available in this list:

Setting	Description
Yes, let wizard decide	If the original Access tables contain floating-point (Single or Double), Memo, or OLE object fields, the Upsizing Wizard creates new timestamp fields in the resulting SQL Server tables for those fields.
Yes, always	The Upsizing Wizard creates a timestamp field for all upsized tables regardless of what field types they contain. This improves the performance of upsized Access tables that might not contain Memo, OLE Object, or floating-point fields, but which have fields of other types.
No, never	The Upsizing Wizard does not add timestamp fields to tables.

• Only create the table structure, don't upsize any data The Upsizing Wizard upsizes all data to SQL Server by default. If you select the Only create table structure, don't upsize any data check box, only the data structure is upsized.

Top of section

Step 4: Choose how to upsize your application

On the next page of the wizard, you can choose one of three ways to upsize your Access database application. Under What application changes do you want to make?, select one of the following options:

• Create a new Access client/server application If you select this option, the Upsizing Wizard creates a new Access project. It prompts you for a name, uses the current Access database name by default, adds the CS suffix, and stores the project in the same location as the existing Access database. The Upsizing Wizard creates the Access project file and then upsizes all the database objects from the Access database to the Access project. If you don't save the password and user ID, then the first time you open the Access project, Access displays the Data Link Properties dialog box so that you can connect to a SQL Server database.

• Link SQL Server tables to existing application If you select this option, the Upsizing Wizard modifies your Access database so your queries, forms, reports, and data access pages use data in the new SQL Server database instead of data in your Access database. The Upsizing Wizard renames the Access tables that you upsize by adding the suffix _local. For example, if you upsize a table named Employees, the table is renamed Employees_local in your Access database. Then the Upsizing Wizard creates a linked SQL Server table named Employees.

  Note

  After the upsizing operation is complete, the tables renamed with the _local suffix are no longer used. However, it's a good idea to keep the local tables until you verify that the upsizing succeeded. Later, you can delete the local tables to reduce the size of your Access database. Be sure to back up your database before you delete any tables.

  Queries, forms, reports, and data access pages based on the original Employees table now use the linked SQL Server Employees table. Many field properties from the original local table are inherited by the new local table, including Description, Caption, Format, InputMask, and DecimalPlaces.

• No application changes Select this option if you only want to copy your data to the SQL Server database, and not make any other changes to your existing Access database application.

Save password and user ID By default, the Upsizing Wizard creates linked tables in the existing application or creates an Access project without saving the user name and password. This means users are prompted for a user name and password each time they sign in to a SQL Server database.

If you select Save password and user ID, users can connect to a SQL Server database without signing in each time. If you select Create new Access client/server application, the Access project stores the user name and password in the OLE DB connection string.

The Upsizing Wizard report

When you click Finish, the Upsizing Wizard creates a report that describes all created objects and lists any errors found during the process. The wizard displays the report in Print Preview, where you can print it or save it as an XPS or PDF file. The report isn't saved as an Access object when you close the Print Preview window.

The Upsizing Wizard report contains information about the following:

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

Top of section

Top of Page

How database objects get upsized

The following data and database objects get upsized:

• Data and data types All Access database data types are converted to their SQL Server equivalents. The wizard converts Access text to Unicode by adding the Unicode string identifier to all string values and the Unicode n prefix to all data types.

• Queries

  • 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 reference only tables, also called base queries, and use parameters or an ORDER BY clause are converted to user-defined functions. If needed, the TOP 100 PERCENT clause is added to a query that contains an ORDER BY clause.

  • Parameter queries that use named parameters keep the original text name used in the Access database and are converted to stored procedures or inline user-defined functions.

    Note

    You might need to manually convert queries that did not upsize, such as SQL pass-through queries, data definition queries, and crosstab queries. You might also have to manually upsize queries that were nested too deeply.

• Forms, reports, and controls SQL statements in the RecordSource, ControlSource, and RowSource properties for forms, reports, or controls stay in place and aren't converted to stored procedures or user-defined functions.

• Startup properties The Upsizing Wizard upsizes 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 or macros. You might need to modify your application to take full advantage of SQL Server features. For more information, see the MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server.

Top of Page