Creating Large-Scale Applications with MS Access 97

Article
02/20/2014

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Presented at Tech-Ed 97

OFF 405

James Sturms

Session Goal

The goal of this session is to enable the developer of Microsoft Access-based applications to be able to use and understand fully the Microsoft Access Upsizing Tools. The developer should be able to use the tools to facilitate client-server development between Microsoft Access and Microsoft SQL Server. The session will discuss design strategies for using the tool optimally in the development process.

What You Will Learn

Have a clear understanding of the Microsoft Access Upsizing Tools and how it interacts with Microsoft SQL Server.
Understand how to prepare for the upsizing process, complete upsizing, and take the next steps required to make your application's complete client-server solutions.

Overview

This session will break down into three sections:

First we will discuss the benefits of upsizing and the steps one needs to take before the upsizing process begins.
We will then detail the upsizing process, discussing exactly what the Upsizing Tools can do and cannot do.
Finally, we will discuss the next steps required after upsizing in order to complete your client-server application.

The Upsizing Tools and the sample application used in this document will be available on your conference CD. The application is for your personal use: You may learn from it and reuse code segments. They cannot be redistributed, however, for production use.

The reader should be familiar with the design of Microsoft Access-based applications and conversant with Microsoft Access programming techniques. No prior experience with Structured Query Language (SQL) database data sources is needed for this session.

This session focuses on the use of the Microsoft Access Upsizing Tools. The tools, which were released in January of 1997, are a free add-on for Microsoft Access 97. The tools are designed to work with Microsoft SQL Server database management system versions 4.21, 6.0, and 6.5. You need both Microsoft Access 97 and Microsoft SQL Server to use the Upsizing Tools.

The Upsizing Tools consist of two major components:

Upsizing Wizard
SQL Server Browser

Below is a description of these two major components that we will be discussing throughout the session.

Upsizing Wizard

The first component of the Upsizing Tools is the Upsizing Wizard. It takes a Microsoft Access database and creates an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original Microsoft Access database.

This will allow developers who design client-server applications on their desktop in Microsoft Access to generate a SQL Server database from their prototype. Also, developers who have existing Microsoft Access-based applications will be able to "grow" those applications to SQL Server and gain the benefits of a client-server architecture.

SQL Server Browser

The SQL Server Browser allows developers to view, create, and edit SQL Server objects including tables, views, defaults, rules, stored procedures, and triggers. The Browser can be used to manage a SQL Server database created by the Upsizing Wizard or any other existing SQL Server objects.

Before Upsizing

The first question we need to discuss is, "Why do we want to upsize a file server database to client-server?" The main reasons to do this are the benefits that a client-server configuration can offer, including improved performance, security, and reliability. Database application solutions inherently grow and become more complex over time. Developers need the assurance that they have a path to scale as their applications become larger and more complex and must support more users. In addition to scaling an existing application, the process of upsizing is useful for jump-starting client-server development. By this I mean that Microsoft Access is a very powerful file server database management system that, in addition to allowing a developer to build solid solutions, also offers the ability to rapidly prototype client-server solutions for prospective clients. Microsoft Access' rapid application development tools allow for many iterations of a prototype without the inherent complexities of client-server. It also allows for testing the initial designs without deploying the final, more robust back end. Once the plan is final, the upsizing process allows the developer to use the initial prototyping work to quickly build the data structure and rules on the server to jump-start the server-based work.

Before we can upsize an application to SQL Server, there are several important design issues we need to take into consideration. Ideally, we would design an application with client-server deployment in mind from the very beginning. As we will see, the design to build an optimized client-server application is often very different than a file server-only application. If you have an existing application that was not built with client-server in mind you will likely need to retrofit your application in order to take advantage of the upsizing process to follow.

Design Tips-Client Side

