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 from an Access database to a new or existing SQL Server database or new Access project (.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, stored procedures, and triggers 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:
-
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 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.
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 connection to the existing 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.
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).
-
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".
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:
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 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) 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).
|
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, 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. |
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.
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. 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:
-
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 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.
-
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 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, 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 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:
StartUpShowDBWindow
StartUpShowStatusBar
AllowShortcutMenus
AllowFullMenus
AllowBuiltInToolbars
AllowToolbarChanges
AllowSpecialKeys
UseAppIconForFrmRpt
AppIcon
AppTitle
StartUpForm
StartUpMenuBar
StartupShortcutMenuBar -
Modules and macros The Upsizing Wizard does not make any changes to modules or macros. You might need to modify your application to take full advantage of SQL Server's features. For more information, see the MSDN article Optimizing Microsoft Office Access Applications Linked to SQL Server.
No comments:
Post a Comment