returning the path of a linked table in VBA

[Paul]

Is there any way to capture the path of a linked table (say, tblContacts) in
VBA?

Thanks in advance,

Paul

 

[Allen Browne]

Is there any way to capture the path of a linked table (say, tblContacts)
in VBA?

Parse it from the Connect property of the TableDef, e.g.:
CurrentDb.TableDefs("tblContacts").Connect

 

[Allen Browne]

The GetDataPath() function here will do it for you:
http://allenbrowne.com/ser-53code.html#GetDataPath

 

[David W. Fenton]

The GetDataPath() function here will do it for you:
http://allenbrowne.com/ser-53code.html#GetDataPath

That's awfully convoluted, don't you think? This is a lot simpler,
seems to me:

Mid(CurrentDB.TableDefs("MyLinkedTable").Connect, 11)

It returns a zero-length string when there's no connect string. For
non-Jet linked tables, it will return an incorrect result, so you
could do the check for this first:

Left(CurrentDB.TableDefs("MyLinkedTable").Connect, 10) =
";DATABASE="

I see that your version works for linked Jet/ACE tables and text
files, the connect strings of which both terminate with
";DATABASE=Filename/path". If you don't care about text files, this
would work:

Dim strConnect As String

strConnect = CurrentDb.TableDefs(strTable).Connect
If Left(strConnect, 10) = ";DATABASE=" Then
GetDataPath = Mid(strConnect, 11)
End If

If you want to handle as many connection types as possible without
doing anything special, and want to avoid Split() (so it works in
A97 without supplying a custom replacement for Split()), this would
work:

Dim strConnect As String
Dim lngLocation As String

strConnect = CurrentDb.TableDefs(strTable).Connect
lngLocation = InStr(strConnect,";DATABASE=")
If lngLocation <> 0 Then
GetDataPath = Mid(strConnect, lngLocation + 10)
End If

This will work in all the same situations as your original code and
has no dependency on Split().

 

[Paul]

Nice functions in there, Allen. I can use the GetDataPath() function to get
the path of the linked table, but I can also use some of those other
functions in there to do other things like return the version of the
database and the location of the msaccess.exe file.

More treasures from your trove.

Thanks much.

Paul

 

[Allen Browne]

The Connect string contains several elements, e.g. it may contain user name
and password info. It seems inappropriate to go displaying that kind of
security info in the interface, hence the suggestion to Split the items and
get just the one you want.

 

[Allen Browne]

The functions for the Splash utility at:
http://allenbrowne.com/ser-53code.html
do return the version of MSACCESS and of JET, and the sample database shows
how to use them.

Not sure if what you mean by the version of the 'database'.

Simplest way to get the location of the current version of Access is:
SysCmd(acSysCmdAccessDir)

 

[Paul]

By the version of the database I was referring to the version of JET.

Thanks for the additional tip on using the SysCmd() function, Allen.

 

[David W. Fenton]

The Connect string contains several elements, e.g. it may contain
user name and password info. It seems inappropriate to go
displaying that kind of security info in the interface, hence the
suggestion to Split the items and get just the one you want.

Your code only works with the linked table formats that store
path/filename, and none of those have any extra parameters (so far
as I saw in my testing). Thus, for the cases where your code works,
my alternative also works. But my alternative is substantially
simpler.

 

[Allen Browne]

By the version of the database I was referring to the version of JET.

Ah, okay. Hopefully you found the JET version function on the same page:
http://allenbrowne.com/ser-53code.html#GetJetVersion

 

[Paul]

I did. Thanks, Allen.

Ah, okay. Hopefully you found the JET version function on the same page:
http://allenbrowne.com/ser-53code.html#GetJetVersion