Design forms so that a form does not require data to be retrieved from the server during the form-opening process. Add a command button to the form to fetch data to populate the form. In many cases, you may want to save the last set of form data in a local table, then load the form from the saved data the first time the user opens the form.
Use Recordset objects of the Snapshot type if the result set contains relatively few columns and doesn't contain OLE Object or large Memo fields, and you don't need to update the server tables. Set the value of the RecordSetType property of the form to Snapshot to create a Snapshot instead of a Dynaset. Pass-through queries always return Snapshot Recordset objects.
Minimize the number of items in server-populated pick list combo boxes. Use Snapshot Recordset objects to populate the combo boxes. It is important to remember that as database sizes increase, certain solutions may become unworkable. For instance, a combo box of 30 records is reasonable for a user to browse and pick from. When the list of possibilities is in the hundreds, this list becomes unworkable.
If pick list data changes infrequently, maintain a local copy of the server table that populates the combo boxes. Include only the field(s) you need for the pick list in the table. Create an index on the local table to speed pick list population. Provide the user a simple means of replacing the local tables.
If decision-support application users need to compare multiple sets of data, consider storing the data returned by the server in temporary local tables. Provide a form in which the user can elect to use the previously stored data, or execute a new query. Adding the ability to quickly compare results of successive queries is especially important in applications that process financial information.
Adhere to server-based naming restrictions from the beginning. The Upsizing Wizard will correct many common mistakes in this regard, but not 100 percent. It is best to adhere to SQL Server's restrictions from the beginning in order to assure a painless migration to the back end. SQL Server names must be 30 characters or less. The first character must be a letter or the symbol "@". The remaining characters may be numbers, letters, or the "$," "#," and "_" symbols. No spaces are allowed.
In code, do not use table-specific commands such as Table-Type RecordSets, DoCmd.OpenTable, or the SEEK method. These operations are only supported on the local Jet tables and will break if they are suddenly referring to server-based tables.

Create ODBC Data Source

You need to make sure you have the Open Database Connectivity (ODBC) data source you will need for upsizing. You should also decide which database you plan to upsize and make a backup copy of it.

The Upsizing Wizard requires that you log in to a SQL Server database. If you are creating a new SQL Server database, you should make sure you have an ODBC data source for the Master database for the SQL Server you want to upsize to.

If you are upsizing to an existing database, the Pubs sample database for instance, make sure you have an ODBC data source for Pubs.

You can create an ODBC data source by running the ODBC Administrator. When using Microsoft Windows 95 or Microsoft Windows NT 4.0, you create an OBCD data source using the 32bit ODBC icon in the Control Panel.

Important: If you click Options, and then select the Convert OEM to ANSI Characters check box when you create your data source, you must click Select and then specify a code page translator. If you select the Convert OEM to ANSI Characters check box and don't select a code page translator, the Upsizing Wizard will fail.

Design Tips-Server Side

To make upsizing go as smoothly as possible, you should make sure that you have sufficient permissions on the SQL Server you want to upsize to. Calculate how much disk space upsizing will require and make sure you have enough free disk space.
The permissions you need vary according to what you want to accomplish. At minimum, you must have CREATE TABLE permissions on the server you wish to upsize to. This will allow you to upsize to existing databases.
If you want to build a new database from scratch, you must have CREATE DATABASE permissions. Finally, if you want to create new devices, you must be a member of the admin group.

Estimate SQL Server Database and Device Size

If you are creating a new database, the Upsizing Wizard will ask you to select devices for your database and, optionally, a log. It will also ask you to set the size of the database itself. In order to answer these questions, you should estimate how much space your new database will require.

Database Size

When SQL Server creates a database, it sets aside a fixed amount of space for that database on one or more devices. Not all this space is necessarily used by the database. Database size is just the upper limit on how large a database can grow before it runs out of space.

By looking at the size of your Microsoft Access database and estimating the rate at which your new SQL Server database will grow, you can arrive at a rough estimate of the space needed for your database.

If you have ample disk space on your server, simply multiply the size of your Microsoft Access database by two. This will ensure that the Upsizing Wizard has enough space to upsize your database and leave it some room to grow as well. If you expect a lot of data to be added to the database, you should make the multiple larger.

The Upsizing Wizard works best when there is plenty of disk space available. In situations where disk space is scarce, it is possible to fit a Microsoft Access database onto a SQL Server database less than twice its size. In general, every megabyte of Microsoft Access data will require 1.3 to 1.5 megabytes on SQL Server. Remember that the size of a Microsoft Access MDB file includes all Microsoft Access objects, not just data.

Device Size

All SQL Server databases and logs are placed on devices. At one level, a device is merely a logical location to put databases and logs. At a lower level, a device is a physical file. When a device is created, SQL Server creates a file, thus reserving a set amount of disk space for its own use. A device can be thought of as a fixed portion of disk space that SQL Server sets aside for its own use.

If no devices have enough free space, you may create a new device using the Upsizing Wizard. New devices should be at least as big as your estimated database size. It is recommended that you make the device still larger, if possible. This will let you expand your new database later or place other databases or logs on the same device.

In SQL Server 4.21, the size of a device cannot be changed. Make sure you create devices that are sufficiently large. SQL Server 6.x allows you to increase the size of your devices, but not decrease them.

Once you've arrived at an estimate of how much space your new database will require, you will know how big a device you must have. The Upsizing Wizard will show how much free space is available on each available SQL Server device and allow you to choose which one you want.

