Max Wanadoo
max.wanadoo at gmail.com
Thu May 8 08:17:58 CDT 2008
Gulp! Don't worry Roz, I think John is just trying to cheer you up for the weekend. Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Thursday, May 08, 2008 1:58 PM To: Access Developers discussion and problem solving Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com