Resource Logic Client/Server Development Services

Converting Microsoft Access 97
Microsoft Access 2000

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 considered migrating to the .MDB program but was inappropriate in this particular case.

Access 97 moves to Access 2000 fairly easily.  Visual Basic for Applications (VBA) was made 'more compatible' with Visual Basic 6, particularly with respect to support for ActiveX controls, and thus, in particular, ADO (ActiveX Data Objects).

A 'Rule of Thumb': If you are sharing an .MDB among multiple users on a LAN, you are probably better off migrating the 'backend' to SQL-Server.  This is almost absolutely true if there are more than five users total, and more than three people sharing at any one time.  In the sites that I've worked on, users have complained about performance when more than two people are sharing an .MDB.

One area that has perhaps subtle revisions in code, which provoked failures in one of our 97->2000 conversions, was in certain complex conditional expressions. Anyone encountering problems in VBA should look carefully at their 'if then' and 'do loop' constructions. After we had fixed about five lines of code (which we did on the 97 side before our next attempt at migrating the code) our conversion succeeded without a hitch.

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. Particular areas of interest are blocks of code that don't compile on the first round, and queries that seem to be perplexing when migrated into SQL Stored Procedures. Please write me at Please put a description of your question or comment on the subject line. Thanks.