Devices

In most cases, the Upsizing Wizard provides more than enough control over SQL Server devices. There are two cases where you may wish to create devices before running the Upsizing Wizard.

Servers With More Than One Physical Disk

If your server has more than one physical hard disk, you may want to place your database on one disk and the log for the database on a different disk. In the event of a disk failure, the likelihood of recovering will be much greater.

The Upsizing Wizard allows you to create new devices, but only on one physical disk—the same disk as the Master database device. To place a database and log on separate disks, make sure you have devices that are big enough on both disks, creating new devices if necessary, then run the Upsizing Wizard.

Placing New Databases or Logs on Multiple Devices

SQL Server allows databases and logs to span several devices. However, the Upsizing Wizard allows you to place databases and logs only on a single device or the "Default" device.

If you want to specify multiple devices for a database or log, make those devices default devices. Make sure only those devices are set as default. Then run the Upsizing Wizard and choose "Default" for the database or log device.

Note that if the size you specify in the Upsizing Wizard for the new SQL Server database, or the size of the log doesn't require using all the devices set as default, SQL Server will use only the devices necessary to accommodate the database or log.

The Upsizing Process

The Upsizing Wizard allows you to create a new SQL Server database. However, if you have previously upsized your Microsoft Access database, or you want to add Microsoft Access tables to an existing SQL Server database, you can upsize to an existing database.

Exporting Table Properties and Linking Server Tables

The Upsizing Wizard can export additional table properties and create timestamp columns in the SQL Server table. The wizard can also modify your Microsoft Access database so that your queries, forms, and reports use the data in your new SQL Server tables, rather than the data in the local Microsoft Access database.

Timestamp Columns

By default, the Upsizing Wizard creates new columns with the data type of timestamp in SQL Server tables generated from Microsoft Access tables which contain floating-point (single or double), memo, or OLE fields.

A timestamp field contains a unique value, generated by SQL Server, which is updated whenever the record is updated. Microsoft Access uses the value in timestamp fields to see if a record has been changed before updating it.

A timestamp field provides the best performance and reliability. In the absence of a timestamp field, the Microsoft Access Jet engine must check all the fields in the record to determine if the record has changed, this will slow performance.

Microsoft Access does not check to determine if text or image 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, the Microsoft Access Jet engine will overwrite the change. Also, the value of a floating-point field may appear to have changed when it hasn't, so Microsoft Access may determine that the record has been changed when it has not.

You can also choose to have the Upsizing Wizard create a timestamp field for all upsized tables, regardless of what field types they contain. In particular, this improves the performance of tables that don't contain Memo, OLE Object, or floating-point fields, but have many fields of other types. A timestamp field prevents Microsoft Jet from having to check all of the fields to determine if a record has been changed.

Linking Server Tables

The Upsizing Wizard can modify your Microsoft Access database so that your queries, forms, and reports use the data in the new SQL Server database rather than the data in your Microsoft Access database.

The wizard renames the Microsoft Access tables you export with the suffix "_local." For instance, if you export a table named "Employees," the table is renamed "Employees_local" in your database. Then, the Wizard creates a linked SQL Server table named "Employees."

Forms, reports, and queries based on the original Employees tables will now use the SQL Server "Employees" table.

Field Names and Aliasing Queries

SQL Server will not allow spaces or symbols in field names other than #, $, and _. The Upsizing Wizard automatically replaces spaces and illegal characters with the "_" symbol.

If field names were changed when a table was exported, the wizard names the linked table with the suffix "_remote." The Upsizing Wizard then creates an aliasing query, so that forms, reports, and queries will work properly on the new server table.

For example, if you export a table "Employees," the wizard creates a linked table called "Employees_remote" and renames the "Employees" table to "Employees_local." The wizard then creates an aliasing query called "Employees," to accommodate the fact that the field names on the SQL Server are different.

Figure: There is no performance penalty for using aliasing queries instead of linked tables.

Finishing

When you reach the final screen in the Upsizing Wizard, it offers to create an Upsizing Report. The upsizing report documents what objects the Upsizing Wizard created on SQL Server. It includes information about any devices and databases that were created as well as a complete explanation of how each Microsoft Access object that was upsized maps to a SQL Server object. After upsizing is complete, you can view this report on screen or print it for future reference. The upsizing report cannot be saved to disk. In order to save this information, you must print the report or output the report to Microsoft Word.

How the Upsizing Wizard Works

The Upsizing Wizard makes upsizing a Microsoft Access database to SQL Server practically transparent.

