One of our current projects involves moving the contents of a shared Access/97 database to Access/2000 on the front end and SQL-Server 2000 on the back end. We have made some discoveries that may be useful to others.

Access .MDB databases consist of Tables, Queries, Forms, Reports, Macros, and Modules, along with some other constructions (for example: menu shortcuts, which are a type of macro). Everything except queries are easily migrated from Access/97 to Access .ADP projects. .ADP projects use a separate 'back end' such as SQL-Server or another client/server database, so tables end up in a separate repository.

The tables are imported into SQL-Server (using the Import functionality of Enterprise Manager). Forms, Reports, Macros, and Modules import directly into the newly created .ADP.

The Access Query can be implemented in SQL-Server either as a view (no run-time parameterization) or a stored procedure (run-time parameters in some way limit the scope of the returned records). Microsoft claims that their upsizing utility will convert queries to either a view or a stored procedure. Problem is, if an Access Query has a grouping clause, the keyword inserted by Access is 'DISTINCTROW'. There is no corresponding term in T-SQL. Therefore, this group of queries will not convert.

Most reports use queries, and lots of these involve some sort of grouping. One should assume that queries will not convert for the most part, and that it will be necessary to analyze the queries and redefine them as stored procedures. This substantially affects any proposed time estimate or delivery schedule.

Naming and Coding Conventions

There are a number of habits Access developers have that translate poorly into SQL-Server. First and foremost is the use of unusual characters, like question marks, single quotes, and pound signs. Some examples of 'bad news' column names: BACKORDERED?, Manager's, and Item#.

Versions of SQL-Server prior to 2000 didn't like these at all, but 2000 has lifted some of the constraints on column names. It is still a good idea to get rid of special characters, and if possible, embedded blanks. For the latter, simply replace blanks with underscores. "First Name" simply becomes "First_Name".

The presents of blanks and special characters in names bloats the program code, and sometimes user's 'plain English' queries. The same INSERT expression with and without embedded blanks:

Insert into Customers ([Customer Name], [Customer Address], [Customer City], [Customer State], [Customer Zip], [Customer Telephone]) values ('New customer', '123 Main Street', 'Podunk', 'ID', '80123', '212-555-1212');

Insert into Customers (Customer_Name, Customer_Address, Customer_City, Customer_State, Customer_Zip, Customer_Telephone) values ('New customer', '123 Main Street', 'Podunk', 'ID', '80123', '212-555-1212');

Which would you rather work with day in and day out?

A Recommended Migration Path

The procedure that we used in this migration was three part:

1. Import tables 'as is' from Access/97 to SQL-Server 2000. This SQL database had goofy column names, huge text sting fields (names Varchar'ed at 255 characters), and randomized primary keys (Access has a 'Random' option for Autonumber, no such option exists for Identity columns in SQL-Server 2000).

2. Import tables into an 'optimally structured' SQL database using stored procedures. In our situation, the business owner expected to replicate the database on his laptop, so this is the point where we converted primary keys to GUIDs. This required that we have the old primary keys and the new primary keys together in the same table.

3. Export the conversion database to the production database. When these tables were exported, they had the new GUID primary keys, but not the 'old' ones sent over from Access/97.

Just importing the Access/97 into SQL-Server revealed a number of data integrity problems, in particularly malformed dates. We had a number of '03/01/202' type entries in Date/Time fields, and even some completely invalid numerical representations in Single and Double typed fields. Some of these are only caught when moving Date fields to SmallDate fields, since there is a real year 202, it just doesn't fit in a SmallDate coulumn.

The owner had embedded an Excel spreadsheet in one of his Access/97 tables. We were able to migrate this table without incident.

Note: We discovered that trying to import the Access/97 .MDB as an Access file does not work with Enterprise manager (at least if you have Office/2000 installed on the machine). It is necessary to define an ODBC data source, and then import from that data source. If you have more than one Access/97 .MDB, you have to define a separate data source for each one of them.

Access Queries and SQL-Stored Procedures held up to the light side by side.

A lot of the traffic driven to this site appears to be related to Access 97 conversion issues. I am interested in hearing what kind of problems or experiences you are having, and I might be able to add material to this site to address those concerns. Please write me at info@resourcelogic.net. Please put a description of your question or comment on the subject line. Thanks.