Relink Excel Spread Sheet to Access (on Shared Drive)

[jpstory]

Hi All

I have the following code that relinks tables to Excel files.
It works when the Excel files are saved on my local machine but fails if the files are on shared drive (a mapped drive).
Is there any work around to get it work on shared drive? Thanks !

Sub Test()
Dim db As Database
Dim tbl As TableDef
Dim MyTbl As DAO.TableDef
Dim Path As String

Set db = CurrentDb

For Each tbl In db.TableDefs
  If tbl.Name = "TblA" Then
    Set MyTbl = tbl
    MyTbl.Connect = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Test Folder\Testing\Test File A.xls[Sheet1$]"
    ' Fails if I Switch to Share Drive: MyTbl.Connect = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=T:\Test Folder\Testing\Test File A.xls[Sheet1$]"
    MyTbl.RefreshLink
    Exit Sub
  End If
Next
End Sub

 

[apr pillai]

1. Ensure that you don't have any access privilege issues with the mapped network server.
2. Use UNC (Universal Naming Conventions) for linking the table rather than the mapped drive.
3. change the link address:

T:\Test Folder\Testing\Test File A.xls[Sheet1$]

to \\ServerName\Test Folder\Testing\Test File A.xls[Sheet1$.

This method will allways ensure that the table is linked from the correct location from server and doesn't depend on the mapped drive letter.