Access Refresh Links (access 2002)

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

Guest

Help,

Refreshing Text Link in VB & VBA
In my VB App or inside a VBA code how can I refreshing the table link to an
external text file without receiving the error:

Runtime Error '3625': The text file specification 'xxx' does not exist. You
can't import, export, or link using the specification.

The code I am typing in is :

For Each tbl In CurrentDb.TableDefs
If tbl.SourceTableName <> "" Then
strOldCon = tbl.Connect
For I = 1 To Len(strOldCon)
If UCase(Mid(strOldCon, I, 29)) = "\\LON0302\DFS\DATA\DTA\FMAN_Q"
Then
strNewCon = strNewCon & "Q:"
I = I + 28
Else
strNewCon = strNewCon & Mid(strOldCon, I, 1)
End If
Next
If strNewCon <> strOldCon Then
'MsgBox strOldCon & vbCrLf & strNewCon
tbl.Connect = strNewCon
tbl.RefreshLink
End If
strOldCon = ""
strNewCon = ""
End If
Next

Thanks for any help
Harp
 
Hi Harp,

It looks as if you're dumping the last part of the connection string.
Something like this air code should do the job:

Dim strOldPath As String
Dim strNewPath As String
Dim lngPos As Long

strOldPath = "\\LON0302\blah blah"
strNewPath = "Q:"

...
For Each tbl ...
lngPos = InStr(strOldPath, tbl.Connect)
If lngPos > 0 Then
tbl.Connect = _
Left(tbl.Connect, lngPos - 1) _
& strNewPath _
& Mid(tbl.Connect, lngPos + Len(strOldPath) +1)
Else
'tbl is connected to something else
End If
...
 
Back
Top