Locating linked tables on network

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a user group that has multiple MS Access and MS Excel Spreadsheets that have linked tables and worksheets. They were all linked on the network by the drive name (c:\, v:\, etc) instead of the actual network server name - the user group is currently moving network drives so all the databases/spreadsheets will be moved from o:\ to s:\, unfortunately! Is there an easy way to search the o:\ drive, locate each file and check to see if there is a linked table or worksheet programmically and open the file and update the location of the drive??? There are over 100's of files we believe. Thanks!
 
In Access you should use the Linked table manager to relink the tables using
UNC paths instead of drive letter assignment. Otherwise you will soon
discover that for some users drive S must be mapped to something else or
that another critical program will not work. It will also prevent stupid
"Out of DOS file handles" error messages.

Alex.

tbjohnson2 said:
I have a user group that has multiple MS Access and MS Excel Spreadsheets
Click to expand...
that have linked tables and worksheets. They were all linked on the network
by the drive name (c:\, v:\, etc) instead of the actual network server
name - the user group is currently moving network drives so all the
databases/spreadsheets will be moved from o:\ to s:\, unfortunately! Is
there an easy way to search the o:\ drive, locate each file and check to see
if there is a linked table or worksheet programmically and open the file and
update the location of the drive??? There are over 100's of files we
believe. Thanks!
 
tbjohnson2 said:
I have a user group that has multiple MS Access and MS Excel Spreadsheets that have linked tables and worksheets. They were all linked on the network by the drive name (c:\, v:\, etc) instead of the actual network server name - the user group is currently moving network drives so all the databases/spreadsheets will be moved from o:\ to s:\, unfortunately! Is there an easy way to search the o:\ drive, locate each file and check to see if there is a linked table or worksheet programmically and open the file and update the location of the drive??? There are over 100's of files we believe. Thanks!
Click to expand...


No, a file is unaware of the applications that link to it.

I don't know about Excel, but, at least in Access, you can
search the apllpication's TableDefs collection looking for
linked tables and check their path:

Dim db As DAO.Database
Dim tdf As DAO TableDef
Dim intPos As Integer
Dim strPath As String

Set db = CurrentDb()
For Each tdf in db.TableDefs
If tdf.Connect <> "" Then
intPos = InStr(tdf.Connect, ";Database=")
If intPos > 0 Then
strPath = Mid(tdf.Connect. intPos + 10)
If Left(strPath, 3) = "O:\"
Debug.Print tdf.Name, strPath
End If
End If
End If
Next tdf
Set db = Nothing
 
Back
Top