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
- 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 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.
- Improved security Using a trusted connection, SQL Server can integrate with Windows system security to provide a single integrated access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes.
- 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.
- Server-based processing Using SQL Server in a client/server configuration reduces network traffic by processing database queries on the server before sending results to the client. Having the server do the processing is usually much more efficient, especially when working with large data sets.
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, data validation, and referential integrity code on the server, rather than 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:
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
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.
About 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.
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 (Navigation Pane: The pane that appears when you open an Access database or an Access project. The Navigation Pane displays the objects in the database, and can be customized to sort and group objects in different ways.) 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:
||Action if selected
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.
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.
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.
||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.
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
||ON DELETE NO ACTION ON UPDATE CASCADE
||"The record cannot be deleted because the table <foreigntable> includes related records."
||ON DELETE CASCADE ON UPDATE NO ACTION
||"You cannot add or change a record because a related record is required in table <primarytable>."
and Cascade Delete
|ON DELETE CASCADE ON UPDATE CASCADE
||Value is not set.
||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:
|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.
||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.
||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:
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:
- 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.
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 equivalent in SQL Server. The 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.
- 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.
Note You might need to manually convert queries that did not upsize, such as SQL pass-through queries (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of the data being processed by the Access database engine.), data definition queries (data definition query: An SQL-specific query that can create, alter, or delete a table, or create or delete an index in a database. ANSI defines these as DDL queries and uses the tokens CREATE, DROP, and ALTER.), and crosstab queries (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.). You might also have to manually upsize queries that were nested too deeply.
- Forms, reports, and controls SQL statements in RecordSource, ControlsSource and RowSource properties for forms, reports, or controls are kept in place and are not converted to stored procedures or user-defined functions.
- Startup properties The Upsizing Wizard upsizes the following startup properties:
Top of Page