This section refers to specific SQL Server objects that are created when the database, Employee.MDB, is upsized. This sample database is included with the Upsizing Tools. You may find the examples easier to understand if you upsize this database and then use the SQL Server Browser to access the objects discussed in the examples.

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. In SQL Server, validation rules and referential integrity can also be implemented with code bound to a table (triggers).

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:

Microsoft Access	SQL Server 4.21	SQL Server 6.x
Database	Database	Database
Table	Table	Table
Indexes	Indexes	Indexes
Primary Keys	Non-clustered Unique Index	Non-clustered Unique Index, Primary Key
Field	Field	Field
Default	Default	Default
Table validation rule	Update and Insert triggers	Update and Insert triggers
Field validation rule	Update and Insert triggers	Update and Insert triggers
Field Required property	Update and Insert triggers	Update and Insert triggers
Relations	Update, Insert, and Delete triggers	Update, Insert, and Delete triggers or DRI

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

Links to New Server Tables

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

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

Description
Caption
Format
InputMask
DecimalPlaces

Note: Linked tables were referred to as Attached tables in Microsoft Access 2.0.

Indexes

SQL Server and Microsoft Access indexes are very similar. Microsoft Access primary keys are converted to SQL Server non-clustered, unique indexes. The primary key index is always named with a prefix of "aaaaa." In SQL Server 6.x, this index is also marked as a SQL Server Primary Key. When linking to a remote table, Microsoft Access chooses the index that is first alphabetically in the list of available indexes as the primary key. The "aaaaa" prefix 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.

Example

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

Microsoft Access Index Name	Index type	SQL Server Index Name	Index type
PrimaryKey	Unique primary key	aaaaaEmployees_PK	Unique
Dept ID	Non-unique, ascending	Dept_ID	Non-unique
Reports To	Non-unique, ascending	Reports_To	Non-unique

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:

Microsoft Access Type	SQL Server Type
Yes/No	bit
Number (Byte)	smallint
Number (Integer)	smallint
Number (Long Integer)	int
Number (Single)	real
Number (Double)	float
Currency	money
Date/Time	datetime
AutoNumber	int
Text(n)	varchar(n)
Memo	text
OLE Object	image

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.

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 expression to SQL Server Transact-SQL. If the default was successfully created, the wizard binds it to the appropriate SQL Server field.

Triggers

A trigger is a series of Transact-SQL statements associated with a particular SQL Server table. The Upsizing Wizard uses triggers differently depending on whether you are upsizing to SQL Server 4.21 or 6.x and whether you choose to use Declarative Referential Integrity on SQL Server 6.x.

SQL Server 4.21

AutoNumber fields, validation rules, and table relations map to SQL Server triggers. The Upsizing Wizard creates triggers when you export validation rules or table relationships, or when your table contains an AutoNumber field.

SQL Server 6.x

If you choose to use triggers to implement referential integrity, table relations will map to SQL Server triggers. Validation rules always map to SQL Server triggers.

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.

Trigger	Microsoft Access Functionality Emulated
UPDATE	Validation rules: Record validation, Field validation, and Required property Referential integrity
INSERT	Validation rules: Record validation, Field validation, and Required property Referential integrity (Child table triggers only) AutoNumber data type (SQL Server 4.21 only)
DELETE (Parent table triggers only)	Referential integrity

Declarative Referential Integrity

Declarative Referential Integrity (DRI) is new functionality introduced in SQL Server 6.0. DRI allows you to declare relationships between tables with the table definition. For example the Dept ID field in the Employees table is a foreign key to the Dept ID field in the Departments table. SQL Server DRI allows you to create these foreign keys as part of the table definition. DRI on SQL Server does not support the cascading updates and deletes that are allowed in Microsoft Access.

When upsizing to SQL Server 6.x, the Upsizing Wizard gives you the option of using DRI or triggers to enforce referential integrity. If your Microsoft Access database contains cascading updates and deletes, the Upsizing Tools will default to using triggers to maintain the cascades. You have the option of using DRI instead, but your cascades will not be upsized.

AutoNumber Fields

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

In SQL Server 6.x, AutoNumber fields are upsized to SQL Server Identity columns. Identity columns are functionally equivalent to AutoNumbers.

Note: On all supported versions of SQL Server, the functionality of random AutoNumber fields that use the Long Integer field size are reproduced by using triggers. The functionality of random AutoNumber fields that use the ReplicationID (GUID) field size cannot be reproduced.

Example

The Transact-SQL code below was generated as part of upsizing a Tasks table to SQL Server 4.21, and is contained in the trigger called Tasks_ITrigger. This code provides the same functionality as the AutoNumber 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 
/*
 * VALIDATION RULE FOR FIELD 'Status'
 */
