Microsoft Office Online
Sign in to My Office Online (What's this?) | Sign in

 
 
Microsoft Office Access
Search
Search
 
Check for updates: (c) Microsoft
Office downloads
 
 
 
Product Information
Help and How-to
Choose your version
Training
Templates
Support and Feedback
Technical Resources
Additional Resources
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 linkEmail this link Printer-Friendly VersionPrinter-Friendly Version Bookmark and ShareShare
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 in the life of your Microsoft Office Access application, you might want to consider upsizing to it to a Microsoft SQL Server database to optimize performance, scalability, availability, security, reliability, and recoverability.

In this article

About upsizing a Microsoft Office Access database

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 an Access database to a new or existing SQL Server database or new 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).

Benefits of upsizing a database to SQL Server

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:

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 database. The Upsizing Wizard works best when there is plenty of disk space available.

  • Create unique indexes   A linked table must have a unique index to be updateable in 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.

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

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 (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.) connection to the existing SQL Server database.

    ShowAbout ODBC data sources

    A data source is a source of data combined with the connection information needed to access that data. Examples of data sources are Access, SQL Server, Oracle RDBMS, a spreadsheet, and a text file. Examples of connection information include server location, database name, logon ID, password, and various ODBC driver options that describe how to connect to the data source.

    In the ODBC architecture, an application (such as Access or a Microsoft Visual Basic program) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (for example, Microsoft SQL ODBC driver) to connect to a data source (in this case, a SQL Server database). In Access, you use ODBC data sources to connect to data sources external to Access that do not 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 either the ODBC Data Source Administrator to store the connection information in the Microsoft Windows registry or a DSN file, or 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 with a user-defined name. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer and system-wide services. A machine data source is especially useful when you want to provide added security, because only users who are logged on can view a machine data source and it cannot be copied by a remote user to another computer.

    File data sources

    File data sources (also called DSN files) store connection information in a text file, not the Windows registry, and are generally more flexible to use than machine data sources. For example you can copy a file data source to any computer with the correct ODBC driver so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.

    A file data source can also be unshareable. An unshareable file data source resides on a single 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, and it helps simplify your application by removing the requirement that a system administrator or user first 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 you would like to use.
    • Use Trusted Connection   You can use a trusted connection, that is, SQL Server can integrate with the Windows operating system security to provide a single log on to the network and the database.
    • Login ID and Password   If you don't use a trusted connection, type the logon ID and password of an account with 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 (mydatabase 1, for example).
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.

 Tip   Any table that has a name ending in "_local" is excluded from the list of available tables to prevent upsizing tables that have already been upsized. If you do want to upsize these tables again, rename them before you run the Upsizing Wizard by removing the suffix "_local".

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 by default.

 Note   By default, the Upsizing Wizard converts Access field names to legal SQL Server field names, and converts Access data types to the equivalent SQL Server data types.

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.

The Upsizing Wizard 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 Upsizing Wizard also adds the prefix "aaaaa" to the index name. This is because Access chooses the index that is first alphabetically in the list of available indexes as the primary key and the "aaaaa" prefix ensures that the right index is chosen.

All other indexes retain their names, except where illegal characters are replaced with the "_" character. Unique and non-unique Access indexes become unique and non-unique SQL Server indexes.

A linked table must have a unique index to be updateable in 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 the data in your tables after upsizing them, make sure you add a unique index to each Access table before upsizing.
Validation rules The Upsizing Wizard upsizes the following as update and insert 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.):
  • All field Required properties
  • Table validation rules
  • Record validation rules
  • 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 for each of the commands that can modify data in a table: the UPDATE, INSERT, and DELETE commands. The trigger is automatically executed when the command is carried out. The Upsizing Wizard uses triggers rather than SQL Server rules to enforce field level validation because SQL Server rules do not allow you to display custom error messages.

Each validation rule doesn't necessarily have a one-to-one correspondence with a trigger. Each validation rule might become part of several triggers, and each trigger might contain code to emulate the functionality of several validation rules.

