Differing Network paths

[mrf1xa]

Wonder if anyone can help me on this one. I have a split db with the tables on a server. Trouble is, depending on which office (and sometimes what floor!) the user is at, the PC's are mapped to the drive differently. So for 1 PC MyDrive could be mapped to K:\, on another it may be Q:\. Indeed K:\ may be present but relate to another drive! Don't ask why- the wonders of our IT setup are quite beyond me!!

Does anyone know a way of getting the DB to look for the drive NAME, which remains constant, instead of the drive letter?

Many thanks

Nigel

 

[jebry]

Hi Nigel!

I have always been able to do this through the Linked Table Manager. I start out going to MyNetworkPlaces and choosing Entire Network. From there I can find the network servers and choose the one I need and follow the standard directory/subdirectory setup until I get to the database I am linking to.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[danvlas]

When linking the tables, don't use mapped drives, but browse to Network Neighborhood to get paths like
\\ServerName\ShareName\FileName.mdb

In this way you'll have everything OK.

Dan

 

[mrf1xa]

Hi Jeff, thanks for the swift response.

Linked table manager is how I usually do it, but this network is a pain. If I use the Linked table manager on one machine, and then go back and look at the results, it says something like tblMyTable (K:\MyDir\MyFile)

If I then send the frontend to another user, it produces and invalid path error, because their machine needs to look at Q:\MyDir etc. Same drive, different letter. It works fine if I then use the linked table manager on their PC, but I don't fancy doing this 50 times over when I send out the final version!

Nigel

 

[mrf1xa]

aha, I think I see the difference now, will give it a whirl when back in office.

Sorry to appear dense!

Nigel

 

[danvlas]

Run this code when opening the front end database (in the Open event of the main switchboard)

Sub RefreshLinks()
Dim tdf As TableDef
Dim newconn As String
newconn = "\\ServerName\ShareName\FileName.mdb" 'New path

On Error GoTo ErrLinks
refreshconnection:
newconn = ";Database=" & newconn
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = newconn
tdf.RefreshLink
End If
Next
MsgBox "Tables successfully linked"

Exit Sub

ErrLinks:
Select Case Err.Description
Case 3011
MsgBox "The database " & Right(newconn, Len(newconn) - 10) & " does not contain the " _
& "table '" & tdf.NAME & "'. Please select another database.", vbCritical, "Invalid database"
Case Else
MsgBox "Error: " & Err.Number & "-" & Err.Description
End Select
Exit Sub
End Sub

Change or build newconn as necessary.

It should work...

Dan