What is a split database application?
A split database consists of two database files. One database
(called the back-end database) contains only the tables and relationships and
resides on a network file server. The other database (referred to as the
front-end database) contains all the other database objects: queries, forms,
reports, macros and modules and resides on each user's computer.
A
split database design works well in multiuser environments, and it offers these
advantages:
- Performance is improved because the application portion of
the database, the user interface, is on the user's local hard disk.
- Network traffic is reduced because only the data travels
over the network; all other objects in the database are local.
- Updating your application is easier. You can make design
changes to the front-end database, and then distribute the changes to users
without changing any of the data in the back-end database.
- In non-runtime environments, users can create their own
custom objects, such as queries, forms, or reports, without affecting other
users.
After you develop your application, you can manually create the
back-end database, export the tables into it, and then link them back to your
front- end database, or you can use the Database Splitter Wizard in Microsoft
Access to automatically do the work for you.
Creating the setup files
When you distribute a split database application, create two
separate collections of setup files: one setup for the front-end database and
all of its support files, and one setup for the back-end database that contains
only that file and, optionally, a Readme file.
The setup for your
back-end database does not need to include any shortcuts, registry values, or
additional components. However, the Setup Wizard will automatically include
Microsoft Access setup files, and
those files will be installed on the file server along with your back-end
database.
When you distribute your application, instruct a network
or system administrator to run the Setup program for your back-end database on
the network file server first. Also provide instructions for setting the
correct permissions for the folder where the data resides, such as Read, Write,
Create, and Delete. You can include those and other instructions in a Readme
file, and include the Readme file as part of the setup for your back-end
database.
After Setup install the appropriate files on the file
server, instruct all users to run the setup program for your front-end
database. This setup program sets up your main application and all of the
Microsoft Access run-time files on each user's computer.
Refreshing linked tables
After you set up the back-end database on a network file server,
you may need to provide a mechanism in your front-end database to refresh the
linked table connections. In fact, it is a good idea to run a Visual Basic
procedure each time the front-end database opens to verify that the table links
are valid, and to refresh them if they are not. This can prevent potential
problems if a user moves the back-end database to another location, or if the
user's local computer is incorrectly mapped to the network server.
The Developer Solutions sample application (Solutions.mdb) included with
Microsoft Access contains a module called modRefreshTableLinks (or
RefreshTableLinks in version 7.0) that you can use as a basis for writing your
own Visual Basic procedure to refresh your table links. You can execute the
procedure in the OnLoad event of your Startup form, or in an AutoExec macro in
your application, so that table links are verified or refreshed each time your
application is started.
For more information about how to customize modRefreshTableLinks to use in your own application, click the following article number to view the article in the Microsoft Knowledge Base:
154397
How to modify RefreshTableLinks module in Solutions.mdb
Note If you can emulate the network topology that your
application's users will have, you do not have to refresh the table links in
your application (front-end) database. For example, if you know your data
(back-end) database will reside on G:\MyAppFolder, where G: is a mapped network
drive, you can map a drive the same way in your development environment, and
place the back-end database in G:\MyAppFolder. Then, when you distribute your
application, each table's Connect property will already contain the correct
path to the back-end database. However, because you cannot control whether
users move the back-end database to another location or how each user will map
their network drives, you should include a procedure to refresh table links in
all your split database applications.