Sharing Databases with Users of Previous Versions of Microsoft Access

Mark Roberts
Microsoft Corporation

Updated 31 January 1997

Introduction

When users of your database applications created in previous version of Microsoft® Access upgrade to Microsoft Access 97, you have three options:

Convert your database application.
Open the database application in its existing format, which is called enabling your database application.
Split your database into a front-end/back-end application, that is, create a front-end database in Microsoft Access 97 linked to table data in a previous version back-end database.

If all users that will work with your application have upgraded to Microsoft Access 97, your choice is easy—you can convert the application. When you convert a database from a previous version of Microsoft Access to Microsoft Access 97, users of your application can use all of the new features available in Microsoft Access 97. However, keep in mind that this is a "one-way trip"—after a database is converted, the new database created by the conversion process cannot be opened in previous versions of Microsoft Access, nor can it be converted back to an earlier format.

For information on how to convert a database, see "converting databases" in Microsoft Access 97 Help. For additional information on conversion issues, see the Microsoft Access 97 Conversion Whitepaper on the Web at http://www.microsoft.com/ACCESSDEV/ACCWHITE/ACC97CNV.HTM, and Chapter 11, "Upgrading from Previous Versions of Microsoft Access" in the Microsoft Office 97 Resource Kit in the MSDN Library.

Note You can export tables from a Microsoft Access 97 database to an existing database created with a previous version. However, the functionality of certain field data types will be lost. For example, Hyperlink fields are supported only in Microsoft Access 97. Hyperlink field data will be exported to tables in all previous versions as Memo fields. Also, the functionality of Autonumber fields with the NewValues property set to Random, and Lookup Wizard fields are supported only in Microsoft Access 97 and 95. Only the data from these fields will be exported to previous versions.

However, if users of previous versions of Microsoft Access must use your database application, do not convert it. The following sections describe the two methods you can use to share a database application across multiple versions of Microsoft Access.

Sharing Databases with Microsoft Access 1.x, 2.0, or 95

If the workgroup that is using your database application is upgrading gradually to Microsoft Access 97, your application may need to be shared by users of Microsoft Access 1.x, 2.0, or 95. There are two strategies for sharing databases between different versions of Microsoft Access. You can enable the database, or you can create a front-end database in Microsoft Access 97 format linked to table data in a back-end database from a previous version of Microsoft Access.

The following table summarizes the advantages and disadvantages of each strategy.

Strategy	Advantages	Disadvantages
Enable previous-version databases.	All Microsoft Access users can open databases and add, edit, or delete data. Any additional development can be made in one file.	Database file size can increase substantially. Microsoft Access 97 users cannot modify or add new objects, or take advantage of many features unique to Microsoft Access 97. Requires some additional memory, particularly when running code.
Create a front-end database in Microsoft Access 97 format linked to table data in a back-end database from a previous version of Microsoft Access.	All Microsoft Access users can open databases and add, edit, or delete data. Microsoft Access 97 users can modify or add new objects (except tables) and can take advantage of features unique to Microsoft Access 97 (except for new table features).	Additional development of the front-end databases must be synchronized across versions. Changes made to the design of the back-end database may require changes in multiple front-end databases. New table features unique to Microsoft Access 97 are not supported in the previous version back-end database.

The following sections provide additional detail to help you decide the best strategy for your situation.

Note When using either of these strategies, you may encounter some of the same issues involved in converting a database. For example, the version of Visual Basic® that is used by Microsoft Access 97 contains some new reserved keywords. If the database you are trying to enable contains Access Basic or Visual Basic code that contains these keywords, Microsoft Access 97 displays the following message: "There were compilation errors during the conversion or enabling of this database." For more information on troubleshooting conversion problems, see the Microsoft Access 97 Conversion Whitepaper on the Web at http://www.microsoft.com/ACCESSDEV/ACCWHITE/ACC97CNV.HTM and Chapter 11, "Upgrading from Previous Versions of Microsoft Access" in the Microsoft Office 97 Resource Kit in the MSDN Library.

Enabling a Database

Enabling a database keeps the format intact so it can be shared by users of different versions of Microsoft Access. When a Microsoft Access 97 user enables a Microsoft Access 1.x, 2.0, or 95 database, other users can browse the database and add, delete, or modify records; but they cannot switch to Design view on any objects. To modify the design of existing objects or to add new objects, the database must be opened in the version of Microsoft Access used to create it.

Note Before you enable a database, make sure it is not open in a previous version of Microsoft Access. If the database is located on a server or shared folder, make sure no one else has it open.

To enable a Microsoft Access 1.x, 2.0, or 95 database in Microsoft Access 97:

