Splitting Your Database to Make it Easier to Protect | Real World Microsoft Access Database Protection and Security

Splitting the database into a back end with tables and relationships and front end with objects like forms and reports is an important step in protecting and securing the database. This additional protection comes about because you need to use different methods of protection for objects (in the front end) and data (in the back end).

After splitting a database, the front-end database communicates with the back-end database through linked tables. A linked table establishes a connection to data in another Access database or in a different format such as SQL Server, FoxPro, or MySQL. Linked tables follow all the rules of the remote Access database and allow you to use that information without opening the back-end database. Splitting the database into two Access databases is a very good way to become accustomed to working with a data-only database. By adopting this model, you are well on the way to making your database talk to other databases that offer enterprise-level database security, something that Access will never match.

The Database Splitting Wizard

To practice splitting a database, I suggest that you follow these steps. If you do not have a copy of the Northwind database or you would like to bring back the original, read the instructions in Chapter 1.

Open Windows Explorer, copy the Northwind database from the sample directory, and place it in a temporary directory on your computer, such as in C:\Temp.
Open the Northwind database in C:\Temp.
Start the Database Splitter wizard (shown in Figure 4-2), which you can open from Tools ˜ Add-ins in Access 97 and from Tools ˜ Database Utilities in Access 2000 and later. With Access 97, you may need to install the advanced wizards.

Figure 4-2: The Database Splitter wizard in action. Heed the warnings!
Enter a name for the back-end database (shown in Figure 4-3). This new back-end database will now hold all the tables and relationships from the current database (Northwind.mdb). The front-end database will now have links to these tables, which will be visible under Tables in the Database window (shown in Figure 4-4). I recommend that you place the back-end database in the same directory as the current database while you get used to working with linked tables.

Figure 4-3: Enter the destination of the back-end database.

Figure 4-4: Arrows next to table names denote linked tables.

Tip
Access 2002 provides quick access to sample data. Simply choose Help ˜ Sample Databases, and you can select one of the sample files installed on your computer.

Why You Should Split Your Database

Splitting your database is important for many reasons:

Development can proceed while people are using the database.
Current wisdom says that one should split the user interface (UI) from the data (sometimes referred to as n- tier ).
Testing UI changes won't affect the "live" system's data.
The quicker the users adopt a development database, the more likely the development will proceed in the right direction. I've yet to run into a user who really tests an application before it goes live.
If you are an external developer, your client is far more likely to reuse your skills if you are maintaining the front-end database. I know this from personal experience.
Splitting a database makes it much easier to plan for and convert to a server database such SQL Server or Oracle. Upsizing to SQL Server is made easier because you can modify the access table links to point to server-based tables with the same names. Once you have split the database, testing changed links can carry on independently of normal database operations.
Front-end databases are very suited to conversions to the compiled Access database format (called MDE format). This format totally secures forms, reports, and modules (stops design view). Read more about it in Chapter 11.
Protecting data in a back-end database is easy to focus on when the database isn't crowded with other objects.
Users in a front-end database won't be able to change the design of a table unless they physically open the back-end database.
Linked tables will adopt the same workgroup security as the tables in the back-end database.

Now I will explain how to deliver and install a front-end database.

Delivering a New Front-End Database with Linked Tables

Now that you've split your database, the next issue you will encounter is that the physical location of your development back-end database won't be the same as that of the live back-end database. The exception to this issue occurs in the early phase of development, when the person(s) who are testing your database are using local drives such as C: or D:. If that is the case, make the location of your development back-end database match the location of your live back-end database, and save yourself some of the steps here. Of course, if you are using local drives for development and even to store important data, make sure that you have a backup system in place and, even more importantly, make sure that you can retrieve it.

Now let's see what you have to do to install a new front-end database as the live database.

