Use of the SUBST command to reduce breakage of Access links

[Apologies for an apparent cross posting -- I accidentally posted this
to .forms instead of .queries and I'm now posting correctly]

One of the great failings of the past 20 years of Microsoft's
dominance has been the failure to implement good file system
redirection. XP today is almost as dependent on absolute paths as it
DOS 2.1. [1]

In the world of Access this manifests as broken links to external data
sources. I use links very extensively in my data management work, a
typical project may contains dozens of query files with links to
dozens of data tables distributed over one or more drives. Any change
to any path, including renaming a folder or file or moving a file,
will break the links.

Access 2003 responds to a broken link by irreversibly breaking a query
on first use. It doesn't matter if you don't save the query when you
see it's broken, the query is now broken. (This may be fixed in 2007.)
If you're careful you can use Linked Table Manager to repair the link
before first use of the query, but if you foget you're in trouble.

Today I reinvented a workaround. I say reinvented because I found a
single mention of it in this newsgroup from 1999 [2]. It worked then
so I presume it works now. Seven years is long enough that I'll repost
the technique.

The trick is that DOS 3.x's SUBST command still works in XP. Indeed,
in XP you can apply a SUBST operation to path containing a drive
letter mapped to a network share.

The result is a de facto partial indirection layer.

Assume I have a database file john.mdb in c:\work\fark\dbase\cpt.

I run this command: SUBST P: c:\work\fark\dbase\cpt.

Now I create a link from a query database to a file in john.mdb

The link will have the path P:\john.mdb

Now I move john.mdb to e:\dbase\cpt

I now clear the P: substitution and run: SUBST P: e:\dbase\cpt
my links will not break.

For more information on SUBST simply type SUBST /? on the command
line.

Of course if Microsoft were to implement file system indirection, or
even relative paths in Access links, this kludge would not be useful.

john
jfaughnan@spamcop.net
meta: jfaughnan, jgfaughnan, Microsoft Access 2003, indirection,
redirection, link, linked table manager, 070620


[1] Mac Classic's greatest innovation was an absolute file identifier
that provided indirection, one could move files around without
breaking relationships. OS X, sadly, broke much of this, but OS X
today still has quite a bit of indirection.

[2] http://groups.google.com/group/microsoft.public.access.forms/browse_frm/thread/aebc5be5ea2f31ad/556c23eda8a0c227

0
jgfaughnan
6/20/2007 2:16:31 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
738 Views

Similar Articles

[PageSpeed] 29

hi,

jgfaughnan wrote:
> Any change
> to any path, including renaming a folder or file or moving a file,
> will break the links.
This will also break the substed drive.

> Assume I have a database file john.mdb in c:\work\fark\dbase\cpt.
Use a shared folder and UNC, e.g. \\.\myworkdir\.mdb . This will at 
least avoid problems with policies or devices using "your" drive letter.


mfG
--> stefan <--
0
Stefan
6/20/2007 2:29:03 PM
I've sang the praises of Subst many times over on Access-L. 

I use it on my home computer, which only has a C hard drive, when I'm doing 
work that needs to mimic a network mapped drive. For example having a back 
end .mdb file on an M-drive.

Of course with USB external drives such as flash drives, it's pretty easy to 
map them as an M drive also.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"jgfaughnan" wrote:

> [Apologies for an apparent cross posting -- I accidentally posted this
> to .forms instead of .queries and I'm now posting correctly]
> 
> One of the great failings of the past 20 years of Microsoft's
> dominance has been the failure to implement good file system
> redirection. XP today is almost as dependent on absolute paths as it
> DOS 2.1. [1]
> 
> In the world of Access this manifests as broken links to external data
> sources. I use links very extensively in my data management work, a
> typical project may contains dozens of query files with links to
> dozens of data tables distributed over one or more drives. Any change
> to any path, including renaming a folder or file or moving a file,
> will break the links.
> 
> Access 2003 responds to a broken link by irreversibly breaking a query
> on first use. It doesn't matter if you don't save the query when you
> see it's broken, the query is now broken. (This may be fixed in 2007.)
> If you're careful you can use Linked Table Manager to repair the link
> before first use of the query, but if you foget you're in trouble.
> 
> Today I reinvented a workaround. I say reinvented because I found a
> single mention of it in this newsgroup from 1999 [2]. It worked then
> so I presume it works now. Seven years is long enough that I'll repost
> the technique.
> 
> The trick is that DOS 3.x's SUBST command still works in XP. Indeed,
> in XP you can apply a SUBST operation to path containing a drive
> letter mapped to a network share.
> 
> The result is a de facto partial indirection layer.
> 
> Assume I have a database file john.mdb in c:\work\fark\dbase\cpt.
> 
> I run this command: SUBST P: c:\work\fark\dbase\cpt.
> 
> Now I create a link from a query database to a file in john.mdb
> 
> The link will have the path P:\john.mdb
> 
> Now I move john.mdb to e:\dbase\cpt
> 
> I now clear the P: substitution and run: SUBST P: e:\dbase\cpt
> my links will not break.
> 
> For more information on SUBST simply type SUBST /? on the command
> line.
> 
> Of course if Microsoft were to implement file system indirection, or
> even relative paths in Access links, this kludge would not be useful.
> 
> john
> jfaughnan@spamcop.net
> meta: jfaughnan, jgfaughnan, Microsoft Access 2003, indirection,
> redirection, link, linked table manager, 070620
> 
> 
> [1] Mac Classic's greatest innovation was an absolute file identifier
> that provided indirection, one could move files around without
> breaking relationships. OS X, sadly, broke much of this, but OS X
> today still has quite a bit of indirection.
> 
> [2] http://groups.google.com/group/microsoft.public.access.forms/browse_frm/thread/aebc5be5ea2f31ad/556c23eda8a0c227
> 
> 
0
Utf
6/20/2007 6:20:01 PM
Reply:

Similar Artilces:

How can I email an excel file using my outlook contacts?
"cheflady" <cheflady@discussions.microsoft.com> wrote in message news:F9C13E60-150D-4F03-B519-A8769F08CD0F@microsoft.com... > > <sigh> Please use the big white space to write your question........ exactly the same way you email ANY file..... go to file\send\mail recipient( as attachment) "Gordon" wrote: > "cheflady" <cheflady@discussions.microsoft.com> wrote in message > news:F9C13E60-150D-4F03-B519-A8769F08CD0F@microsoft.com... > > > > > > > <sigh> Please use the big white space to write you...

Changing Function to use a Date Input
Hi. can anyone please advise me on how to change the function keys.. sa F10, F11 & F12 to use to input a date into a cell. EG. F10 to be 01/01/04 F11 to be 02/01/04 F12 to be 03/01/04 What I require is to go to any cell and press the relevant function ke to Input the associated date Any help would be greatly appreciate Many thank Rob PS using Excel 200 -- Message posted from http://www.ExcelForum.com First, I'm not sure if you entered Jan 1, 2004, Feb 1, 2004, and Mar 1, 2004 or Jan 1st-3rd, 2004. Here are a couple of macros. The first turns this on, the second turns it...

tender using outside gift card
Hi, does anyone know how to 'pass' the transaction total to an outside exe or dll? we are trying to process the transaction with a gift card (not an internal gift card, this GC is another company who wants to 'share' the gift card balance). basically RMS needs to pass the transaction total to the exe (or dll) which will query the balance of the gift card (over the internet and/or local DB). the exe/dll will query its database and return a yes or no to RMS any help would be appreciated. scott@kanesvending.com -- Walt You use what's called a "Hook" function t...

Transferring data between worksheets using Sheet Command?
Hi for an assignment i have to enter grades for 200 students in 4 subjects. there is a front summary sheet that contains all the subjects and all the students and their overall grade GPA etc.. anyway this summary sheet has to be populated automatically from the individual math, english etc.. worksheets. The guide says to do it using the 'sheet command' any help greatly appreciated. Thanks ...

Quick Links on Home Page
If you set up a couple of custom quick links on your home page, is it being stored in a central location so that if you log in from a different computer (or in our case get connected to one of several GP app servers in our Citrix farm), you get the same home page? I am busy testing GP 2010 so this is new to me (we are in v9 currently). Also, is it possible to “copy” a home page to different users if you want to assign the same set of quick links to all of them? Thanks -- Pieter ...

Using 'Add to Favourites' option on a shared calendar not working
In Outlook 2007 we have a few users who are unable to add a shared calendar to their Calendars->Other Calendars list. The steps that work on a mojority of computers are: 1. Go->Folder List. 2. Navigate to Public Folders->All Public Folders->...->Communal Calendar. 3. Right Click on Communal Calendar and select 'Add to Favourites'. 4. Click OK on 'Add to Favourites' dialog. 5. Go->Calendar and the calendar should have appeared under 'Other Calendars' list. However, in a few cases when OK is clicked in step 4 the calendar never appears in...

CALCULATING DEPRECIATION USING ACCESS
HOW DO I DO IT? Why don't you ask the Access newsgroup instead of the Microsft CRM ng? -- Brandon IT Director Office Equipment & Supplies at http://www.presentationsdirect.com "PATRICK" <PATRICK@GLWHOLESALE.COM> wrote in message news:026d01c352d1$755a23b0$a101280a@phx.gbl... > HOW DO I DO IT? ...

How to use this DeleteEMFs() Macro
This macro was on the MicroSoft website as a work around for the .emf storage problem http://support.microsoft.com/default.aspx?scid=kb;en-us;299372 What exactly does this macro do and how should I use it? I want to remove all the .emf files from a particular Excel File. B/c the workbook creates hundreds or thousands of temp .emf files everytime it opens and it takes forever to open.. Thank you very much for any help! george Private Sub Workbook_Open() Call DeleteEMFs End Sub Private Sub DeleteEMFs() Dim fso As Variant Set fso = CreateObject("Scripting.FileSystemObject"...

Access denied after installation
Hi, I've installed the CRM 4.0, SQL Connector and the Email Router on my server. The installation was successful and without errors. Now I want to use the webinterface but even the domain admin has no access. The login failed with any user or admin. (I tested it with the domain admin and a local admin) The installation of the CRM for Outlook failed too, because nobody has access to the CRM. Can you tell me the things I have to do? Thanks!! Regards, Matze The first and basic thing 1 =) Have you restarted the server after installation. we need detailed error log . Please enable the Dev ...

how to reduce print time
I am printing name badges from publisher. I have a graphic and very little text on them. It takes over 45 minutes for the first page to print. What can I do to remedy this? If the graphic is a gradient it will slow the print down. Is your printer driver current? Have you looked at the support documents on the web site of your printer manufacturer? What version Publisher are you using? Take a look at the printers preferences. Right-click your printer in the printer folder, properties, Advanced tab. There are options you can try to speed up the printing. -- Mary Sauer http://msauer.mvps.o...

Links to multiple Worksheets
I have a workbook (Excel2000) with 130+ worksheets, I want to create a Master worksheet with a link to cell A1 of each worksheet in column A of the master worksheet eg. in the the master cell A1 is linked to cell A1 Sheet1, A2 is linked to cell A1 Sheet2 etc Is there a quick way to do this or do I have to create each link separately Thanks Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.ph...

Linking Non-Contiguous cells
I am trying to link different rows of data in Excel to a Visio diagram as labels. In Excel I have a document that has a header row with a multiple rows of data following. Each picture on my Visio diagram corresponds to a different row on the excel worksheet. So to link a lable to each picture in Visio I need the first row(header row) and the corresponding data row. In all but one, these rows are not going to be next to each other. I've tried to highlight the header row and the data row and use copy, paste special in Visio and it pastes every row between them instead of just the...

Outlook 2003: Copy Command Missing on Right-Click
In Outlook 2003, when I right click on an email message, only the move command is available; the copy command is missing! The only way I can copy an email message to another folder is to drag it with the right mouse button and then it comes up as an option. Is there a fix to this bug? Thanks... ...

Command Button
I would appreciate any help with this. I have a command button on my main form that when you click on it it will take you to the main switchboard and the reports page. This is the what I have now. My Switchboard has two pages. Page one has forms and button to go to the second page which lists reports. This only takes me to the main switchboard. If I type [SwitchboardID] is says there is no such field. I want the command button to take me to the second page of the switchboard and open reports. This is the info in the switchboard table for Reports. SwitchboardID = 2; ItemNumber =...

Using cell value in VBA sub
I'm using the following sub Sub FindRow() Dim rngFound As Range Set rngFound = Range("A:A").Cells.Find(What:="test", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry ""test"" was not found" Else rngFound.EntireRow.Select End If End Sub My problem is that if I excange Test with a1 - wanting to look the value in cell a1 I always get the first blank a-cel...

Complex Query question
I've got a challenging query I need help with! Here's a snapshot of the tables and relationships. I've put the relevant tables into this query layout just for illustrative purposes. ----- click on it to make it bigger (it's still a little hard to read) http://tinypic.com/view.php?pic=6xu9oh4&s=1 Note - the table names start with phrases that are not relevant to this question (ie I use the word "record" for another use than the normal database usage of that word). ------ I'll explain the setup. I want the Table Record-Orders-Sales to lookup Record-Cur...

E-mail links
After switching from my ISP dail up to cable I am unable to click on a link in outlook 2002. It opens the browser but does not fill in the address. Any suggestions would be appreciated Thankyou Do other URL links within the Windows environment (in a word doc, on the desktop etc) also result in this behaviour? I had something similar to this happening for many many months. It turned out a few DLL's had to be reregistered. I actually don't recall exactly what I did... I know urlmon.dll was one of them... but there were a few others in my case that needed to be registered. There are ...

How do I create a topographic map using Excel?
I am a teacher and need to create a topographic map using Excel? I can not figure out how to get a third axis. Any suggestions would be more than helpful. You could make a contour or surface chart. This doesn't work with any X and Y values you want to include, however. You need regularly spaced X and Y values, categories, really, and exactly one Z value for each possible X-Y pair. It's pretty unsatisfactory for a topographical map, unless you generate loads of X and Y values. The data has to be arranged thus: Y1 Y2 Y3 X1 Z11 Z12 Z13 X2 Z21 Z22 Z23 X3 Z31 Z32 ...

Use of Indirect
I am trying to do something similar to what I have seen on one of these discussions. I have sheetnames of the other worksheets in a workbook in row 1 of a worksheet and am wanting to refer to a formula in cell A33 of each of the works - I've always had problems getting my head around this "INDIRECT" function. For instance in D31 I'm trying to get the result ="SheetnameD1"!A33 using something like: =INDIRECT("'"&D1&"'!A33",), but there are two problems with this, 1 this returns a #REF! error, and 2 copying it across the rows wil...

Outlook 2003 hangs when using Word 2003 as email editor
I'm having a recent problem with Outlook 2003. When using Word 2003 as email editor and attempting to reply to a message, my application hangs for a long time. I then can release it only but closing Word through task manager. Microsoft is evidently aware of this problem by posting "Microsoft Knowledge Base Article - 278214," but without providing a resolution or workaround. I've repaired Outlook and uninstalled both applications, then reinstalled. I've deleted the 2 add-ins and installed Outlook and Word without any success. Can anyone help me? I really li...

IE will not access Windows update page
Internet Explorer cannot display the webpage browser address shows: <http://windowsupdate.microsoft.com> "updates are ready for your computer "KB952069" will not install and notification continues to pop up after automatic updates says "Installation Compete" Dell Mini 1010 Windows XP Home 32bit SP3 1gb RAM I cannot access Microsoft Windows Updates. All other sites work just fine. Original issue: Could not install any programs Software ran: Malwarebytes, Adaware, Spybot, Trojan Remover and CCleaner After finding various critters affecting PC...

anonymous access
Hi , some one is generating mails on behalf of me. how to identify the whi is doing that. No one is permited to send mails on behalf of me. It's kind of virus mail. Please help me to resolve this Thank Rudera Depends on *exactly* what you mean, and the mode of OL "Rudra" <rudra.murthy@solix.com> wrote in message news:DF58C9BA-E92F-4D3C-9590-71905507B29A@microsoft.com... > Hi , some one is generating mails on behalf of me. how to identify the whi is doing that. No one is permited to send mails on behalf of me. It's kind of virus mail. Please help me to resolve this ...

linking spreadsheet colour to chart
I have data that I must produce in a chart. Normally from a pivot table. The certain parts of the data are colour specific from an index. Is there anyway (other than filling the chart colours in manually) to link the data from the spreadsheet to the chart? ...

Access 2.0 Converter 09-27-04
I have installed the converter for moving from Access 2.0 to Access 2003 running under Windows XP Professional. However Access 2003 still tells me I need to convert the database and does not recognise the converter has been installed, so points me again to the Microsoft Website to obtain the converter. Any suggestions please. Hi Colin, My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups. Are you sure that you ran the .exe file for the Access 2.0 Converter so that it was installed? To download the Access 2.0 Converter free of charge, visit the following Micro...

Access Query result different when opened in automation
I have a query that pulls information from three different linked tables. (this might very well be the source of my problems but I dont understand why) When I run the query manually in Access the result set is as I expect, but when I use that query in a function in Access VBA, the where clause is partially ignored. The part that is ignored is here: (ServerDetails.SERVER_TYPE) Not Like "*Domain Controller*") The full filter is here: WHERE (((ServerDetails.ENVIRONMENT)="HOME") AND ((ServerDetails.SERVER_TYPE) Not Like "*Domain Controller*") AND (((ServerDet...