| 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. Use the Upsizing Wizard
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). 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. 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. There are three ways to use 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 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.
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 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. 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. Using a trusted connection, SQL Server can integrate with the Windows 2000 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. A SQL Server database on a server also employs innovative security features to help prevent unauthorized users from getting to the database file directly, but rather they must access the server first. 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. 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.
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:
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. The Upsizing Wizard report contains information about the following:
The following data and database objects get upsized: 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. For SQL Server 2000 databases:
For SQL server 7.0 and 6.5 databases:
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.
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.
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.
For all versions of SQL Server, the Upsizing Wizard upsizes any custom command bars as well as the following startup properties: StartUpShowDBWindow 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 an SQL Data Definition Language). For more information on developing optimized client/server applications, see the Microsoft Office XP Developer's Guide.
If you select Use existing database, Microsoft Access displays the ODBC Select Data Source dialog box so 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 Microsoft SQL Server database. For more information about ODBC data sources, Press Help. If you select Create new database, Microsoft Access displays the Microsoft SQL Server Database Wizard. Follow the instructions in the SQL Server Database Wizard. 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 2000 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). An ODBC data source (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.) A data source is a source of data and the connection information needed to access that data. Examples of data sources are Microsoft Access, Microsoft 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 Microsoft SQL Server database (SQL database: A database that is based on Structured Query Language (SQL).)). 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:
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 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. If you are a programmer, you can define a formatted connect string in your Microsoft Visual Basic code that specifies the connection information. Using a connect string avoids defining a machine or file DSN and passes the connection information directly to the ODBC Driver Manager. This is useful, for example, when you want to avoid requiring system administrators or users to first create a DSN, or to simplify the installation of your application. To help protect the connection string information in your code, employ security methods for your code by using a MDE file or a password.
If you choose to create a new Microsoft SQL Server 6.5 database, the Upsizing Wizard asks you to enter devices and sizes for your database and transaction log. It also asks you to enter a pre-allocated size for the database and transactions.
All Microsoft SQL Server databases and transaction logs are placed on devices. A device is both a physical file and a logical location for databases and transaction logs. When you create a device, SQL Server creates a file and reserves a set amount of disk space for its own use. If you are a system administrator, the Upsizing Wizard displays the amount of free space available on the existing devices. Select a device that has at least enough free space for your estimated database size. If no existing devices have enough free space, you can create a new device using the Upsizing Wizard. New devices should be at least as big as your estimated database size. If possible, make the device larger than your database size, so you can expand your database later, or place other databases or transaction logs on the same device.
The transaction log is a chronological record of all activity that occurs on the server, SQL Server uses this log for database backup and recovery. The transaction log device you select must have at least one megabyte of space available. Ideally, a database and its corresponding transaction log should be placed on devices that are on separate physical disks. These devices must be created before starting the Upsizing Wizard, because the Upsizing Wizard creates all new devices on the same physical disk where the Master database device resides. If you have only one physical disk, you should place the database and its transaction log on separate devices, so that the log will be available even if the database encounters problems. The Upsizing Wizard creates the transaction log using the size you specify. The size of your transaction log should be at least as large as the size of all of the upsized tables in your database. The size you specify can't exceed the space available on the transaction log device you selected. Note For new databases, the Upsizing Wizard truncates the transaction log file on checkpoint if it fills up before the upsizing is complete. However, it's possible for the transaction log to overflow on a single table. If this happens, Microsoft Access doesn't upsize the table and writes the error to the Upsizing Wizard report.
When SQL Server 6.5 creates a database, it sets aside a fixed amount of space for that database on one or more devices. Not all of this space is necessarily used by the database right away. The database size is the maximum size a database can become before it runs out of space. To estimate the space needed for your database, look at the size of your Access database and estimate the rate at which your new SQL Server database will grow. In general, every megabyte of Access data requires at least 1.3 to 1.5 megabytes on SQL Server. Note that the size of a Access database (.mdb) file includes all Access database objects, not just data. If you have ample disk space on your server, multiply the size of your Access database by two. 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. The transaction log size should be about 25% of the database size. You can select the Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) tables you want to upsize to a Microsoft SQL Server database.
The Available Tables list box includes all linked tables except for SQL Server tables already in an SQL Server database. Linked tables that point to an SQL Server database that has been selected for upsizing automatically appear in the Export to SQL Server list box and can't be removed. 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". Tables that are not currently visible in the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.) are also excluded, including hidden tables and system tables.
If you choose to link SQL Server tables to your Access database, the Upsizing Wizard may create an aliasing query to handle name differences between Access and SQL Server version 6.5, which doesn't allow spaces or special characters in field names other than pound sign (#), dollar sign ($), and underscore (_) characters. The Upsizing Wizard automatically replaces spaces and illegal characters with the "_" character. If field names are changed when a table is upsized, the Upsizing Wizard names the linked table with the suffix "_remote" (for example, Employees_remote) and renames the original table by adding the suffix "_local" (for example, Employees_local). The Upsizing Wizard then creates an aliasing query called Employees so that forms, reports, and queries continue to work properly on the new linked SQL Server table.
If you have previously selected Declarative Referential Integrity (DRI) to upsize tables, DRI prevents you from overwriting related tables that were previously upsized. You can drop an upsized SQL Server database by using the Drop SQL Database command (Tools menu, Database Utilities submenu) in SQL Server 2000 Desktop Engine, or by using SQL Server's Enterprise Manager, and then upsizing the Access database again. If you want to re-upsize only some tables, you must delete those tables and any other tables they are related to starting with the "many" table in a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) (a table that has no references to its primary key from another table), and then upsize the entire set of tables again. The Upsizing Wizard can only establish DRI relationships between related tables that are upsized at the same time, but it can't establish relationships between those tables and pre-existing tables on the server.
You can select which table attributes to upsize to your Microsoft SQL Server database. At a minimum, the Upsizing Wizard converts Microsoft Access field names to legal SQL Server field names, and converts Access data types to the equivalent SQL Server data types. By default, all attributes are selected for upsizing.
If you select the Indexes check box, the Upsizing Wizard upsizes all indexes. The Upsizing Wizard converts Microsoft Access primary keys to Microsoft SQL Server non-clustered, unique 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." Microsoft 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 Microsoft Access indexes become unique and non-unique SQL Server indexes. Note that SQL Server doesn't support ascending or descending 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. Validation rules If you select the Validation rules check box, the Upsizing Wizard upsizes all field Required properties and table, record, and field validation rules 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.). A trigger is a series of Transact-SQL statements associated with an 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 may become part of several triggers, and each trigger may contain code to emulate the functionality of several validation rules. When you set the Required property of a Microsoft 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 Null.) values on SQL Server. Validation text For SQL Server 2000 databases, 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. For SQL Server 7.0 and 6.5 databases, the Upsizing Wizard creates a fragment of Transact-SQL code that presents a message similar to the one Microsoft Access displays if the field is null when a record is added or updated. If you select the Defaults check box, the Upsizing Wizard will upsize 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. 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 Microsoft 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).
Microsoft 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.
Note In linked SQL Server tables, Microsoft 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, Microsoft Access overwrites the change. Also, the value of a floating-point field may appear to have changed when it hasn't, so in the absence of a timestamp field, Microsoft Access may determine that the record has been changed when it has not.
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.
You can select different ways to upsize your Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) application.
If you select Create new Access client/server application, the Upsizing Wizard creates a 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.). 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 an SQL Server database.
The following data and database objects get upsized: 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. For SQL Server 2000 databases:
For SQL server 7.0 and 6.5 databases:
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.
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.
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.
For all versions of SQL Server, the Upsizing Wizard upsizes any custom command bars as well as the following startup properties: StartUpShowDBWindow 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 an SQL Data Definition Language). For more information on developing optimized client/server applications, see the Microsoft Office XP Developer's Guide.
If you select Link SQL Server tables to existing application, the Upsizing Wizard can modify your Access database so that your queries, forms, reports, and data access pages use the data in the new Microsoft SQL Server database rather than the data in your Microsoft Access database. The Upsizing Wizard renames the Microsoft 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. 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. Select No application changes if you only want to upsize your data and not make any other changes to your existing Access database application. 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 an SQL Server database. If you select Save password and user ID, users can access an 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.
Note 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.
The Upsizing Wizard doesn't upsize a switchboard manager form. The Switchboard Manager uses a table to store information about the form and is not designed for a client-server environment. When you save a Microsoft Access database as an MDE file, all modules and source code within the database are compiled and removed. Your Visual Basic for Applications code will continue to run, but you can't view it or edit it. If you run upsizing tools with the Create a new Access client/server application option, the Upsizing Wizard must have access to objects that were removed when you saved the database as an MDE file. You may select either the No application changes or Link SQL Server tables to an existing application options when upsizing an MDE, but if you want to create a client-server application, you must use the original Access database (.mdb) that the MDE file was created from.
DDE (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.) code is not supported in an Access project. If you upsize an Access database to an Access project, you will need to convert the DDE code to ADO code.
A field in the table in the Access database may contain a null value for more than one record, but the Indexed property is set to Yes (No Duplicates) and the Required property is set to No. If a field in your table is indexed using these property settings, do not add more than one record that contains a null value to the column. Remove the records with the null values and then upsize the table again.
If the Upsizing Wizard runs out of disk space on the server, the wizard halts, leaving a partial database and any devices it created on the server. If this happens, you can drop databases by using the Drop Database command (Tools menu, Database Utilities submenu) in SQL Server 2000 Desktop Engine, or by using SQL Server Enterprise Manager. For more information on SQL Server Enterprise Manager, see the SQL Server documentation.
You may not have the correct permissions. You need read/design permission on all Access database objects to upsize them. You need the following permissions in a SQL Server database:
If the pages in your Access database are located on a web site, then the Upsizing Wizard can’t copy them. To upsize these pages:
|
Email this link
Printer-Friendly Version
Share
Show All
Hide All