IF (SELECT Count(*) FROM inserted WHERE NOT (Status In ('Not
Started','Started','Done'))) > 0
    BEGIN
        RAISERROR(778218, 16, 1)
        ROLLBACK TRANSACTION
    END
ELSE 
/*
 * VALIDATION RULE FOR FIELD 'Date Completed'
 */
IF (SELECT Count(*) FROM inserted WHERE NOT (Date_Completed Is Null Or
Date_Completed>'1/1/94')) > 0
    BEGIN
        RAISERROR(778219, 16, 1)
        ROLLBACK TRANSACTION
    END
ELSE 
/*
 * VALIDATION RULE FOR TABLE
 */
IF (SELECT Count(*) FROM inserted WHERE NOT (Status='Done'
Or Date_Completed Is Null)) > 0
    BEGIN
        RAISERROR(778220, 16, 1)
        ROLLBACK TRANSACTION
    END
...

Note: The numbers in the RAISERROR statements (778218-778220) map to unique error messages in the sysmessages table of the master database.

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.

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 'Field ''Emp_ID'' cannot contain a null value.'
        ROLLBACK TRANSACTION
    END
ELSE

Table Relationships

Table relationships are handled differently on SQL Server 4.21 and SQL Server 6.x.

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.

SQL Server 4.21

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

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(778221, 16, 1)
        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(778296, 16, 1)
        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 validation rule that was violated. A custom error message for each value is stored in the sysmessages table in the master database. To add user-defined error messages, use sp_addmessage system stored procedure. To delete user-defined error messages, use sp_dropmessage system stored procedure

SQL Server 6.x

You have the choice of using triggers or DRI. If you use DRI, you get engine level enforcement of you referential integrity via Foreign Keys, but lose the cascading updates and deletes supplied by the triggers.

Next Steps

At this point in the process you have the basics completed for your application but you still have work to do to create a full-fledged client-server application. You need take a number of additional steps, in both your SQL Server and Microsoft Access databases, to ensure that your application and data are secure and functioning properly.

Server Steps

On your SQL Server-based server, you should:

Make sure that tables you want to edit from Microsoft Access can be updated.
Set permissions on the database so that users are able to access the objects they need.
Optionally, prevent unauthorized users from gaining access to server data by preventing the storing of passwords with linked tables.
Protect your work by making your new database recoverable in case it is damaged or lost.

Adding Unique Indexes for Updatability

A linked table must have a unique index to be updatable in Microsoft Access. The Upsizing Wizard can export an existing unique index, but will not create one where none exists. Make sure that tables you want to edit from Microsoft Access are updatable.

You can use the SQL Server Browser to add unique indexes to tables.

Setting Permissions

The Upsizing Wizard does not export users, groups, or permissions that you have set in your Microsoft Access database. The new SQL Server database and its objects receive a set of default permissions from the SQL Server. Set permissions on the database so that your users are able to access the objects they need.

Database Logon Permissions

The default permissions of a new database make it accessible only to system administrators and the database owner.

You can add new users and groups using the SQL Server Security Manager or the system procedures sp_adduser and sp_addgroup. (For more information on adding users and groups, see the SQL Server Security Manager Help file and the documentation of the system procedures sp_adduser and sp_addgroup in the SQL Server Transact-SQL Reference.)

Object Permissions

All objects created by the Upsizing Wizard, including tables, triggers, and defaults, are accessible initially only to the database owner and system administrators. This is true whether you upsized to a new or existing database. If you overwrite existing objects, you also overwrite all object permissions.

To grant permissions on tables, use the SQL Object Manager or the GRANT and REVOKE commands. (For more information on setting object permissions, see the section "Managing Object Permissions" in Part 3 of the SQL Object Manager User's Guide, or the GRANT and REVOKE commands in the SQL Server Transact-SQL Reference.)

Synchronizing Local and Remote Permissions

Although Microsoft Access isn't aware of SQL Server security, it can't violate it. For example, if you're editing a remote table for which you don't have INSERT permission, Microsoft Access lets you type a new record; however, when you try to save it, the server returns an error message, and prevents you from inserting the record.

You can minimize these discrepancies by synchronizing local and remote user permissions and passwords. Then you need log on only once, as Microsoft Access automatically attempts to log in to the server using your local user permission and password and prompts you only if this login fails.

Saving Passwords Locally

When a remote table is linked in Microsoft Access, the user can save his or her server password locally with the table, and will no longer need to log on when opening the table. This also makes it possible for unauthorized users to gain access to server data. You can prevent this by creating a table named MsysConf on SQL Server.

When a user logs on to a SQL Server database, Microsoft Access looks for the MSysConf table. If it exists, its values control whether users can save their passwords with linked tables, and also control the rate of background population of records. If no MSysConf table exists, default values are used, and users are allowed to store passwords locally.

To prevent users from storing passwords locally

Create the MSysConf table. For information on creating the MSysConf table, search Upsizing Tools Help for "MSysConf table: creating."
Add a new record.
Set the Config column value to 101.
Set the value to zero.

These settings disable the "Save login ID and password locally" check box when you link to a server table. To enable the check box, set the value to 1.

Changes made to the MSysConf table will not take effect until the connection is re-established.

The options set in an MSysConf table apply to all Microsoft Access applications linked to the table's database.

Ensuring Recoverability

Protect your work by making your new database recoverable in case it is damaged or lost.

Dumping the Master Database

When a database is created on a SQL Server-based server, new records are added to the system tables in the Master database. Dumping the Master database provides you with a backup copy including all the latest changes.

Scheduling Backups

Schedule regular backups of your database so that you can restore your database from this backup copy in the event of a serious problem.

Device Mirroring

Mirroring a device continuously duplicates the information from one SQL Server device to another. In the event that one device fails, the other contains an up-to-date copy of all transactions.

If you anticipate that many changes will be made to a database between backups and you can't afford to lose those changes, consider device mirroring. Device mirroring is most effective when the devices are located on separate disks, as both devices may be lost if they are on the same disk and the disk fails.

Client Steps

Once you have transferred objects from Microsoft Access to SQL Server, you may need to modify code in the original Microsoft Access database so that it functions properly with the new SQL Server database.

Unsupported Objects and Methods

You may need to change code that uses objects and methods that are not supported in remote tables. In many cases, such as the CompactDatabase method or the Container object, there is simply no equivalent on a SQL Server.

The following data access objects are not supported:

Container
Document
Index
QueryDef
Relation

The following methods are not supported:

CompactDatabase
CreateDatabase
CreateField
CreateQueryDef
DeleteQueryDef
ListParameters
ListTables
OpenQueryDef
RepairDatabase
Seek
SetDefaultWorkspace

Nested Transactions

Microsoft Access supports transactions nested up to five levels. SQL Server supports only one transaction at a time. If your Microsoft Access code includes nested transactions, only the outermost transaction is sent to the server; the other transactions are ignored, and no error is produced.

Example

BeginTrans
'Outermost transaction sent to the server
'edits, updates
 BeginTrans
 'Nested transaction is not sent to the server
 'edits, updates
 CommittTrans
CommittTrans

You may need to modify your code in light of this difference. If your nested transactions are rolled back when any of them fail, the single-transaction limitation won't pose major difficulties. If some nested transactions are committed even if others fail, duplicating this functionality with one transaction may prove to be difficult.

Default and AutoNumber Values

Microsoft Access default and AutoNumber field values appear when you begin editing a new record. Default values generated by SQL Server defaults and AutoNumber values generated by table triggers appear only after a record has been inserted. You will need to change any code, such as code for lookups, that depends on having the values before the record is committed.

Validation Rules

In Microsoft Access, field validation occurs when the user tabs out of a field. When you edit SQL Server data in linked tables, triggers and rules are not fired until you leave the record. Record validation rules that rely on field validation occurring when a field is exited may need to be modified.

Unconverted Expressions

The upsizing report shows whether each Microsoft Access table validation rule, field validation rule, and default was successfully converted. If the Upsizing Wizard was not successful in translating a Microsoft Access expression, you will need to rewrite it using Transact-SQL.

For example, if a field validation rule could not be converted, you should rewrite the validation rule in Transact-SQL and then add it to the update and insert trigger for the table. You could also create a rule.

Another option is to perform validation at the form level in Microsoft Access. However, if server data is then modified without using a particular form, the validation will not be applied and invalid data may be entered.

Record Locking

You cannot open a Dynaset object against a linked server table in exclusive mode. Consequently, the value of the Record Locks property of all forms must be set to No Locks or Edited Record. (Edited Record is treated the same as No Locks.) The value All Records is illegal and generates an error.

With server tables, Microsoft Access uses optimistic locking internally. The row is locked only while the update process occurs, when the edited value is committed, which is usually a very brief interval.

Back-End Databases

Many developers take a "back end, front end" approach to developing Microsoft Access applications. They keep tables in one database (the back end) and all other objects such as forms and reports in another database (the front end).

To upsize a back-end/front-end application

Upsize the back-end database using the Upsizing Wizard.
Delete the renamed local tables (table names ending with "_local") from the back-end database.
Open the front-end database in Microsoft Access, and then delete the links to the tables in the original back-end database.
On the File menu, point to Get External Data, and then click Link Tables.
In the Files Of Type box, click ODBC Databases, and then select the Data Source that specifies the SQL Server database to which you upsized the back-end tables, and log on.
In the Link Tables dialog box, select all of the upsized back-end tables.
If in the future you don't want to require users to log on to open the tables, select the Save Password check box, and then click OK.
On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, select the original Microsoft Access back-end database.
In the Import Objects dialog box, click the Queries tab, and select any aliasing queries created when upsizing (they will have the same names as the original names of upsized tables), and then click OK.

Setting User-Defined Constants

The Upsizing Wizard has several default settings that you can change by modifying the constants in the UT_modUserConstants module in the Upsizing Wizard add-in database (Wzcs97.mda) before you run the Upsizing Wizard. In most cases, you should use default behavior, but there may be situations where you want change one or more of these settings.

Important: Microsoft Technical Support does not support use of the Upsizing Wizard with settings other than the default values.

To modify the values in the UT_modUserConstants module

Close Microsoft Access.
If you are using Windows 95 or Windows NT Workstation version 4.0, in My Computer or Windows Explorer, open the folder where Microsoft Access is installed, and then double-click Wzcs97.mda.

If you are using Windows NT Workstation version 3.51, in File Manager, open the directory where Microsoft Access is installed, and then double-click Wzcs97.mda.
In the Database window, click the Modules tab, and then double-click UT_modUserConstants.
Modify any of the user-defined constants.
Close the database. The new values will be used the next time you upsize a database.

The following table describes each of the user-defined constants.

Constant	Description
UT_CAREFUL	If this constant is set to False, the Upsizing Wizard uses Microsoft Access field or table names containing SQL Server reserved words as the upsized table and field names. If set to True, the wizard converts those names to allow for compliance. The default value is False.
UT_USE_CHAR	If this constant is set to False, when upsizing Microsoft Access Text fields, the wizard creates fields with the VARCHAR data type. If set to True, the wizard creates CHAR fields. The default value is False.
UT_CLUSTERED	If this constant is set to False, and you're using SQL Server version 6.0 or later, the wizard doesn't create a clustered primary key for your upsized table. If you're using SQL Server version 4.21, the index on the primary key is not created as a clustered index. If set to True, the wizard creates tables with a clustered primary key (version 6.x) or a clustered index on the primary key (version 4.21). The default value is False. Before you change this constant, see Chapter 5, "Clustered Indexes" in the SQL Server Database Developer's Companion for information on the implications of using clustered indexes on a monotonically increasing column such as an Identity column.
UT_QUIET	If this constant is set to False, the wizard halts on all errors after you click the Finish button. If set to True, the wizard prevents the display of most error messages. Most errors will be logged in the final report, but some may not be. If you need to upsize a database that will take a long time and don't want the wizard to halt for errors, set this constant to True. The default value is False.
UT_EXPORT_ GUID	If this constant is set to True, the wizard exports tables with AutoNumber fields with that have their FieldSize property set to ReplicationID (GUID). The field's data is exported, but their AutoNumber behavior is lost, because there is no equivalent behavior in SQL Server tables. If set to False, the wizard doesn't export such tables. The default value is True.
UT_USE_NULL_ CONSTRAINTS	When creating tables on SQL Server version 6.x, if this constant is set to True, the wizard sets NULL or NOT NULL on each column to duplicate the functionality of the Primary, Unique, and Required properties in Microsoft Access tables. If set to False, the wizard creates triggers to control when null values can be entered. The benefit of using triggers is that you can change the nullability of columns without dropping the entire table.When creating tables on SQL Server version 4.21a, this setting is ignored; the wizard always creates triggers. The default value is False.
UT_CHECK_ FOR_FULL_LOG	If this constant is set to True, the wizard checks to see if the database's transaction log is full whenever an ODBC Call Failed error occurs. If the log is full and the database is new, the wizard dumps (deletes) the log automatically. If the database is not new, the wizard prompts the user whether or not to dump the log. You may want to set this constant to False to speed up the process of upsizing. If you do this, make sure enough log space is free before upsizing a database. The default value is True.
UT_DELETE_ SERVER_XLAT	When upsizing a database, the wizard creates a character translation table on your server which takes some time to generate. If this constant is set to True, the wizard deletes the character translation table when it is finished upsizing a database. If set to False, the wizard doesn't delete the table, which will save time if you upsize several tables to the same server. The default value is True.

SQL Server Browser

As we discussed earlier in this document, the Upsizing Tools consists of an additional tool—the SQL Server Browser. It is essentially a client-server database container integrated into your Microsoft Access development environment. It allows you to view, edit, and modify SQL Server objects including: Tables; Views; Rules; Defaults; and Stored Procedures. This allows the developer to leverage their understanding of the Microsoft Access developer toolset when developing against the back-end database.

The SQL Server Browser can perform many of the same functions as the SQL Enterprise Manager, which is provided with Microsoft SQL Server 6.x. This section outlines the important differences between the SQL Server Browser and the SQL Enterprise Manager, to help you choose the tool that best suits the task.

User Interface

Microsoft Access users will find the SQL Server Browser interface familiar and easy to use. The SQL Enterprise Manager is powerful and easy to use, but doesn't resemble the Microsoft Access interface.

The SQL Server Browser allows you to view, edit, create and delete objects, in windows similar to the Microsoft Access Database window, and Table window in Design view.

However, in the SQL Server Browser, you must use ad hoc SQL for the following tasks:

Device and database management
Segment management
Backup and recovery
Security
User and group administration
Server configuration
Defining CHECK, FOREIGN KEY, PRIMARY KEY, or UNIQUE constraints, or the IDENTITY property

From within the Browser, you must run stored procedures to:

Drop (delete) databases or increase their size
Bind or unbind rules and defaults outside the table Design tool
View object dependencies

Modifying a SQL Server Table

You cannot delete any fields in the Table window because SQL Server doesn't allow you to drop columns from existing tables.

Caution: Each modification to an existing table is made immediately, rather than when you close the Table window.

To modify a table

In the SQL Server Browser window, click Table.
Select a table.
Click Design.

The SQL Server Browser downloads the table definition from the server and displays it in a Table window similar to the Microsoft Access Table window in Design view.
Make the changes you want to the field names, defaults, triggers, and indexes.

Note: Data type, field length, the Required property setting, and validation rules cannot be modified in existing tables.

Expression Translation

When adding defaults to SQL Server via the SQL Server Browser, you can use the most common Microsoft Access expression and the SQL Server Browser will convert this expression to a SQL Server expression. The following expressions are converted:

Microsoft Access functions	SQL Server functions
String functions
chr$(x)	char(x)
asc(x)	ascii(x)
str$(x)	str(x)
space$( x)	space(x)
lcase$(x)	lower(x)
ucase$( x)	upper(x)
len(x)	datalength(x)
ltrim$( x)	ltrim(x)
rtrim$(x)	rtrim(x)
right$(x,y)	right(x,y)
mid$(x,y,z)	substring(x,y,z)
Conversion functions
cint(x)	convert(smallint,x)
clng(x)	convert(int,x)
csng(x)	convert(real,x)
cdbl(x)	convert(float,x)
cstr(x)	convert(varchar,x)
ccur(x)	convert(money,x)
cvdate(x)	convert(datetime,x)
Date functions
now(x)	getdate(x)
date(x )	convert(datetime,convert(varchar, getdate(x)))
year(x)	datepart(yy,x)
month(x)	datepart(mm,x)
day(x)	datepart(dd,x)
weekday(x)	datepart(dw,x)
hour(x)	datepart(hh,x)
minute(x)	datepart(mi,x)
second(x)	datepart(ss,x)
datepart("<Access datepart>" , x)	datepart(<SQL Server datepart>, x)
dateadd("<Access datepart>" , x, y)	dateadd(<SQL Server datepart>, x, y)
datediff("<Access datepart>" , x, y)	datediff(<SQL Server datepart>, x, y)
Math functions
int(x)	floor(x)
sgn(x)	sign(x)

As part of translating expressions, the Upsizing Wizard and SQL Server Browser replace a number of delimiters, operators, constants and wildcard characters, as listed in the following table.

Description	Microsoft Access	SQL Server
Date delimiter	#	'
String delimiter	"	'
Mod operator	mod	%
Concatenation operator	&	+
Wildcard character	?	_
Wildcard character	*	%
Constant	Yes	1
Constant	On	1
Constant	True	1
Constant	No	0
Constant	Off	0
Constant	False	0

Creating Large-Scale Applications with MS Access 97

On This Page

Session Goal

What You Will Learn

Overview

Upsizing Wizard

SQL Server Browser

Before Upsizing

Design Tips-Client Side

Design Tips-Server Side

The Upsizing Process

Exporting Table Properties and Linking Server Tables

Timestamp Columns

Finishing

How the Upsizing Wizard Works

Overview of Object Mapping

Next Steps

Server Steps

Client Steps

Unsupported Objects and Methods

Setting User-Defined Constants

SQL Server Browser

User Interface

Additional resources