Resetting Table Links

[LarryP]

I have a procedure that resets table links depending on the site that is
using the database. Most links are to Access tables in other databases, one
is to an Excel file, one to a .txt file. For the first two everything works
fine, but for the .txt file I'm getting an invalid path error. Since the
path IS valid, and the VBA is identical except for the "Text;" type
declaration, I'm stymied as to why the error. See partial code below:


Select Case Right(!TucsonTableLink, 3)
Case "xls":
tdfLinked.Connect = "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" &
SessionFilePath & !TucsonTableLink
Case "txt":
tdfLinked.Connect = "Text;DATABASE=" & SessionFilePath &
!TucsonTableLink
Case "mdb":
tdfLinked.Connect = ";DATABASE=" & SessionFilePath &
!TucsonTableLink
End Select

........

tdfLinked.RefreshLink
#######THIS IS WHERE THE ERROR OCCURS.....########

At the point where the error occurs, the actual path looks like this:
\\apfs\projs\prodcntl_ops\Entrpris\Data\PRISM\IPT Health Tool Source
Files\qn.txt

Except for the actual file name, that exact same path is used successfully
to link numerous other tables, so I'm darned if I can see why it blows up for
this file.

(By the way, off the subject, what's the type declaration for Excel 2003?
VBA Help only goes to Excel 8.0 for Excel 97.)

 

[david]

convert the path to a 'short path', using that API,
and check that you are allowed to link to "txt" files,

Private Declare Function GetShortPathName Lib "kernel32" Alias
"GetShortPathNameA" (ByVal longPath As String, ByVal shortPath As String,
ByVal shortBufferSize As Long) As Long

http://msdn.microsoft.com/en-us/library/aa364989(VS.85).aspx



(david)

 

[LarryP]

I looked at that link and will try it, although It's a bit over my head. But
before I do, let me add that the database links to the text file without
problem via the Linked Table Manager. It's just when I try to reset the link
via VBA that I run into this problem. Given that, do you think the 'short
path' idea is still worth exploring?

 

[david]

It's worth trying:

Paste the declaration into the top of the module, and use it to convert like
this:

Dim LongPath As String

LongPath = SessionPath
NewPathLength = GetShortPathName(LongPath, SessionPath, len(SessionPath))
SessionPath = Left(SessionPath,NewPathLength)

Debug.print SessionPath

(david)