Linked Drive Letters

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

In a front-end/back-end configuration, I've encountered
users that have their hard-drives assigned as something
other than "C". With that, I've had to walk them through
re-linking of all the tables before the newly installed mde
will function properly.

Is this a common-place problem? If so, what's the best
way to resolve this from code? I.e., are there facilities
within VBA I've not yet discovered that will allow me
to re-link all the tables to a different back-end mdb?

Thanks,
Bill
 
Bill,

You could try getting the environment variable "windir"(this may depend on
which type of system you are on; I'm on NT). Assuming your backend and the
system directory are on the same drive, you should be able to "divine" what
your drive letter is.

Sorry, I do not have the code to get an environment variable with me right
now and I cannot find it. I believe it is called "ENVIRON()", but that is on
Access97.

Good luck!
Jonathan Scott
 
Bill,

BTW, here is my code for relinking tables on the fly. It is not fully tested,
but has worked for me. It might not be fully robust, but I'd sure like to
have some feedback if you use it. (I've never seen the "Contact
Administrator" error message, and it fails if there is no link to the
specified table)

Private Sub reconnectTableToBackend(tableName As String, dbSystemPath As
String)
Dim dbSystemConnectString As String
Dim tables As TableDefs
Dim thisTable As TableDef

Set tables = CurrentDb.TableDefs
Set thisTable = tables(tableName)
dbSystemConnectString = ";DATABASE=" & dbSystemPath

If (Not alreadyConnected(thisTable, dbSystemConnectString)) Then
With thisTable
.Connect = dbSystemConnectString
.RefreshLink
End With
End If

If (StrComp(thisTable.Connect, dbSystemConnectString, vbBinaryCompare) <>
0) Then
MsgBox thisTable.Name & " is still not connected. Contact
Administrator. " _
& vbCrLf & "'" & thisTable.Connect & "'" _
& vbCrLf & "'" & dbSystemConnectString & "'"
End If
End Sub

Jonathan Scott
 
If your users don't have the same drive mappings, you can use UNC format
(\\server\share\folder\file.mdb) rather than e:\folder\file.mdb. To link
this way through the Linked Table Manager, find the file through the Network
Neighborhood (or whatever it's called in your operating system).

See http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web" for an
example of how to relink in code.
 
Hi Doug,
Although I can certainly use the UNC format to link up my back-end
tables, the \\server\share names on my machine would most certainly
not match those of any of my users. Or, did I miss something in what
you were suggesting?
Bill
(PS) I'm studying the code at the URL you sent.
 
Not sure what you mean that "the \\server\share names on (your) machine
would most certainly not match those of any of (your) users".

\\server\share refers to your file server, not to anything on your machine.
 
Doug,
There is no file server per se' on a single machine configuration,
unless there's more notion to the UNC format than I'm aware of.
My only experience with the use of UNC is in a "small office"
environment where a file is shared and other machines "see" the
file via the use of a named "workgroup". In such a configuration,
the name of the "workgroup" is fixed and likewise with the hard-drive.
Which is what I meant by "names on my machine".
Bill
 
Doug,
The defUNC notes:
"For example, the UNC name for a database named
Northwind.mdb on a shared directory named Samples
on a computer called MyWorkstation would be
\\MyWorkstation\Samples\Northwind.mdb."

It seems to me that one is still locked into the name of the
machine. So, as that varies between my machine and
someone else's, that UNC would fail, would it not?

Bill
 
Doug,
By-the-way, I did re-link my mde front-end using the UNC
format using "My Network Places", which seems to take
forever, but a user on a different machine would STILL
have to re-link the tables upon installing a revised mde. I
am attempting to make the installation of any revised mde
as painless as possible for the naive user.

My current thought, the UNC questions notwithstanding, is
to ask the user to use an "install shortcut" upon installation
of a revised mde that requires a re-link of the back-end
tables. That approach would make use of the Command()
function, see my post "Passing augment to mde", out of
which I would obtain the location of the back-end mdb
and re-link in code per Dev Ashish's contribution. With
that approach, the re-link only has to be done once with
the introduction of the revised mde.

Bill
 
Back
Top