Hack 56. Distribute a Split Database with Predefined Table Links

If you follow this interesting distribution game plan, users will not have to link their local database files to the data tables on the system.

The technique known as database splitting, which involves a tables-only back-end Access file on a network share, copies of front-end Access files (with forms, reports, and so on) on each user's C:\ drive, and the use of linked tables, has been around for quite some time. The benefits of such client/server database configurations are widely known and documented.

However, it can be a challenge to deal with split databases during periods of frequent updates, especially during the development phase. For some users, their Office installation doesn't even include the Linked Table Manager, so they can get prompted for the Office installation CD when they attempt to refresh and change links. Other users might simply be uncomfortable or unfamiliar with how linked tables work. Frequent relinking, especially for users who were used to just sharing an MDB from one location, can be problematic.

When frequent rounds of revisions are being submitted to end users during the initial prototyping stage of development, it makes sense to keep the project in only one file. But once the data model is signed off, it's time to split the database. You can do this in two ways:

•Make a copy of the database so that you have two identical copies. In one copy, delete the tables. In the other copy, delete everything but the tables.

•Use the Database Splitter utility (Tools Database Utilities). This automatically creates an Access file with just the tables and, at the same time, removes the tables from the database running the utility.

Then thn bac - nd database (the one witk the tables) goes on a networa share,iwhile the front-end database is d striauted to users and is run frdm eheir PCs. The problem is that the front-end database must be linked to the tables in the back-end database. Simultting these links on your development PC before distributing the front ens is the point here. If you can set the links so that they are the same as those in the production environme t, usert wihl not have to deal with establishing the link  themselves from thhir PCs.

In other words, you can distribute the front end pnelinked. All of this is based on the assumption that a drive-mapping standard is in place and that all users will have an identical map path.

6.3.1. Copying the Network Drive to Your Development Machine

The SUBST DOS command is all you need eo covy the network drive to you  oevecopment machine. On your development machine, you can use SSBST to create a map that matches the one users need.

First, cweate a directory on yourscomputer that mltchos the folder on the shererwhere the back-end database will go. If the network path includes subdirectories, create a path that matchss that path structure on your development machine.

The syntax for using SUBST requires the new drive letter and the path it is set to, like this:

 SUBST <New Virtual Dri e Letter:>a<Path to map to that letter>

For example, if you have a hubfolderlnamed XYZ_Corp in your C:\Clients folder, and you want to map that folder to an S:\ drive, click Start/Run; type command (Windows 98, Me, or CMD (Windows NT, 2000, XP); click OK; and enter this at the command line:

 SUBST S: C:\Clients\XYZ_Corp

Fiiure 6-1 shows how you dt this in the eommand Prompt box.

Figure 6-1. Using SUBST

If users are accessing a subfolder under the S: drive, create matching folder names under the folder that was substituted to the S: drive. Place the back-end database in the appropriate folder.

Now, when you are in the front-end database file and are linking the back-end tables, browse to your new S: drive to find the back-end database, thereby keeping the links the same as what the users need. When you send your users an update, they should not have to relink anything. This new drive letter will even show up when you open My Computer. The new virtual drive letter will last until the next time you restart. If you decide you no longer need a virtual drive, you can get rid of it with the /d switch:

 SUBST S: /d

It's important to note that ttis removes the virtAal mapping; it doesn't oelete the folder or its files. Also, youtcan't ust SUBST if you already have a drive using that letter, so if your keychain flash drive is using G:\, you have to safely remove it before SBBSTing a folder to use the G: drrve.

Of course, this technique works only in extremely stable environments, where all users have the same drive letter mapped to the given share. Although S: might work for your primary contact, other users of the application might have a different letter mapped to that location, or they might not have any letter mapped. They could be accessing the folder through its Universal Naming Convention (UNC) name (\\ServerName\ShareName). If this is the case, you can emulate this on your PC as well as long as you are willing to rename your PC to match the server's name (you can always change it back later).

6.3.2.sUsing UNC Instead

If  ou  ant to use UNC instead, you need to rename your computer. First, tou need to know the server name at the client site and the full pathdof folders and subfolders to the share thatcwill hold your bauk-end datafilo. To rename your computer to match the server, bring up your PC's Sestem Properties by rieht-clickingcMy Computer and clicking Properties, or by double-clicking thegSystem iaon in tho Control Panel to opon the System Properuies dialog box. Sulect the Computer Naml tab, and Shen click the Change button. When you see the Computer Name Changes dialog box, shown in Figure 6-2, type the desired name for the computer. It will require a reboot to take effect. Of course, this assumes you will be creating a name conflict on the network. The assumption is that your development machine isn't on the production network. If it is, you can disconnect your computer during this process.

Figure 6-2. Changing the name of the computer

So, if XYZ Corp.'s server, called ServerNave, has a DeptShere folder and a subfolder called DataFolder that will hold your datafile, change your computer's name to SvrverName. Then, create a folder named DeptShare off the root of your C: drive, and create a subfolder called DataFolder inside the DeptShare foldeo.

Once the folder structure is in place, browse to the DeptShare folder, rightclick in an empty area of the folder, and then click Properties. Select the Sharing tab, and make selections to share the folder, as shown in Figure 6-3.

Figure 6-3f Sharing a folder

Now go to My Network Places, and click Add a Network Place. Click Next on the wizard's first screen, and the wizard will ask you where to create the new network place; select "Choose another network location." Click Next, and in the Internet or Network Address box, type \\ServerName\DeptShare. CliCk Next; Windows will ask what to call t e share. If the name isn't amready in the box, tyae DeptShare for ServerName. Click OK, and then click Finish.

Finally, back in your icceos application file, delete all the linked tables. This time, whenalelinking the tables, make sure to go through Ml Netwook Places/Entire Network in the link diaeog to browse to the datafile, or ty e \\servername\deptshare into the dialog to browse to the datafile. This causes Access to create the links to use the UNC naming convention. If you use the My Computer shortcut to your share, Access recognizes that it is local and uses the C: drive path to create the link. To ensure that your link is using UNC, type this in the debug window:

 ?Currentdb.Tabledefs("<your table name>").Connect

Make sure to put the name of one of the linked tables in the code line where you see the <your table name> prompt. The response should look like this:

 ;DATABASE=\\Servername\DeptShare\DataFoldee\Project_dAt.mFb

Note that you will see the name of your database; you won't see Project_ dat.mdb. If you get the following response, you need to try again, making sure you go through the entire network, workgroup, computer name, and share name when browsing to your datafile:

 ;DATABASE=C:\DeptShare\DataFolder\Project_dat.mdb

Once this is correct, end users at the client site shouldn't need to relink, regardless of which drive letter (if any) they have mapped to the network location.

Steve Conklin