Getting the path of an attached db on a network

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

The following code gets the path of the current database
(which is the front end and located locally). I need to
be able to modify it such that it will refer to an
attached table database on a network.

Dim dbCurrent As Database
Dim FilePath As String

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
FilePath = dbCurrent.Name
dbCurrent.Close

Any help would be greatly appreciated!
 
The Connect property of the linked TableDef includes the location of the
database in which the linked table resides. It also includes some other
text, but should be simple to separate.

Your database may not do so, but there is no Access limitation to only
linking tables in a single other database. There can be many, if you choose.

Larry Linson
Microsoft Access MVP
 
Lisa said:
The following code gets the path of the current database
(which is the front end and located locally). I need to
be able to modify it such that it will refer to an
attached table database on a network.

Dim dbCurrent As Database
Dim FilePath As String

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
FilePath = dbCurrent.Name
dbCurrent.Close
Click to expand...


You can get the path to an attached table from the Connect
property of the tables TableDef object. For a Jet backend
database, this is all you need:

BEpath = Mid(dbCurrent.TableDefs("atttable").Connect, 11)

For other kind of backends, it takes a little more:

strConn = dbCurrent.TableDefs("atttable").Connect
intPos = InStr(strConn, ";DATABASE=")
BEpath = Mid(strConnt, intPos + 10)
 
Thanks Marsh, this works great:

BEpath = Mid(dbCurrent.TableDefs("atttable").Connect, 11)

As opposed to copying and pasting the answers, what's the
purpose of the Mid function?

It doesn't seem to work without using it, yet I don't
understand its purpose.

thanks again for the help.
 
Lisa said:
BEpath = Mid(dbCurrent.TableDefs("atttable").Connect, 11)

As opposed to copying and pasting the answers, what's the
purpose of the Mid function?

It doesn't seem to work without using it, yet I don't
understand its purpose.
Click to expand...

The Connect property contains all the information required
to process data from another database. For a Jet back end,
the Connect property is only:
;DATABASE=pathtothebackendmdbfile

so the Mid function is used to extract just the path.

As Larry mentioned in his reply, Access can link to many
other kinds of databases and the connect property would then
have additional information in it.
 
Lisa said:
The following code gets the path of the current database
(which is the front end and located locally). I need to
be able to modify it such that it will refer to an
attached table database on a network.

Dim dbCurrent As Database
Dim FilePath As String

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
FilePath = dbCurrent.Name
dbCurrent.Close

Any help would be greatly appreciated!
Click to expand...

Lisa -

Others have given you the answer to your immediate question, but I
wanted to warn you that this line:
dbCurrent.Close
Click to expand...

should be deleted. You should never attempt to close
DBEngine.Workspaces(0).Databases(0). You didn't open this database;
Access did, and Access requires it. Access is supposed to trap and
ignore any attempt to close the current database, so you may get away
with this call, but it's a bad practice to close things you didn't open.
 
Back
Top