I've got a database that is stored on a network drive. In a subfolder from
the folder housing the database there are files which relate to the data
stored in the database. The interface to the data is in a separate database
file stored locally with links to the tables in the database on the network
drive. I'd like to use the location of the linked tables as a variable for
creating links to those files but I'd prefer not to hard code the path into
the VBA. Is there code that will return, from the local copy, the path of
the database on the network drive containing the table linked-to so that I
can store it in a variable for use elsewhere? In case that wasn't clear:

tables:
\\server\folder\main.mdb has tblMain
c:\localdir\local.mdb has links to main.tblMain stored as detailed above

I'd like to be able to use vba in local.mdb to return the path of main.mdb
by looking at the link info. Thanks!

Take a look at the connect property of the linked table.
For instance
?CurrentDB().TableDefs("tbl_Personnel").Connect
;DATABASE=J:\CenterDatabases\Center Project History\Data\CHDPM Projects and
Accomplishments_be.mdb

You will need to parse out the path, but that should not be too tough to do.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"ArielZusya" <(E-Mail Removed)> wrote in message
news:0F53561B-AE28-41F7-9545-(E-Mail Removed)...
> I've got a database that is stored on a network drive. In a subfolder from
> the folder housing the database there are files which relate to the data
> stored in the database. The interface to the data is in a separate
> database
> file stored locally with links to the tables in the database on the
> network
> drive. I'd like to use the location of the linked tables as a variable
> for
> creating links to those files but I'd prefer not to hard code the path
> into
> the VBA. Is there code that will return, from the local copy, the path of
> the database on the network drive containing the table linked-to so that I
> can store it in a variable for use elsewhere? In case that wasn't clear:
>
> tables:
> \\server\folder\main.mdb has tblMain
> c:\localdir\local.mdb has links to main.tblMain stored as detailed above
>
> I'd like to be able to use vba in local.mdb to return the path of main.mdb
> by looking at the link info. Thanks!

ArielZusya wrote:

>I've got a database that is stored on a network drive. In a subfolder from
>the folder housing the database there are files which relate to the data
>stored in the database. The interface to the data is in a separate database
>file stored locally with links to the tables in the database on the network
>drive. I'd like to use the location of the linked tables as a variable for
>creating links to those files but I'd prefer not to hard code the path into
>the VBA. Is there code that will return, from the local copy, the path of
>the database on the network drive containing the table linked-to so that I
>can store it in a variable for use elsewhere? In case that wasn't clear:
>
>tables:
>\\server\folder\main.mdb has tblMain
>c:\localdir\local.mdb has links to main.tblMain stored as detailed above
>
>I'd like to be able to use vba in local.mdb to return the path of main.mdb
>by looking at the link info.


You can get the apth to the back end mdb form any linked
table def object's Connect property:

path = Mid(CurrentDb.TableDefs!anylinkedtable.Connect, 11)

--
Marsh
MVP [MS Access]

Thanks for the help, John and Marshall! That's precisely what I was looking
for. Ultimately what I did was:

Dim dbsCurrent As Database
Dim strPath As String
Dim lngPathPos As Long
Dim strPathPart As String
Dim blnIncludesFile As Boolean
Dim varServerPath as String

Set dbsCurrent = CurrentDb

strPath = CurrentDb.TableDefs!tblMain.Connect
lngPos = InStrRev(strPath, "\")
blnIncludesFile = InStrRev(strPath, ".") > lngPos

If blnIncludesFile Then
strPart = Left$(strPath, lngPos)
Else
strPart = strPath
End If

varServerPath = Mid(strPart, 11)


works like a charm! Thanks again for all your help!

"John Spencer" wrote:

> Take a look at the connect property of the linked table.
> For instance
> ?CurrentDB().TableDefs("tbl_Personnel").Connect
> ;DATABASE=J:\CenterDatabases\Center Project History\Data\CHDPM Projects and
> Accomplishments_be.mdb
>
> You will need to parse out the path, but that should not be too tough to do.
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "ArielZusya" <(E-Mail Removed)> wrote in message
> news:0F53561B-AE28-41F7-9545-(E-Mail Removed)...
> > I've got a database that is stored on a network drive. In a subfolder from
> > the folder housing the database there are files which relate to the data
> > stored in the database. The interface to the data is in a separate
> > database
> > file stored locally with links to the tables in the database on the
> > network
> > drive. I'd like to use the location of the linked tables as a variable
> > for
> > creating links to those files but I'd prefer not to hard code the path
> > into
> > the VBA. Is there code that will return, from the local copy, the path of
> > the database on the network drive containing the table linked-to so that I
> > can store it in a variable for use elsewhere? In case that wasn't clear:
> >
> > tables:
> > \\server\folder\main.mdb has tblMain
> > c:\localdir\local.mdb has links to main.tblMain stored as detailed above
> >
> > I'd like to be able to use vba in local.mdb to return the path of main.mdb
> > by looking at the link info. Thanks!
>
>
>