returning the path of a linked table in VBA
in [Forms]
|
Prev: Get date based on two other fields
Next: Exit ALL Subs?
From: Paul on 11 Feb 2010 20:47 Is there any way to capture the path of a linked table (say, tblContacts) in VBA? Thanks in advance, Paul
From: Allen Browne on 11 Feb 2010 21:04 "Paul" <BegoneSpam(a)forever.com> wrote in message news:OiW7FV4qKHA.4752(a)TK2MSFTNGP04.phx.gbl... > 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 - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
From: Allen Browne on 12 Feb 2010 00:24 The GetDataPath() function here will do it for you: http://allenbrowne.com/ser-53code.html#GetDataPath -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in message news:u8me$e4qKHA.3464(a)TK2MSFTNGP06.phx.gbl... > "Paul" <BegoneSpam(a)forever.com> wrote in message > news:OiW7FV4qKHA.4752(a)TK2MSFTNGP04.phx.gbl... >> 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 - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org.
From: David W. Fenton on 12 Feb 2010 12:44 "Allen Browne" <AllenBrowne(a)SeeSig.Invalid> wrote in news:#EwgoO6qKHA.4604(a)TK2MSFTNGP05.phx.gbl: > 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(). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Paul on 12 Feb 2010 14:12
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 |