Overview of Object Mapping

To upsize a Microsoft Access database to SQL Server, the Upsizing Wizard creates SQL Server objects that, as far as possible, do everything the Microsoft Access database did.

In some cases, mapping Microsoft Access objects to SQL Server objects is very straightforward. Microsoft Access databases, tables, fields, defaults, and indexes map to SQL Server databases, tables, fields, defaults, and indexes. This is a direct, one-to-one mapping.

However, this is not the case for all objects. Validation rules and referential integrity, in Microsoft Access, are part of the data dictionary and are enforced at the engine level. SQL Server validation rules and referential integrity are not part of the data dictionary, and are enforced through code bound to a table.

These differences, as well as design decisions made by the Upsizing Wizard, mean that much of the Microsoft Access data dictionary cannot be mapped directly to SQL Server constructs.

The following table summarizes how objects are mapped from Microsoft Access objects to SQL Server:

The following sections discuss each Microsoft Access object and the SQL Server object (or objects) to which it maps.

Database and Table Objects

A Microsoft Access .MDB file maps directly to a SQL Server database. A Microsoft Access table, excluding much of its data dictionary, maps to a SQL Server table.

The Upsizing Wizard replaces illegal characters with the "_" symbol. Any names that are SQL Server keywords, FROM or GROUP for example, have the "_" symbol appended to them, resulting in the names FROM_ and GROUP_.

Attachments to New Server Tables

If you selected the Attach newly created SQL Server tables check box, the Upsizing Wizard will create these attachments as well as give them many of the properties of the fields in the original local table.

Fields in attached tables inherit the following properties from the original fields:

• Description

• Caption

• Format

• InputMask

• DecimalPlaces

Indexes

SQL Server and Microsoft Access indexes are very similar. Microsoft Access primary keys are converted to SQL Server clustered, unique indexes, and are always named "aaaaa_PrimaryKey." When attaching to a remote table, Microsoft Access chooses the index that is first alphabetically in the list of available indexes as the primary key. The name "aaaaa_PrimaryKey" ensures that the right index is chosen. All other indexes retain their names, except where they contain illegal characters. Illegal characters are replaced with the "_" symbol.

Unique and non-unique Microsoft Access indexes become unique and non-unique SQL Server indexes. SQL Server doesn't support ascending or descending indexes.

Relationship Indexes

When you create a relationship between two Microsoft Access tables, a new index on the foreign key in the relationship is created automatically. These system-generated indexes do not appear in the Microsoft Access index editor.

Microsoft Access names these relationship indexes "Reference" and, if the index name is not unique within a database, adds a suffix. Because an index named "Reference" could also be created by a user, the Upsizing Wizard exports all indexes, and does not distinguish between system-generated indexes and user-created indexes.

These system-generated relationship indexes improve performance when tables are joined. However, if you end up with two identical indexes, one user created and the other system generated, you can drop one of the indexes.

Example

The following table lists the indexes created when an Employees table is upsized.

The index "Reference" is a relationship index created by Microsoft Access on the Dept ID field, which serves as the foreign key for a Departments table.

Fields

Field names and data types are automatically translated into SQL Server fields when a Microsoft Access table is exported by the Upsizing Wizard.

Microsoft Access data types map to SQL Server data types as follows:

Defaults

A Microsoft Access default expression maps directly to a single SQL Server default. While largely similar, there are some differences in the way defaults are created and behave in the two products.

SQL Server defaults are independent of any particular field or table. Once a default has been created, it can be used or "bound" to any number of different fields. The Upsizing Wizard tries to create a SQL Server default based on the default expression for a Microsoft Access field.

Defaults created by the Upsizing Wizard are named according to the SQL Server table to which they are bound, with a number that represents the position of the field in the Microsoft Access table definition. (This is the same as the order in which the fields appear in table design view.)

If two or more fields have the same nonzero default expression, the Upsizing Wizard creates two defaults that are functionally identical with different names. Fields with a default expression of zero are bound to a default named UW_ZeroDefault.

If any exported tables contain counter fields, the UW_ZeroDefault is automatically created and bound to each of those fields, whether or not you chose to export defaults. Also, any Yes/No fields that don't have a default will automatically have a "no" default bound to them. This makes interaction between Microsoft Access and SQL Server much smoother.

The upsizing report will indicate whether the Upsizing Wizard was successful in translating the Microsoft Access Basic expression to SQL Server Transact-SQL. If the default was successfully created, the wizard binds it to the appropriate SQL Server field.

Triggers

Counter fields, validation rules, and table relations map to SQL Server triggers. A trigger is a series of Transact-SQL statements associated with a particular SQL Server table. The Upsizing Wizard creates triggers when you export validation rules or table relationships, or when your table contains a counter field.

Validation rules and table relations do not map directly to triggers. Each rule or relation may become part of several triggers. Each trigger may contain code to emulate the functionality of several validation and referential integrity rules.

A table can have three triggers, one for each of the commands that can modify data in the table: the UPDATE, INSERT, and DELETE commands. The trigger is automatically executed when the command is carried out.

The following table describes the triggers created by the Upsizing Wizard. Any specific trigger may contain code to emulate one, all, or none of the Microsoft Access functions listed.

Counter Fields

Counter fields in Microsoft Access are long integer fields that are automatically incremented. SQL Server doesn't support the counter data type, so the Upsizing Wizard includes code in the INSERT triggers that provides equivalent functionality.

Example

The Transact-SQL code below was generated as part of upsizing a Tasks table, and is contained in the trigger called Tasks_ITrigger. This code provides the same functionality as the counter field "Task ID" in a Microsoft Access table Tasks.

