You can select which table attributes to upsize to your Microsoft SQL Server database. At a minimum, 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. By default, all attributes are selected for upsizing. This topic provides reference information about what happens when you decide to:
Add timestamp fields to tables
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.
Return to top
If you select the Validation rules check box, the Upsizing Wizard upsizes all table, record, and field validation rules, and field Required properties 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 or 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. The Upsizing Wizard also 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.
Return to top
If you select the Defaults check box, the Upsizing Wizard will upsize all default values as American National Standards Institute (ANSI) defaults, not as default objects bound to the appropriate SQL Server field. Microsoft SQL Server defaults, unlike Microsoft Access defaults, are independent of any particular field or table. Once you create a default, you can bind it to any number of different fields.
Return to top
You can decide how to upsize table relationships and referential integrity by using either update, insert, or delete triggers, or 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). However, SQL Server DRI doesn't have the Microsoft Access cascading update or delete features.
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 The Microsoft SQL Server Design Tool requires Declared Referential Integrity (DRI) to read the database diagram of an SQL Server database. To allow the SQL Server Design Tools 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.
Return to top
Microsoft SQL Server uses a timestamp field to indicate that a record was changed (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.
Return to top
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.
Return to top