Use of the SUBST command to reduce breakage of Access links

[jgfaughnan]

[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
(e-mail address removed)
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/micr..._frm/thread/aebc5be5ea2f31ad/556c23eda8a0c227

 

[Stefan Hoffmann]

hi,

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 <--

 

[Guest]

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.

[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
(e-mail address removed)
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/micr..._frm/thread/aebc5be5ea2f31ad/556c23eda8a0c227