TableDef.Connect retuns no full path (97)

Topic

New Reply

Valuable members of this board,
does tabledef’s Connect property always return a 8-char-abbreviated path (but the file name in full)? How do you get a full path? For example…
For this backend path:
…so_ClusterDatabankenWater_SubsLongBackendName.mdb
the connect property yields:
…SO~B1WY7WA~B21S5LongBackendName.mdb
I tried a couple of different approaches, got for a short while some wacko results which fortunately disappeared but still, with my Access 97 ‘stabilised’ again, I can’t get it right. Some searches didn’t provide any solution & I can’t motivate myself to further examine that flood of threads, FAQ’s & articles about using VBA to retrieve a linked table’s source file, as this is so specific…

Reply | Quote

Replies

I don’t remember how it was in Access 97, and whether it depends on the Operating System. In Access 2002 on Windows XP, the Connect property stores the long path and file name.

You can use the GetFullPathName API function to retrieve the long path name belonging to a short one.

Reply | Quote

(Edited by hasse on 24-Nov-04 20:45. Applied code formatting to Code line)

Hans,
I guess it’s the Office version (97) and not the Operating System (NT), and that ‘back then’ the Connect property stored no full path yet: on the same site you refer to, I bumped in PromptFileName(), another API-function which yields the actual file’s full path using from the Open File Dialog (no add-ins or whatever needed – might be a good start for a workaround, if ever…). I assigned the resulting ‘fresh’ full path to the Connect property, retrieved the property’s value immediately afterwards and… found the full path ‘lost’ again:

Public Sub test()
MsgBox "Original MyTable.Connect = " & GetLinkedDBName("MyTable") '=> abbreviated path
CurrentDb.TableDefs("MyTable").Connect = PromptFileName()  
                               '=> Function includes added Msgbox yielding full path
MsgBox "New MyTable.Connect = " & GetLinkedDBName("MyTable") 'abbreviated path (again)
End Sub

(Or am I misinterpreting this?)

Also using your suggestion, I get the same result: no full path. I might have made a mistake though, as I’m no ‘educated’ programmer (not speaking of API). So… did I do it correctly like this…?
I created a new function (GetFullLinkedDBName) in which I ‘feed’ the path string yielded by the original function (GetLinkedDBName) as a variable to the API-function (GetFullPathName):

GetFullLinkedDBName  = GetFullPathName(GetLinkedDBName(TableName), 255, Buffer, "")

Attachment: complete code used, with ‘test’ function for try-out (replacing “MyTable” with…)
If my code is OK, the problem remains unsolved (but is was certainly a nice try, so still ). Then, I will ask no further, as I have to put this aside for higher priorities… This swallowed too much of my time already.
But if not, please let me know the correct application of the function – then I’ll give it a last try.

Reply | Quote

If Access 97 stores a “short path” in the Connect property, no API function is going to change that – even if you feed it a “full path”, Access will convert it to a “short path”. I meant that if you ever needed to reconstruct the “full path” from the abbreviated version, you could use an API function.

Reply | Quote

Sorry for not making myself clear…
Hans, I think I got your point, even though I needed that other API-function to make me realise this.

Actually, my question was: why does this API-function fail in my code? As I can’t find what might have escaped me… (cf. previous post): the custom GetLinkedDBName function yields the “short path” from the Connect property value. This value is passed to the GetFullPathName API-function, which should reconstruct the “full path” from that “short path”. But… it doesnt .

Reply | Quote

I’m sorry, I hadn’t actually tested it myself

You will find a working function fGetLongName (I tested it this time) in API: Get Short and Long file names on the Access Web.

Reply | Quote

All’s well that ends well :-)… these functions did the job!

Reply | Quote

All’s well that ends well :-)… these functions did the job!

Reply | Quote

I’m sorry, I hadn’t actually tested it myself

You will find a working function fGetLongName (I tested it this time) in API: Get Short and Long file names on the Access Web.

Reply | Quote

Sorry for not making myself clear…
Hans, I think I got your point, even though I needed that other API-function to make me realise this.

Actually, my question was: why does this API-function fail in my code? As I can’t find what might have escaped me… (cf. previous post): the custom GetLinkedDBName function yields the “short path” from the Connect property value. This value is passed to the GetFullPathName API-function, which should reconstruct the “full path” from that “short path”. But… it doesnt .

Reply | Quote

If Access 97 stores a “short path” in the Connect property, no API function is going to change that – even if you feed it a “full path”, Access will convert it to a “short path”. I meant that if you ever needed to reconstruct the “full path” from the abbreviated version, you could use an API function.

Reply | Quote

(Edited by hasse on 24-Nov-04 20:45. Applied code formatting to Code line)

Hans,
I guess it’s the Office version (97) and not the Operating System (NT), and that ‘back then’ the Connect property stored no full path yet: on the same site you refer to, I bumped in PromptFileName(), another API-function which yields the actual file’s full path using from the Open File Dialog (no add-ins or whatever needed – might be a good start for a workaround, if ever…). I assigned the resulting ‘fresh’ full path to the Connect property, retrieved the property’s value immediately afterwards and… found the full path ‘lost’ again:

Public Sub test()
MsgBox "Original MyTable.Connect = " & GetLinkedDBName("MyTable") '=> abbreviated path
CurrentDb.TableDefs("MyTable").Connect = PromptFileName()  
                               '=> Function includes added Msgbox yielding full path
MsgBox "New MyTable.Connect = " & GetLinkedDBName("MyTable") 'abbreviated path (again)
End Sub

(Or am I misinterpreting this?)

Also using your suggestion, I get the same result: no full path. I might have made a mistake though, as I’m no ‘educated’ programmer (not speaking of API). So… did I do it correctly like this…?
I created a new function (GetFullLinkedDBName) in which I ‘feed’ the path string yielded by the original function (GetLinkedDBName) as a variable to the API-function (GetFullPathName):

GetFullLinkedDBName  = GetFullPathName(GetLinkedDBName(TableName), 255, Buffer, "")

Attachment: complete code used, with ‘test’ function for try-out (replacing “MyTable” with…)
If my code is OK, the problem remains unsolved (but is was certainly a nice try, so still ). Then, I will ask no further, as I have to put this aside for higher priorities… This swallowed too much of my time already.
But if not, please let me know the correct application of the function – then I’ll give it a last try.

Reply | Quote

I don’t remember how it was in Access 97, and whether it depends on the Operating System. In Access 2002 on Windows XP, the Connect property stores the long path and file name.

You can use the GetFullPathName API function to retrieve the long path name belonging to a short one.

Reply | Quote