Use the Upsizing Wizard

Microsoft Office Access 2003

previous page next page

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

Show Ways to upsize

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. 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 an SQL Server database.
Create an Access database front-end to an 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 Jet database engine.

Show When to upsize an Access database to SQL Server

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 application, consider upsizing to SQL Server to optimize database and application performance, scalability, availability, security, reliability, and recoverability.

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

Show 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. This means your database can be running up to 24 hours a day, seven days a week.

Show Improved security

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.

Show 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. Critical applications can be up and running again right away.

Show Server-based processing

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, stored procedures, and triggers 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.

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

For a SQL Server 7.0 or later database, SQL Server automatically grows your database for you as it's being created.

For a SQL Server 6.5 database, multiply the size of your Access database by two and reserve that amount of space on your disk. 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.

Create unique 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.
Set a default printer You must set a default printer to use the Upsizing Wizard if you want it it to automatically create a report snapshot of the Upsizing Wizard report.
Assign yourself appropriate permissions on the Access database You need READ and DESIGN permission on all database objects to upsize them.
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.
- To create new devices, you must be a system administrator.
If desired, create multiple disk devices If you are upsizing to a SQL Server version 6.5 database, you may want to create devices before running the Upsizing Wizard. The Upsizing Wizard creates all new devices on the same physical disk where the Master database device resides. If your server has multiple disks, you can place your database on one disk and the transaction log on a different disk. In the event of a disk failure, you can recover your database. Make sure that you have devices that are big enough on both disks. Create new devices if necessary.

SQL Server version 6.5 also allows databases and transaction logs to span several devices. However, the Upsizing Wizard only allows you to specify one device for your database and one device for the transaction log. To specify multiple devices for a database or transaction log, make those devices (and no other devices) the default devices. Then run the Upsizing Wizard, and select the default database or transaction log device.

Note If the size of the new SQL Server database or the size of the transaction log doesn't require using all the devices set as default, SQL Server uses only the devices necessary to accommodate the database or transaction log.

Show The Upsizing Wizard report

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

Database details, including database size, as well as transaction logs and device names and sizes for a SQL Server 6.5 database.
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.

Show How database objects get upsized

The following data and database objects get upsized:

Show Data and data types

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.

Show Queries

For SQL Server 2000 databases:

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.

For SQL server 7.0 and 6.5 databases:

Select queries are converted to views.
Sorted queries are converted to a combination of views and stored procedures to allow for nesting and sorting (Views can be nested but can't contain ORDER BY clauses; stored procedures can contain ORDER BY clauses but can't be nested).
Parameter queries, queries that depend on a parameter query, and action queries are converted to stored procedures.

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.

Show Forms, reports, and controls

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.

Show Data access pages

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.

Show Customized command bars and startup properties

For all versions of SQL Server, the Upsizing Wizard upsizes any custom command bars as well as the following startup properties:

StartUpShowDBWindow
StartUpShowStatusBar
AllowShortcutMenus
AllowFullMenus
AllowBuiltInToolbars
AllowToolbarChanges
AllowSpecialKeys
UseAppIconForFrmRpt
AppIcon
AppTitle
StartUpForm
StartUpMenuBar
StartupShortcutMenuBar

Show Modules and macros

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) to ActiveX Data Objects (ADO) 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 2000/Visual Basic Programmer's Guide.

Show Choose to upsize to an existing database or new database

Show Use existing database

If you select Use existing database, Microsoft Access displays the ODBC Select Data Source dialog box so you can create an ODBC connection to the existing Microsoft SQL Server database. For more information about ODBC data sources, Press Help.

Show Create new database

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

Show About ODBC data sources

An ODBC data source— for example, a database and the server on which it resides— is accessed through an Open Database Connectivity (ODBC) driver.

Show What is a data source?

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

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

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

Show Connect strings

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.

Show Enter database and transaction log devices and sizes for a Microsoft SQL Server version 6.5 database

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.

Show Enter a database device and size

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.

Show Enter a transaction log device and size

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.

Show Enter a database and transaction log size

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.

Show Choose which tables to upsize

You can select the Microsoft Access database tables you want to upsize to a Microsoft SQL Server database.

Show Available Tables / Export to SQL Server

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 are also excluded, including hidden tables and system tables.

