Client Server Microsoft Access

As you might (or might not) know, you can use Access's built-in Database Splitter to split any existing database into two separate databases (two separate ACCDB files). The Database Splitter is a wizard that takes you through the steps necessary to split the database. In the process, the wizard creates a database that contains only the tables. To open the Database Splitter, click the Database Tools tab, and then click the Access Database button in the Move Data group.

For example, you might want to split a database file named MOM.accdb by using the Database Splitter. Follow the instructions in the Database Splitter and click the Split Database button. In the Create Back-end Database dialog box, choose a filename for your database. In our example, we named the file MOM_be.accdb. After the database is split, you end up with a file named MOM_be.accdb, which contains all of that database's tables. You also still have your original MOM.accdb database containing all the original queries, forms, reports, pages, macros, and modules.

However, MOM.accdb doesn't contain any tables. Instead, the Navigation pane shows links to external tables. In the Navigation pane, each linked table has an arrow to the left of its icon, as in the example shown in Figure 13-1. Pointing to a linked table's icon or name displays the table's true location in a ScreenTip at the mouse pointer.

Figure 13-1:

Arrows next to table names indicate linked tables.

Figure 13-1:

Arrows next to table names indicate linked tables.

Mouse Pointer Names

Splitting the database in two lets you keep the back end separate from the front end. The back end is the database file that contains only the tables. On a network, you can place the back-end ACCDB file on any shared folder in a network so that all computers in the network can get to the tables.

When not to split a database

Splitting a database isn't something to be taken lightly, just for the heck of it. After you split a database file in two, changing or deleting a field in a table becomes a real headache. You have to open the back-end database table and change the field there. Furthermore, Name AutoCorrect can't propagate a field name change through other objects as it normally would. So you might have to manually change the same field name in several objects in the front-end database file.

Splitting a database across multiple computers also slows things down because now there's the extra step of transferring all data to and from tables over the network. To keep life simple and not slow things down while you're trying to create a database, keep your tables, queries, forms, reports, macros, and modules all in one ACCDB file. Don't even think about splitting things until you've created everything you need — and are sure that everything is working.

If you want to try it, start with a copy of a database (so that you still have the original unsplit database available to you). Open that database with Access in the usual manner, and then use the Database Splitter to split the database. For more information on the whole shebang, open Access Help go to or http:// search.microsoft.com and search for the keywords Database Splitter, Linked Table Manager, and Name AutoCorrect.

After you place the back-end database in a shared folder, the next step is just a matter of opening the front-end database to make sure that it can find the linked tables. If you've changed the location of the back-end database since splitting the tables, you can use the Linked Table Manager — available in the Database Tools group on the Database Tools tab — to reestablish a link with those tables at any time.

Back in the MOM.accdb and MOM_be.accdb example, you might put MOM_be.accdb in a shared folder named MOMFolder on a computer named NetPC and then open the front-end database, MOM.accdb, on a computer other than NetPC. If the links to the back-end database fail, just click the Linked Table Manager button on the Database Tools tab and set the path for all of the tables to the new location. In this example, it's \\NetPC\ MOMFolder\MOM_be.accdb.

After you reestablish the links, you can install the front-end database on any computer in the network that has Microsoft Access installed. For example, Figure 13-2 shows how the back-end database is installed on one computer, which acts as the server by serving table data to all who request it. Each of the other computers has a copy of the MOM.accdb front-end database installed, so each of those computers has access to exactly the same backend tables.

Everything we discuss to this point in the chapter can be done without using any VBA. The Database Splitter and Linked Table Manager tools are both right on the Database Tools tab in Microsoft Access. After you establish a valid link between the front-end and back-end databases, everything else is automatic. As far as queries, forms, reports, macros, and modules go, a linked table is no different from a local table (a table that's actually in the current database, not just linked).

Was this article helpful?

0 0

Responses

  • nicole
    How to install front end in access in network to after splitting database?
    8 years ago
  • jarno
    How do you create a client server for access 2007?
    8 years ago

Post a comment