How to share a database in a dual-version environment

This article explains how users with different versions of Microsoft Access can share the same data.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

When you open a database that has been created in an earlier version of Access, you have the opportunity to convert the database to the file format used by the later version, or to open it without having the ability to save design changes.

If two or more users have different versions of Access, you may want to allow them to use the features of their own respective versions when working with common data. In that case, you need to separate the database's tables from its other objects in a back-end database, and then provide the various users with front-end databases suitable to their respective versions of Access. You can use the Database Splitter add-in in Microsoft Access 97 and later versions to simplify the process of separating the front-end and back-end databases.

The following example shows you how to do that with the Northwind.mdb sample database provided with Access 97, assuming that the data needs to be available to users of both Access 97 and Microsoft Access 2000. To create separate front-end and back-end databases, follow these steps:

1. Start Access 97, and then open the sample database Northwind.mdb. If the welcome screen appears, click OK.
2. On the Tools menu, point to Add-Ins, click Database Splitter, and then click Split Database in the Database Splitter dialog box.
3. In the Create Back-end Database dialog box, click Split to accept the default file name.
4. When you see the following message
   Database has been successfully split.
   click OK.

At this point, Northwind.mdb has become the front-end database, and its tables have become links to the back-end database, Northwind_be.mdb.

To ensure that users know which database version they are working with, rename Northwind.mdb as "Northwind97.mdb."

NOTE:

• If the database requires security, permissions should be assigned for the new Northwind_be.mdb database at this point.
• Place Northwind_be.mdb in a shared location, such as a shared folder on a Microsoft Windows NT Server computer.
• Ensure that all users have full permissions to the shared folder, but not Delete permissions for the file Northwind_be.mdb.

To create the front-end database for Access 2000, follow these steps:

1. Start Access 2000 and open the front-end database Northwind97.mdb.
2. In the Convert/Open Database dialog box, select Convert Database, and then click OK.
3. In the File name box, type Northwind2K, and then click Save.

NOTE: Depending on how the Access 97 versions of other databases were developed, you may receive a message that the converted database cannot be saved in a compiled state. If so, you may need to revise the properties of some database objects to bring them into conformity with the more recent version.

When you need to make changes to the design of the database, make those changes on a computer that has only the oldest version of Access in the environment installed to avoid DLL, MDA, and/or OCX version conflict problems. When you make changes, ensure that object names are not reserved words in either the older or newer versions of Access, and save a copy of the finalized program in a separate location to provide for disaster recovery or future design changes.

When you have completed any design changes, copy the program to a computer with the newer version of Access, and then convert it to the newer version as described in the preceding steps.

You can distribute your program to server share users by placing appropriately named versions (for example, Northwind97.mdb and Northwind2K.mdb) in shared folders and giving users full permissions for the folder, but not Delete permission for the program.

To distribute a run-time version of the program, you must use the Access or Office Developer tools to build setup files for each version. If you are distributing a run-time version to an environment in which Access is already loaded on some or all users' local drives, you must perform the setup build by using the same versions of the DLL, MDA, MDW, and OCX files that are already on the users' computers, or you must manually add those files to a folder other than C:\Windows\System or C:\Winnt\System32 to avoid version-conflict issues.

For additional information about running multiple versions of Access simultaneously, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about converting Access databases, click the article numbers below to view the articles in the Microsoft Knowledge Base: For more information about distributing a split database, click the following article number to view the article in the Microsoft Knowledge Base: