Separating Your Application's Tables from Its Other Objects

If you're distributing your application to a number of users, or if your data is located on a server, consider using a back-end database to hold the tables that contain your data and a front-end database to hold the application's other objects (queries, forms, reports, macros, and modules). You can then base all objects in your application on linked tables from the back-end database.

When you separate the application's data from its objects, you can easily distribute new versions of your front-end database that include upgraded queries, forms, reports, macros, and modules without disturbing the application's data. Similarly, if you need to exchange one set of data for another or back up your data, you can perform these operations on your back-end database without affecting the objects in your front-end database. And if your data is located on a server, you can reduce the network load and improve performance by having users run the front-end database on their workstations rather than from the server.

Users open and use the front-end database. Because the objects in the front-end database are based on linked tables, changes that users make to the data using these objects change the data in the back-end database. The following illustration demonstrates this configuration.

If you know from the beginning that you intend to split your application into two database files, you can develop the application with this in mind. Or you can keep tables and objects together in the same file and split them only when you're finished with development and are ready to distribute the application.

The easiest way to split an application after creating its objects is to use the Database Splitter Wizard.

To split an application after creating its objects

1 On the Tools menu, point to Add-ins, and then click Database Splitter.

2 Follow the instructions in the dialog boxes that appear.

The Database Splitter Wizard creates a new, empty back-end database. It moves all tables in the current database to the new back-end database, preserving table relationships and properties. The current database is now the front-end database. The wizard then links each table in the back-end database to the front-end database.

See Also For more information on using linked tables, search the Help index for "linked tables."

The links used by linked tables in the front-end database are based on the location of the back-end database. If users move the back-end database to a different location, the links will fail. If the current links are broken, you can automate the process of linking tables for your users by prompting them for the path to the back-end database when they start your application. Then you can use the RefreshLink method to refresh your application's links. To see an example of how this works, open the Developer Solutions sample application in the Samples subfolder of your Office folder. In the upper part of the Developer Solutions form, double-click Use Multiple Databases. In the lower part, double-click Link Tables At Startup.

Casa de Bender