Use of the SUBST command to reduce breakage of Microsoft Access 2003 links - an esoteric hack for escaping DOS 2.1's "path trap".

Where to pass on these kinds of tips and techniques for working around Microsoft's kludgy applications? It's a bit of a puzzle since Microsoft's products have an unlimited number of oddities requiring an unlimited number of hacks. This is but one of the myriad ...

I posted this one to Microsoft's high quality Access queries newsgroup, and since I use this blog to keep track of things like myself I'll post it here too. It's probably too esoteric for anyone else though ...

Use of the SUBST command to reduce breakage of Access links

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.

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.

Indirection is a member of the interesting class of things that are as unappreciated as they are valuable. Nobody ever mentions file system indirection as the most important innovation of the early Macintosh, but I think it really was. Twenty years later XP is almost as much a "prisoner of the path" as DOS 2.1. Sadly, OS X has regressed, though it's still well ahead of XP.

Despite the nastiness of using a fully specified path name to implement data table links in Microsoft Access, I do have to say the "link to table" technique is very useful and has very impressive performance and reliability.