When you set the Required property of an Access field to true, a user cannot insert a record and leave the required field null (if there is no default bound to the field) or make the field null when updating a record. Required fields are upsized to fields that don't allow Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) values on SQL Server.

Validation text

The Access database Validation Text property is converted to the Access project Validation Text property. This enables the Access friendly error messages to be displayed in the event of a constraint violation at run time.
Defaults The Upsizing Wizard upsizes all Default Value properties to American National Standards Institute (ANSI) (ANSI character set: An 8-bit character set used by Microsoft Windows that allows you to represent up to 256 characters (0 through 255) by using your keyboard. The ASCII character set is a subset of the ANSI set.) default objects.
Table relationships The Upsizing Wizard upsizes all table relationships.

You can decide how to upsize table relationships and referential integrity by using update, insert, or delete triggers, or by using Declared Referential Integrity (DRI). DRI works the same way as 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 (typically the "many" side of a one-to-many relationship).

  • 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 with a validation message as shown in the following table.
    Access database relationship SQL Server Foreign Key Validation Text
    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 Value is not 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 have defined cascading updates or deletes in your Access table relationships, and you want to preserve this behavior in the upsized tables, select the Table relationships and Use triggers. This upsizes cascading updates or deletes as triggers to enforce referential integrity.

    A table relationship doesn't necessarily have a one-to-one correspondence with a trigger. Each relationship might become part of several triggers or each trigger might contain code to emulate the functionality of several referential integrity rules. Insert triggers are used on child tables and delete triggers are used on parent tables.

     Note   Access requires DRI to read the database diagram (database diagram: A graphical representation of any portion of a database schema. It can be either a whole or partial picture of the structure of the database. It includes tables, the columns they contain, and the relationships between the tables.) of a SQL Server database. To allow Access to read the database diagram while 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 indicate that a record was changed (but not when it was changed) by creating a unique value field and then updating this field whenever a record is updated. For a linked table, Access uses the value in timestamp fields to determine whether a record has been changed before updating it. In general, a timestamp field provides the best performance and reliability. Without a timestamp field, SQL Server must check all the fields in the record to determine if the record has 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 (Memo data type: In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters.), or OLE object (OLE object: An object supporting the OLE protocol for object linking and embedding. An OLE object from an OLE server (for example, a Windows Paint picture or a Microsoft Excel worksheet) can be linked or embedded in a field, form, or report.) 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.

     Important   In linked SQL Server tables, Access doesn't check to determine if Memo or OLE object fields have changed because these fields could be many megabytes in size and the comparison could be too network-intensive and time-consuming. Therefore, if only a text or image field has changed and there is no timestamp field, Access overwrites the change. Also, the value of a floating-point field might appear to have changed when it hasn't, so in the absence of a timestamp field, Access might determine that the record has been changed when it has not.

  • 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 select one of three different 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 (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.). The Upsizing Wizard prompts you for a name, which defaults to the current Access database name, adds a "CS" suffix, and then 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 that your queries, forms, reports, and data access pages use the data in the new SQL Server database rather than the data in your Access database. The Upsizing Wizard renames the Access tables you upsize with 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 that were renamed with the "_local" suffix will no longer be used. However, it is a good idea to retain the local tables until you verify that the upsizing was successful. At a later date, you can delete the local tables to reduce the size of your Access database. Be sure to back up your database prior to deleting any tables.

    Queries, forms, reports, and data access pages based on the original Employees tables will now use the linked SQL Server Employees table. Many of the properties of the fields in 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 username and password. This means that users are prompted for a username and password each time they log on to a SQL Server database.

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

 Note   This option is disabled for the No application changes option if a linked SQL Server table is configured with an MSysConf table to deny saving passwords.

The Upsizing Wizard report

When you click Finish, 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 displays the report in Print Preview, and you can then print or save the report, for example, as an XPS or PDF file. The report is not saved as an Access object when you close the Print Preview window.

The Upsizing Wizard report contains information about the following:

Top of section

Top of Page Top of Page

How database objects get upsized

The following data and database objects get upsized:

Top of Page Top of Page

advertisement
Printer-Friendly VersionPrinter-Friendly Version