[AccessD] Drive mappings & global constants

Roz,

1) What do you mean by "they will no longer be able to use 'h:\'"?  Are 
all the machines going to have some new network something mapped to h:? 
  If this is the case then Gustav's suggestion will not work since H: 
will be in use for something else.

2) How many machines use these files?  What is their physical location / 
distribution?  All in one building, all in one city, all somewhere in 
the UK?

3) Where are the applications stored and how are they referenced?  Local 
to the hard drive of the workstation?  Copied down to the local drive 
using batch files?  On the same drive H: that is being remapped? 
Scattered to hell and back on 47 different servers?

If they are located on the same H: drive (and H: is now going to be used 
for something else), then you need to look at whether there are 
shortcuts (to open the apps) on the user's machines that also need to 
change to point to the new location.  Perhaps not your job but something 
to keep in mind.

4) What kind of dependencies are we talking about?  Word docs trying to 
mail merge from data on that drive?  Word documents trying to store 
copies of themselves to that drive?  What kind of Excel dependencies? 
Access can be the worst of all since it can involve table links, 
embedded references in queries (IN 'H:\Somefile.MDB'), code references.

You probably don't have the facts yet but there really isn't enough 
information in your email to coherently discuss this thing.

AFAIK, there is no common "something" that all these apps (Access, Word 
and Excel) can reference.  IOW (again, to my knowledge) Word docs cannot 
"see" Access libraries or Excel VBA modules, likewise with Excel and 
Access.  This implies that each application, whether it be a word doc, 
excel doc or Access application will have to be hand modified.

You could write a piece of VBA code to open a file and extract the new 
"drive location", or to open a table, perhaps stored in an Access BE. 
Since the same VBA code can run in Word, Excel and Access, this might 
provide a way for each application to determine at run time where it 
needs to go to do whatever it needs to do.  For this to help of course 
you need a known location to store that file or BE.

Since we do not know yet what kinds of "dependencies" exist... it is 
hard to pin down how big the effort is in terms of the S&R.  Me thinks 
this won't be a half day job.  In fact me thinks this will be a can of 
worms.

John W. Colby
www.ColbyConsulting.com


rosalyn.clarke at barclays.com wrote:
> Dear List
> 
> I have just been asked to resolve something, in zero time as usual, and I'm
> sure there should be an elegant solution I just can't think of it.
> 
> There is a team that has 100+ Access databases, Excel spreadsheets and Word
> templates that contain dependencies on a particular network drive that is
> currently mapped to 'h:\'. In a few weeks time they will no longer be able to
> use 'h:\' and will be given a new drive mapping. At some point shortly after
> that, the server will be changed too, so the current DNS path will no longer
> be valid either.
> 
> I don't know -and nobody will say for definite - what the new mapping & new
> DNS path will be. These files are critical and the business insists on no
> more than 24 hours downtime.
> 
> The dependencies include import/export routines, linked tables & linked
> files.
> 
> What I would like to do is run a giant F&R on the code modules and replace
> the root drives with a constant, and then make the constant available so that
> ALL the applications use the constant. Then they can muck about with drive
> mappings etc. to their heart's content and I won't have to come back to the
> South coast and stay up all night changing code.
> 
> Any tips or ideas for making this work? Is it possible? I have code for
> updating the code modules that should almost work but I've no idea how to
> expose a constant across multiple apps.
> 
> TIA
> 
> Roz