Feb 032009
 

I am beginning a new series called “Field Notes” to share what I learn in the trenches with you and to share the breadth and details of the work I do. As with most client visits, it all starts from an email.

Email:
From: Client
Sent: Friday, January 30, 2009 11:44 AM
To: David W. Grigsby
Subject: Help with Work Project

Dave:

It is good seeing you on Facebook. I am struggling with a project and I am wondering if you can help or point me in the right direction. Any help you can provide would be greatly appreciated. The problem does not seem that difficult to me at first glance but I seem to be spending more time on this than I should. I have never done it before. I am thinking that someone with specific experience could help me do it fairly quickly.

Basically, we are migrating scores of files to a new set of servers. This migration will result in a new set of drive letters and the like. The problem is that this move will screw up some legacy Access databases that point to each other. Basically, I have a single back end database with links to three front end databases. I also have some Excel spreadsheets that pull data from the back end Access database. We use Office 2003.

I have not been able to figure out an easy way to update the linked tables in Access or Excel to reflect the new drive mapping. To make this more complicated – the creator of these files is long gone and there is no documentation. These databases contain extremely important financial data and are used daily.

In a nutshell – I need help moving Access files with links from point a to point b.

Client

P.S.- I am generally aware of the Link Manager tool in Access. I have been unsuccessful in getting it to work. I think I need to update the password configure file first.

Overview:
Migrate several Microsoft Access Front Databases with their corresponding Back End Database from one location to another where the “Drive Mappings/Letters” will be different. This is a nice Chicken and the Egg Trap and the scenario is very real. You can’t change it after you move it as it won’t open and let you use the Linked Table Manager. You probably can’t change it before as it live but the new drive letters are only available in a test environment for Qualification.

Why this happens:
Information Technology (IT) or infrastructure services will needed to change drive mappings (mount points) for shared file resources on a server, cluster or Storage Area Network (SAN) due to storage limits, line of business application changes, new operating systems on the server or workstation, etc. The list is fairly long and this can happen as often as the postal service changes the price of a stamp 🙂

Approach and Traps/Issues:
1.
Move the files to the new drive mappings in a test environment

Issue:
Now you can’t update via the Link Manager because you can’t open needed front end database file because the back end database file has moved. There is an exception if you created have code in the front end to auto fix, aka ask you where it went as the default is to just error and exit.

Trap: You can’t get to the Link Table Manager as the Front End won’t stay open after the error

2. Change to location in live environment before you move

Issue: Risk of Changing mappings while users are in the files as you have to open Front DB to make changes via Linked Table Manager

Issue: In certain cases there is not a way to map new and old drive Mappings if you use same drive letter for different locations requiring different credentials as the group security file may hinder you when you are using different credentials

Trap: can you say scrambled eggs and corrupted/inconsistent data….

3. Copy Files locally where you can create drive letters via Subst for how it will be mapped both by drive letter and folder path
Issue: Need enough storage and the rights under your documents folder to create folders and copy front end and back end files
Trap sprung safely: This works as you removed all the barriers, access rights, changing login scripts, changing the files while users use them

We went with Number 3 🙂

Tools Needed:

Susbt and it comes with all versions of windows currently supported 🙂 You will be running this command from the command prompt.
Net use with show you what network drives are already mapped, map drives and delete mapped drives

Below is a screen shot of the commands in use.


Resources:
1. A copy of front end db, work group file, back end databases, only the Front End will be changed by the Link Manager

2.
Here is a link to a great pdf that covers db design and separating access db’s into Front End and Back end
http://oit.wvu.edu/training/classmat/db/dbdesign.pdf
Page 20 of the manual, not page 20 in the document, goes over the steps and you must select the check box first as in the steps, otherwise you will not get the prompt.
Here is an excerpt:
Linked Table Manager
To update the linked tables in a split database
1. Go to Tools> Database Utilities> Linked Table Manager
2. Make sure to check the Always prompt for new location check box.
3. Choose Select All
4. Click OK

How to disarm the trap in steps (generic as you will need to fill in the needed drives, files,test script):
1.
Create folders locally under your my documents folder to match the existing folder structure as on the network
Remember you can only change drive letters and paths if you set up two folder structures and copy the back end database to both. Most only change drive letters so the same folder structure with two drive mappings work and thus you only have one copy of the files.

2. Copy Front End database, work group file and Back End database from your live drive mappings to your local folders

3. Open a command window via the start menu run and type in the command cmd

4. From the command window disconnect live drive mappings via net use yourdriveletter: /d (your login script should return them after you log out and log in.
If the where created with a net use with the /persistent:yes, then note what it is mapped to so you can remap when you are completed)

5. use the Subst in the command window to Subst the old drive letter to new local folder path and new drive letter to the same local folder path – see above screen shot on subst command

6. Open front end database from old drive letter from Microsoft Access

7. use link manager from in Front End Database to be able to update the links to the new drive letter

8. Goto Tools, Database Utilities, Linked Table Managers, check the box for Always prompt for new location, select all, select ok, pick new location on new drive save

9. From the command window delete old drive via subst command

10. Open Front End database from new drive letter and check that it is working

11. copy Front End file(s) to a test workstation with new real network mappings in your test environment and run your test script to confirm there are no other “embedded” drive letters in the macro’s or vba code, and that the front end database(s) can access the back end database(s).

12. Correct hard coded letters as needed. (we had a couple of these)

Additional Considerations and lessons learned:
a. To prevent this you can use environment variables that are set by login scripts, etc. and by adding config code to read the env vars to make the application more resilient.

b, Make sure you need all your links, is there dead tables/dbs in the link list? In our case we found many and it was helpful to all to remove them to reduce confusion.

Result:

Nice client email and of course billable time 🙂

From: Client
Sent: Thursday, February 05, 2009 12:56 PM

To: David W. Grigsby
Subject: Thank You

I want to thank you for your help on Tuesday. I think we made good progress and I feel very optimistic about the project.

Client