determining directory of linked file and using path as variable in

  • Thread starter Thread starter ArielZusya
  • Start date Start date
A

ArielZusya

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 said:
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.
Click to expand...


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)
 
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!
 
Back
Top