Before shipping a database to the DBA, always put a version number on the startup form in the database. This way, you can actually verify whether the version someone is testing is the latest update.
Before shipping a database, it is always a good idea to make a backup copy of the database. I make a compressed .ZIP file and give the .ZIP file a name that includes the version number. Then I ship the compressed .ZIP file to the DBA.
Once the DBA has the database, he or she should replace the existing front-end database with the latest version. Generally, the DBA should have a recent backup of the current live front-end database, but if he or she doesn't, he or she should make one. Having a recent backup is necessary in case someone has changed something in the database and the DBA needs to recover those changes.
The DBA should now open the front-end database that contains links to the tables. In Access 97, choose Tools ˜ Add-ins ˜ Linked Table Manager. In Access 2000 and later, choose Tools ˜ Database Utilities ˜ Linked Table Manager.
In the Linked Table Manager dialog box, click Select All (shown in Figure 4-5). This action selects all the linked tables in the database. Now click OK.

Figure 4-5: Update the links by clicking the Select All button.
Choose the location of the back-end database by using the Find File dialog box and click OK. This action refreshes the links to the tables in your back-end database.
Check that a couple of the tables are working correctly before releasing the database to your users.

Note	If you rename or delete a table in the back-end database, you will need to delete the current linked table in the front-end database. If you rename a table, add a single link to the new table by right-clicking in Tables in the Database window and choosing Link Tables.

If you are still not sure about the benefits of linked tables, then why not ponder this little story from the days when Digital VMS and UNIX were a big part of my programming life.

User Story

My first Access problem was linking. I remember back in about 1994 when I was an Informix DBA and programmer. Our company purchased some data-mining software to monitor our production data. As part of this project, the software company had to produce an Access 2 database to manage the production data. The first version of the database came out, and our company tested it and made comments. The second version of the software was delivered, and we hired a data entry temp to punch thousands of lines of data into it. Naturally, the instant real data had to be added to the database and issues arose, so we contacted the developer. He came back with these comments: "You will have to stop the data entry, put the database on a floppy disk, send it to me by courier, and I will send it back when I've made the changes (in two days)." This process was not ideal from our company's point of view, so I started to read the help manual (yes, there was one in those days), and there on page 13 was a section that detailed the importance of splitting the database. We informed the developer (who, we found out, was new to Access), and he promptly split the database and our data entry temp got back to work. So too did the developer!

Your Very Own Link Manager

Sometimes the Tools menu is no longer visible because the Allow Full Menus startup option is cleared. Other times finding and using the Link Manager wizard can become a little too onerous for a busy client. If this is the case, you should consider installing a customized link manager of your own. To show you how to do this, I have included the following objects in the demonstration databases.

For Access 97 and Access 2000:

frmRelinkDatabase . This form shows you how to call the link manager function.
basGR8_RefreshTableLinks . This module refreshes the table links. If the backend database does not refresh properly, the user can locate it with the Windows File dialog box.
basGR8_Startup . This module has a number of shared routines.

For Access 2002 and Access 2003:

frmRelinkDatabase . This form shows you how to call the Link Manager function and also how to use the FileDialog object in Office 2002.
basGR10_Files. This module refreshes the table links. If the back-end database does not refresh properly, the user can locate it with the Office 2002 File dialog box.

Instructions for installing this link manager software onto the front end of your split database system follow:

Import the objects appropriate to your version of Access into your database.
Modify the VBA code under the relink button (button 1) in the frmRelinkDatabase form so that it refers to a table in your back-end database. Figure 4-6 shows the demonstration form in the Access 2002/2003 demonstration database.

Figure 4-6: The frmRelinkDatabase demonstration form.
Move the VBA code to a suitable utilities form in your database that is accessible only to the administrators.

