Resource Logic Client/Server Development Services
Issues in Converting Microsoft Access 97
Microsoft Access 2000 ADP Projects
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
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
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
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
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
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 firstname.lastname@example.org. Please put
a description of your question or comment on the subject line. Thanks.