In order to provide a simple, clear example of upsizing an application, we chose to use the existing sample application that ships with Access version 2.0: Northwind Traders. The forms and code will be altered to highlight some of the concepts outlined above.
Included in the sample database will be a report document that outlines the changes made to the application, and the location of the form, query, or code that has changed.
Although Microsoft SQL Server for Windows NT™ version 4.2 is used for this exercise, the methodology employed applies to the majority of other popular client-server RDBMSs.
A high level summary of steps involved in migrating from a file server to a client/server environment include:
At this point your application can behave similarly to when it was a file server application, however, it will probably not behave optimally. The remainder of this section is intended to explain the above steps in more detail and help you optimize your Microsoft Access applications for client/server computing.
To ensure your Microsoft Access applications scale up easily to a relational database, certain practices are recommended:
You use the ODBC Administrator application, which is now a component of Windows' Control Panel, to establish the ODBC data source. You must own the appropriate ODBC driver for your database server. Install ODBC drivers in a manner similar to that for other Windows® drivers. ODBC drivers available are registered in ODBCINST.INI. They appear in the Add Data Source dialog box that appears when you click the Add button of the Data Sources dialog box opened when launching the ODBC Administrator. Process details of adding ODBC data sources are documented in the Microsoft Access Advanced Topic manual, as well as in on-line help.
Indexes, business rules (domain integrity enforcement), default values, and referential integrity rules are not created when you export tables to a client/server database or use bulk-copy operations. You need to add the indexes, rules, and default values after creating new server tables. If permissions vary for users of the database, you'll need this information, too. You can print a very comprehensive list of the properties of the tables in your .MDB file with Microsoft Access version 2.0's Database Documentor.
Creating the structure of a complex, multi-table, client/server database can be a tedious and time-consuming process. Microsoft Access version 2.0's table export capability can speed this process greatly. To do so, export each table to the server database, using the Export facility of Microsoft Access version 2.0's File menu.
There are trade-offs to consider when exporting data. When exporting data using Microsoft Access, the Jet engine exports each row, one at a time. This ensures that your exported data adheres to all the server-based rules, but is significantly slower with very large tables than using server-based, bulk-copy routines.
If your tables contain a few thousand records or less, it is usually faster to export the tables from the Microsoft Access database to the server database, rather than using bulk-copy programs (BCP for SQL Server) with text files.
Most migrations to client/server operation are done in two phases; test and production. You can limit the number of records used for the test phase by writing the appropriate SELECT * INTO tblServer FROM tblMSA WHERE criteria ORDER BY keyfield(s) statement.
If you intend to use BCP or the equivalent to load the tables, you can save time and assure data type and field size consistency by exporting Microsoft Access tables containing at least one record to the server.
You can use the database document you generated earlier to create the default values, domain integrity, and referential integrity constraints in your server.
Note that Microsoft Access version 2.0 has declarative referential integrity implemented in the Jet engine using the SQL-92 reserved words CONSTRAINT, REFERENCES, PRIMARY KEY, and FOREIGN KEY to implement the enforcement process.
If you are migrating a Microsoft Access version 1.1 application that uses attached tables, it is likely that your application includes macros or Access Basic code to enforce referential integrity. One of the primary canons of relational database design is: Rules are enforced by the database, not by applications.
Client/server RDBMSs not supporting the preceding SQL reserved words for enforcing referential integrity require you to create triggers to enforce referential integrity. A trigger is a piece of code residing on the server, similar to a rule, that executes a SQL statement prior to the occurrence of a specified event, such as INSERT, DELETE, or UPDATE. The general syntax of SQL Server's CREATE TRIGGER statement is:
CREATE TRIGGER [owner, ]trigger_name
ON [owner, ]table_name
[FOR {INSERT|UPDATE|DELETE}] AS
sql_statement
Adding Default Values and Enforcing Domain and Referential Integrity
Most client/server RDBMSs provide a default value property. Otherwise, you need to use stored procedures, similar to the preceding example to set default values for fields whose value is not supplied during the append process. You can specify NOT NULL to emulate the Required property of fields in Microsoft Access tables. If the server database does not support ANSI SQL's reserved words, you'll need to create rules to enforce domain integrity, and write stored procedures to enforce referential integrity and perform cascading updates and deletions. None of the rules and stored procedures you create will be visible to Jet; if your applications violate the rules, your application will receive an error message from the server.
Add the indexes to the server tables with the necessary attributes (e.g., PrimaryKey, No Duplicates, No Nulls) identical to those employed by the Microsoft Access database. Jet cannot update a table that does not have a unique index.
The Jet engine in Microsoft Access version 2.0 has significantly enhanced Dynaset that now support server-based, primary key generation from triggers. In earlier versions, as the primary key was generated during an insert, the Dynaset cursor would think the record has been deleted since it is no longer was in the same location. Jet now re-fetches the record, keeping the Dynaset up to date.
This is particularly significant because many developers use the convenient Counter field to generate their primary keys. The equivalent of Microsoft Access's Counter field data type seldom is found in client/server RDBMSs, yet is critical in functionality to your application.
Now you can create an INSERT trigger that increments the integer value of the Counter field each time a new record is appended to the table. The Transact-SQL statement to create the equivalent of a Counter is:
CREATE TRIGGER add_customer_id ON dbo.customers
FOR INSERT AS
_ UPDATE dbo.customers
SET inserted.Customer_ID = (SELECT MAX(Customer_ID)
FROM dbo.customers) + 1
WHERE dbo.customers.customer_ID IS NULL
Note that when you use a trigger to create the Counter field equivalent, you will not see the Counter field's value in a bound control until you complete the appending process for the record.
The easiest and most obvious way to attach tables is to use the File/Attach command from the database container menu. When you attach a table, Jet creates a local image of the table structure and stores it in the system tables. This enables the user to build queries, forms, and reports as if the tables were local to their machine.
After attaching, relationships between tables need to be established using the new relationships editor found in the DB Container's Edit/Relationships menu. This establishes the default join conditions and types while building new queries.
Automating the Attachment Process
There are cases where it is desirable to create relationships from a module to automate the attachment process. This is useful if the database is not found; the user can be prompted to locate where the back end resides, and then automatically reattach it through code. This is also useful if server-table structures change and you need all the clients to reattach.
The general form of the code for Microsoft Access version 2.0's new CreateTableDef() method, that attaches tables from ODBC data sources is:
strConnect = "ODBC; DSN=strDataSourceName;UID=strUserID;
PWD=strPassword; DATABASE=strDbName"
Set tdfName = dbCurrent.CreateTableDef(strTableName,
intAttributes, strSourceTable, strConnect)
dbCurrent.TableDefs.Append tdfName
Renaming Attached Tables
As Jet attaches to a remote table, it prefixes the default table owner ID of SQL Server to each table name when you attach the table. (The period separator between the owner ID and the table name is replaced by an underscore, because periods in table names are not permitted by Microsoft Access.) Thus, the names of attached tables do not correspond to the original table names in your .MDB file. The simple fix however is to simply rename your tables after attaching back their original name.
Remote Tables in Design Mode
Any operation in datasheet view that is permissible for a native Microsoft Access table is applicable to tables attached by ODBC. In design mode, you can set the values of the Format, InputMask, and Caption properties in table design view. All other properties of attached tables are read-only.
As mentioned earlier, a separate report document will be included in the sample database to relate the specific optimization techniques mentioned previously, with particular code segments and design structures in the Northwind Traders' application.