The VBA code that initiates the relinking software is very easy, as follows (see the code under button 1 of the frmRelinkDatabase form):

 If RelinkTables_FX("northwind.mdb", "orders", True, _   "C:\", True) Then   MsgBox "This database was relinked successfully." Else   MsgBox "Relinking was not successful." End If

The code demonstrated for relinking had its origins in a database on the Access 97 CD-ROM called Solutions.mdb. The Access 2000 version of Solutions.mdb is available to download; see the Further Reading section at the end of this chapter for more information. I recommend this database as a useful resource. For the Access 2002/2003 database, I have rewritten the file selection code from the solutions database to use the FileDialog object that was bundled as a shared Office 2002 component.

Implementing the Relinking Software in Your Database

If you have cleared the Allow Full Menus check box in the startup options (as discussed in Chapter 2), the DBA won't have access to the conventional link manager to relink the tables. Therefore, you're going to have to start your own relinking software from your front-end database.

From my experience, I know that you will want to keep this relinking facility away from your users. The most common problem that I have experienced is when users try to relink from a drive mapped as F: when the linked path for the database was previously stored as the G: drive. In this instance, the links and the software the front end will stop functioning for all the users who have the G: drive mapping. For this reason, I add relinking software to the front-end databases in an Access form that is known only to the DBA. That way, this unwanted new link definition becomes much harder for the F: drive user to create.

Two other examples are on the frmRelinkDatabase form. Button 2 demonstrates how to use the FileDialog object to find a single database. Button 3 is a very simple demonstration on how to open the Link Manager wizard by using VBA code, as follows (only available in Access 2002 “2003).

 RunCommand acCmdLinkedTableManager

Now I will explain how you can avoid the incorrectly mapped drive issue by using the universal naming convention (UNC) for the network location of the back-end database.

Linking by using UNCs Rather Than Mapped Drives

Access 2002 help offers this advice: "Important: If you link to a file on a local area network, make sure to use a universal naming convention (UNC) path, instead of relying on the drive letter of a mapped network drive in Microsoft Windows Explorer. A drive letter can vary on a computer or may not always be defined, whereas a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table."

An example of a UNC for a folder called "data" on a computer called "shared-Computer" is

 \sharedComputer\data

My experience with UNCs is that they are not very popular with people who are not network administrators. Therefore, it is difficult to get people at remote sites to use them. Most people just seem to get the G: drive (mapped letter) thing. In addition, I have found that if you are developing for a remote site, it is best not to use UNCs on your own network because it can take the Link Manager wizard a long time to resolve the links when the software is installed at that remote site. Users have reported that it can take up to 15 minutes to relink the databases that started with unknown UNCs, like our network paths. This amount of time, obviously, is not acceptable.

Databases that Should Not be Split

Sometimes it's best not to split a database, such as when a database has a very small number of users and a simple user interface. In cases like this, it will almost invariably be possible to take the database out of production for a few hours while you make changes. Alternatively, if you keep track of which objects (forms, code, reports) that you change, you can import them into the live database. If you adopt this approach, do so because you understand the situation, not because it is too hard to split the database.

Another tricky situation is distributing your database and interface on CD-ROM. In this case, the database will be in read-only mode, so you may want to combine the data and the interface so that you do not need to relink. In this case, relinking is impossible because you cannot write the new linking information to the CD-ROM and, the way PCs are configured these days, the address of the CD drive will change from PC to PC.

Installing the Front-End Databases on the Client PC

One more touted benefit of splitting a database (in the Access manual and in a number of Access books that I have read) is that you can install the resultant front-end database on each user's PC to increase performance. Though this is true, this type of installation should only happen near the end of the development process, when you are happy that the front-end database is working well and that network traffic isn't an issue. In addition, if you are contemplating workgroup security or protection measures in the front-end database, test a shared network version of the database before installing that version on all the client PCs. Remember that the more versions you have out there in userland, the more databases you need to keep synchronized with the latest protection initiatives.

Caution

If you intend to use operating system security on your database, you should be careful installing the front ends on the client PCs because doing so allows the user to find the location of the back-end database. In fact, you really don't even want the user to copy the front-end database, if you can help it. Read Chapter 12 for more on this topic.

So now that you have learned why and how to split a database, let's move on and find out why the VBA code in the front-end database requires an error handler.