Show Why aliasing queries may be created in your Access database

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.

Show The effect of Declarative Referential Integrity (DRI) on table upsizing

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

Show Choose which table attributes to upsize

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.

Show What table attributes do you want to upsize?

Show Indexes

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.

Show Validation rules

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.

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

Show Defaults

If you select the Defaults check box, the Upsizing Wizard will upsize all Default Value properties to American National Standards Institute (ANSI) default objects.

Show 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 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).

Use triggers If your Microsoft Access table relationships have cascading updates or deletes defined, you may want to preserve this behavior in upsized tables. To do this, select the Table relationships check box and Use triggers radio button to upsize any cascading updates or deletes as triggers to enforce referential integrity.
A table relation doesn't necessarily have a one-to-one correspondence with a trigger. Each relation may become part of several triggers or each trigger may contain code to emulate the functionality of several referential integrity rules. Insert triggers are used on child tables and delete triggers on parent tables.

Note Access requires DRI to read the database diagram of an 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.

Use DRI Select the Table relationships check box and Use DRI radio button, to use DRI to enforce referential integrity. The Upsizing Wizard performs differently depending on the version of SQL Server you are upsizing to.

SQL Server 2000 database If you select Use DRI, then Access database column validation is converted to a SQL Server DRI check constraint with a validation message as the following table shows.

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

SQL Server version 7.0 and 6.5 databases These databases do not support cascading update or deletes. Your Access database may not have cascading updates or deletes defined, or you don't care if you lose these features in the upsized database. The Upsizing Wizard warns you that you will lose the cascading update and delete functionality.

Show What data options do you want to include?

Show Add timestamp fields to tables

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.

If you select the default value, Yes, let wizard decide, the Upsizing Wizard creates new fields with the timestamp data type in SQL Server tables that have been upsized from Microsoft Access tables that contain floating-point (Single or Double), Memo, or OLE object fields.
You can also choose to have the Upsizing Wizard create a timestamp field for all upsized tables regardless of what field types they contain by selecting Yes, always. This improves the performance of upsized Access tables that may not contain Memo, OLE Object, or floating-point fields, but have fields of other types.
If you select the No, Never option, the Upsizing Wizard does not add timestamp fields to tables.

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.

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

Show Choose how to upsize your application

You can select different ways to upsize your Microsoft Access database application.

Show What application changes do you want to make?

Show Create a new Access client/server application

If you select Create new Access client/server application, the Upsizing Wizard creates a new Microsoft 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 an SQL Server database.

Show How database objects are upsized

The following data and database objects get upsized:

Show Data and data types

All Access database data types are converted to their equivalent in SQL Server.

Show Queries

For SQL Server 2000 databases:

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.

For SQL server 7.0 and 6.5 databases:

Select queries are converted to views.
Sorted queries are converted to a combination of views and stored procedures to allow for nesting and sorting (Views can be nested but can't contain ORDER BY clauses; stored procedures can contain ORDER BY clauses but can't be nested).
Parameter queries, queries that depend on a parameter query, and action queries are converted to stored procedures.

Show Forms, reports, and controls

Show Data access pages

Show Customized command bars and startup properties

For all versions of SQL Server, the Upsizing Wizard upsizes any custom command bars as well as the following startup properties:

Show Modules and macros

Show Link SQL Server tables to existing application

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.

Show No application changes

Select No application changes if you only want to upsize your data and not make any other changes to your existing Access database application.

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

Show Troubleshoot the Upsizing Wizard

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 with the same name as the Access database (for example, Northwind.snp), and stores it in the default database folder.

Show I can't find my switchboard manager form.

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.

Show I can't upsize my MDE file.

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.

Show My DDE code doesn't work in my Access project

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

Show My upsized table contains no records.

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.

Show I ran out of disk space and my SQL Server database was not completely upsized.

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.

Show I'm having problems accessing or creating database objects

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:

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.
To create new devices, you must be a system administrator.

Show I can’t upsize my data access pages

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:

Copy all page files and related HTML files to your local computer.
Open each page in the database window and change its file location.
Run the Upsizing Wizard.
Test your pages in the new Access project.
Move the pages and all related HTML files back to the original Web site.

previous page start next page