jwcolby
jwcolby at colbyconsulting.com
Thu May 8 11:41:25 CDT 2008
I would suggest telling these folks you need to start NOW and work on this full time until it is finished. For testing purposes you will of course need an alternative location set up to mirror the existing location, particularly if paths are critical to other applications, which is entirely likely. John W. Colby www.ColbyConsulting.com rosalyn.clarke at barclays.com wrote: > Meep. > > 1. The H:\ alias will be used for a different server. > > 2. The files are used by about 30 people on a corresponding no. of machines, > they are currently fat-client, they will be moving to a combination of > fat-client and citrix server farm. The desktops are all in one office, the > server farm is in another city but all are in the UK. > > 3. Everything is stored on the H:\ drive as far as I know. The bulk of the > job is Access - about 70 databases - and they are Access 2000. > > 4. The yucky bit - ofc I don't really know as I have had approximately thirty > minutes so far to investigate this, but from what I've seen / heard: > - Word templates with macros creating / copying / deleting files on the H:\ > drive, > - Word documents merging data from Excel spreadsheets > - Word documents merging data from Access databases > - Access databases referencing tables in other Access databases > - Access databases exporting data to csv & text files, via VBA code and > macros > - Access databases exporting data to Excel spreadsheets, creation / copying > of Excel spreadsheets in the process > - Excel spreadsheets linking data from other Excel spreadsheets > > What other nasties may be lurking I don't know. References in queries! I had > forgotten you could even do that. :( > > Sorry for the lack of info. As usual I panicked first and thought later - > well I haven't stopped panicking yet to be honest! > > I was thinking maybe a constant in a .txt file or something that was copied > to each user's profile, or perhaps put on the new share. Then using my sweep > through the code to write something in that pulls it from the file, as you > say. > > It is *definitely* a can of worms. They pay me by the day so it's only the > timescales that hurt - but still I should have asked for more money! > > Thanks for your input > > Roz > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: 08 May 2008 13:58 > 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 > > This e-mail and any attachments are confidential and intended solely for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this e-mail in error, please notify the sender immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments. > > Internet communications are not guaranteed to be secure or virus-free. > The Barclays Group does not accept responsibility for any loss arising from unauthorised access to, or interference with, any Internet communications by any third party, or from the transmission of any viruses. Replies to this e-mail may be monitored by the Barclays Group for operational or business reasons. > > Any opinion or other information in this e-mail or its attachments that does not relate to the business of the Barclays Group is personal to the sender and is not given or endorsed by the Barclays Group. > > Barclays Bank PLC.Registered in England and Wales (registered no. 1026167). > Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom. > > Barclays Bank PLC is authorised and regulated by the Financial Services Authority. >