Start Microsoft Access 97.
On the File menu, click Open Database.
In the Open dialog box, select a database.
The first time a database from a previous version of Microsoft Access is opened, Microsoft Access 97 displays the Convert/Open Database dialog box.
Click Open Database to open the database without converting it.

If the database contains forms, reports, and modules, Microsoft Access 97 creates separate copies of these objects and their Access Basic (Microsoft Access 1.x or 2.0) or Visual Basic (Microsoft Access 95) code so that they can run under Microsoft Access 97. This information is stored in a hidden table named MSysModules2. Depending on the size of the forms, reports, and modules in the database, the addition of the MSysModules2 table can as much as double the file size. If a Microsoft Access 1.x or 2.0 database has been enabled previously by Microsoft Access 95, it has a similar table named MSysModules, which further increases the size of the database.

Microsoft Access 97 does not display the Convert/Open Database dialog box the next time the database is opened unless a change is made to code in modules, forms, or reports in the previous version of Microsoft Access. If a change has been made to the code, you must enable the database again.

You can speed up the process of enabling a large database created with Microsoft Access 1.x or 2.0 by increasing the maximum buffer size beyond the default. To change this setting, modify the Windows® registry by setting the MaxBufferSize value to 4096, decimal base in the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 2.x. For more information about editing the Windows registry, see Microsoft Access 97 Help.

Creating a Front-end Database Linked to Table Data in a Previous Version Back-end Database

Enabling a database as described in the previous section places limitations on Microsoft Access 97 users, increases the size of the database, and requires additional memory, particularly when running code. As an alternative, you can split the database into a Microsoft Access 97 front-end database that contains all objects other than tables, and then link this database to a shared back-end database in the older version format. The back-end database contains the tables.

In general, it is good practice for developers to keep application code and objects in a separate database from the tables. This allows administrators to convert a copy of the front-end database to Microsoft Access 97 format while leaving the back-end database containing the tables in the previous version format until all users have upgraded to Microsoft Access 97. In this way, the converted copy of the front-end database gains all the features and functionality of Microsoft Access 97, yet the back-end database containing the tables is still available to all users.

Many developers who use previous versions of Microsoft Access organize shared databases as front-end/back-end databases. If this is the case for you, convert a copy of the front-end database and distribute it to all Microsoft Access 97 users, and then use the Linked Table Manager (Tools menu, Add-ins submenu) to relink the tables in the back-end database.

If the current database has not been split in the previous version, you can use Microsoft Access 97 to split the database and link to the older version tables. In Microsoft Access 97, first convert the database, and then split it using the Database Splitter Wizard. Finally, use the Linked Table Manager (Tools menu, Add-ins submenu) to relink the original tables in the previous version database.

Note Before you create and link a front-end database, make sure the linked database is not open in a previous version of Microsoft Access. If the database is located on a server or shared folder, make sure no one else has it open.

To create a Microsoft Access 97 front-end database:

In Microsoft Access 97, point to Database Utilities on the Tools menu, and then click Convert Database.
In the Database to Convert From dialog box, select the database, and then click Convert.
In the Convert Database Into dialog box, type a new name (without the .mdb extension) for the Microsoft Access 97 database.
or
Keep the same name and select a different location for the Microsoft Access 97 database.
Click Save.

Microsoft Access creates a converted copy of the database in Microsoft Access 97 format without altering the original database. Then you can link the copy of the database to the original table data.

To link a front-end database to tables in a back-end database from a previous version:

On the Tools menu, point to Add-ins, and then click Database Splitter.
Follow the instructions in the Database Splitter Wizard.
Delete the back-end database created by the Database Splitter Wizard, and then open the converted front-end database.
On the Tools menu, point to Add-ins, and then click Linked Table Manager.
Select the Always Prompt For New Location check box.
Select the check boxes for all the tables, and then click OK.
In the Select New Location Of table name dialog box, specify the location of the previous version database, click Open, and then click OK.

Alternatively, you can create a front-end/back-end application by importing database objects except tables into a new, blank database created in Microsoft Access 97, and then linking the back-end tables from the previous version database.

To create a Microsoft Access 97 front-end database by importing and linking:

In Microsoft Access 97, click New Database on the File menu, and then double-click Blank Database.
Specify a name and location for the database and click Create.
On the File menu, point to Get External Data, and then click Import. Specify the name and location of the previous version database.
In the Import Objects dialog box, for each tab except the Tables tab, click the tab and then click Select All. Click OK to convert and import application objects. This creates your front-end database.
On the File menu, point to Get External Data, and then click Link Tables. Specify the name and location of the previous version database.
In the Link Tables dialog box, click the Tables tab, and then click Select All. Click OK to link the tables in the back-end database.