Databases - Using Several MS Access Versions

It is possible to use several versions of MS Access on the same data. The data needs to be stored in a separate file (in the oldest format) and linked into the mdb files which contain the forms and reports.

Unfortunately, this significantly increases application maintenance. (Forms, et al. need to be updated in more than one place.)

Creating Version Specific Files | Re-Compiling All Modules | Code Changes

Creating Version Specific Files

In order to convert to a lower level (for example, 2000 to 97)

Make sure that the database you want to convert is closed.
Using the newest available version of MS Accecss, use Tools / Database Utilities / Convert Database... to create a convertable copy of the existing database. (Uh, this breaks all links and all new data formats.)
Make a copy of the convertable file for each version you will be using.
Open one of the copies in each version. They will be automatically converted.
Re-compile all the modules (program code).
Split out the data via Tools / Add-Ins / Database Splitter
Keep only one of the data files. This must be the one from the oldest version (least common denominator). Link this file into each of the "version unique" Form files.

This produces a data file without any forms or reports, and several version specific files which do contain the forms and reports.

Re-Compiling All Modules

After conversion, open the mdb file. The following will appear in a dialog box.

xxx has converted the code in 
'd:\path\xx.mdb'
to your current version of Visual Basic.

To improve the performance of this database, do the following:
1. Open any module in this database in Design view.
2. On the Debug menu, click Compile And Save All Modules.

(I had to write this down because, you only get this message the first time you open the file.)

Well, of course the instructions are not quite right. They work if your Program has a Module, otherwise

Open a form in design mode
Select View / Code (to display the Debug menu selection)
Select Debug / Compile And Save All Modules

I received

Compile Error: Can't find project or library

I was missing "Visual Basic for Application Extensibility 5.3" which appears to be a part of VBA 6. Unfortunately, the path name was so long that I could not read the filename. My Windows 98 system does have "Visual Basic for Application Extensibility" (I can't read its path either). Simply unchecking the missing library and checking the one I have appears to have fixed the problem.

Code Changes

In my test case, I converted from MS Access 2000 to 97. Before I fixed the library problem, I had to change

Date() to Now()

in several queries. Specifically, I changed

<date()-5 to <now()-5

Once the library problem was fixed, Date() seemed to work ok.

Split the Database

In MS Access 97, Tools / Add-Ins / Database Splitter moves all the tables in an mdb file to another file and then automatically links back the tables. Careful - the default directory is My Documents.

In order to re-link them to the Least Common Denominator data, use Tools / Add-Ins / Linked Table Manager.

Press Select All
Un-select any tables that are not linked to the new backend database
Check Always prompt for new location
Press OK

The following file sizes are interesting

Description	Filename	Size after conversion	Size after compacting
Original converted file		2,454 kb
Data (back end) file	Converted3_be.mdb	628 kb	660 kb
User Interface (front end) file	Converted3.mdb	3,840 kb	2,306 kb

Author: Robert Clemenzi - clemenzi@cpcug.org