DECLARE @maxc int, @newc int
SELECT @maxc = (SELECT Max(Task_ID) FROM Tasks) 
SELECT @newc = (SELECT Task_ID FROM inserted) 
IF @newc = 0 OR @maxc <> @newc SELECT @maxc = @maxc + 1 
UPDATE Tasks SET Task_ID = @maxc WHERE Task_ID = @newc

Validation Rules

The Upsizing Wizard can export table validation rules and field validation.

For each table, the Upsizing Wizard:

• Converts each validation rule to Transact-SQL, if possible. The upsizing report will indicate whether conversion was successful.

• Creates a fragment of Transact-SQL code for each Microsoft Access field where the Required property is set to true. This code presents an error message, similar to the one Microsoft Access displays if the field is null when a record is added or updated.

• Combines all the converted validation rules and Required property code.

• Places a copy of the combined code into both an UPDATE and an INSERT trigger on the SQL Server table.

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

Example

The following code is contained in both the Tasks_Itrig and Tasks_Utrig triggers, which are associated with the Tasks table.

This code is the SQL Server equivalent of two Microsoft Access field level validation rules (for the Status and Date Completed fields) and one Microsoft Access table validation rule.

...
ELSE IF (SELECT Count(*) FROM inserted 
WHERE NOT (Status In ('Not Started','Started','Done'))) > 0
  BEGIN
    RAISERROR 44444 "Status must be one of: 'Not 
Started','Started','Done'"
    ROLLBACK TRANSACTION
  END
ELSE IF (SELECT Count(*) FROM inserted 
WHERE NOT (Date_Completed Is Null Or Date_Completed>'01/1/94')) > 0
  BEGIN
    RAISERROR 44444 "Date_Completed must be after 1/1/94"
    ROLLBACK TRANSACTION
  END
ELSE IF (SELECT Count(*) FROM inserted 
WHERE NOT (Status="Done" Or Date_Completed Is Null)) > 0
  BEGIN
    RAISERROR 44444 "Status must be 'Done' before you enter Date_Completed"
    ROLLBACK TRANSACTION
  END

Note The user-defined error number of 44444 is used for all instances where validation rules (table validation, field validation, and the Required property) are violated.

Required Property

When the Required property of a Microsoft Access field is set 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. The Upsizing Wizard can generate SQL Server Transact code to emulate the Required property.

The Upsizing Wizard uses triggers to emulate the Required property, rather than the SQL Server engine–enforced equivalent of this functionality, so that you can allow or disallow nulls by changing the trigger.

Example

The following code is generated because the Required property of the Emp ID field in the Tasks table is set to true. The code is contained in both the Tasks_Utrig and Tasks_Itrig triggers of the Tasks table.

IF (SELECT Count(*) FROM inserted WHERE Emp_ID IS NULL) > 0
  BEGIN
    RAISERROR 44444 "Emp_ID may not be NULL"
    ROLLBACK TRANSACTION
  END

Table Relationships

The Upsizing Wizard creates triggers that include the Transact-SQL code required to duplicate Microsoft Access table relationships. Microsoft Access supports declarative referential integrity that is enforced at the engine level. In SQL Server, referential integrity is enforced by Transact-SQL code in triggers. A Microsoft Access relationship becomes four SQL Server triggers: two for the parent table and two for the child table.

Important: If only one of the tables in a relationship is upsized, or if referential integrity is not enforced in Microsoft Access, the relationship is not exported.

Parent Table

The Upsizing Wizard will create an UPDATE trigger that will either prevent changing the parent table's primary key or cascade that change through the child table, depending on the type of relationship that was created in Microsoft Access.

The wizard will also create a DELETE trigger that prevents deleting a record with related child records, or that deletes the child records, again depending on the type of the original relationship between the tables in Microsoft Access.

Examples

The following Transact-SQL code is contained in the Departments_Dtrig trigger of the Departments table. It prevents deleting a parent record that would orphan related records in the Employees table.

IF (SELECT COUNT(*) FROM deleted, Employees 
WHERE (deleted.Dept_ID = Employees.Dept_ID)) > 0
    BEGIN
        RAISERROR 44445 'There are dependent rows in Employees'
        ROLLBACK TRANSACTION
    END

The second example illustrates how changes to the primary key (the Email field) are cascaded to the child table's foreign key. The code is contained in the Employees_Utrig trigger of the Employees table.

IF UPDATE(Email)
    BEGIN
       UPDATE Tasks
       SET Tasks.Emp_ID = inserted.Email
       FROM Tasks, deleted, inserted
       WHERE deleted.Email = Tasks.Emp_ID
    END

Child Table

For the child table, the Upsizing Wizard creates an UPDATE trigger that prevents changes to the foreign key that would orphan the record. Likewise, an INSERT trigger is created to prevent a new record from being added that has no parent.

Example

This code prevents adding a record to the Tasks table if no parent record exists in the Employees table, and is contained in the Tasks_Itrig trigger.

IF (SELECT COUNT(*) FROM inserted) !=
   (SELECT COUNT(*) FROM Employees, inserted 
    WHERE (Employees.Email = inserted.Emp_ID))
    BEGIN
        RAISERROR 44447 'No row in Employees with matching key'
        ROLLBACK TRANSACTION
    END

Similar code is found in the Tasks_Utrig trigger to prevent orphaning records through changing the foreign key.

Custom Error Values

When the referential integrity established by the wizard-created triggers is violated, the Upsizing Wizard places a custom error value into the @@ERROR variable. The value depends on the action the user was attempting: updating, inserting, or deleting.

The following table lists the error numbers generated for each action: