Access Refresh Links (access 2002)

[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

 

[John Nurick]

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
...