What table attributes do you want 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.
What table attributes do you want to upsize?
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.
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.
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.
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).
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.
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.
What data options do you want to include?
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.
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.
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.