Linking Data from Excel to Access Drive letter problems 3

[Stoffman]

My Application is used in different locations, by different members of staff. Part of the application uploads data from an excel spreadsheet. The problem arises when different members of staff have set up there Network drives as different drive letter alias's. For example the linked table in Access connects to my L: Drive which just happens to be another blokes J: Drive. When he clicks on the'Upload Data' button, it doesn't work because it cannot find the spreadsheet as he doesn't have his L: drive mapped to the same drive address.

Is there anyway to link data using the drive address and now the Drive letter (as you can when setting up shortcuts to your favourite files).

Of course the best answer would be to make sure that everyone sets up the same drives with the same drive Letters, but it just isn't possible as the moment

Any ideas ?

 

[JerryKlmns]

Stoffman,

I had to run yesterday, but I m back and just saw what PHV wrote. Sorry, mea culpa, forgot that CurrentProject is not known by Access97.

I 've converted to Acc97 (not my best field though). In case you haven 't solve it yet with jebry's suggestion, give it a try and let me know.

Sub ReLinkThem2()
Dim tbl As New TableDef
Dim varA

For Each tbl In CurrentDb.TableDefs
    If tbl.Connect <> "" Then
        varA = Split(tbl.Connect, "\")
        varA(0) = ";DATABASE=\\lmpstmf05\rtp"
        tbl.Connect = Join(varA, "\")
        tbl.RefreshLink
    End If
Next tbl
End Sub

In a module, copy paste the code, press Ctrl+G, in the immediate window write this line

call ReLinkThem2

and hit enter. I hope it works.