VBA reference to Access linked table

  • Thread starter Thread starter Dean D
  • Start date Start date
D

Dean D

Does anyone know how to reference the path in VBA to a linked table in
Access? I would like to make it available in the program so the user does
not have to use the Linked File Manager from the menu bar.
 
Dean said:
Does anyone know how to reference the path in VBA to a linked table in
Access? I would like to make it available in the program so the user does
not have to use the Linked File Manager from the menu bar.
Click to expand...


you can get the path from any linked table's TableDef
Connect property. You can then parse out the path from the
connect string. How you do the parsing depends on what kind
of file you're connecting to. It's really easy for a Jet
mdb file:

Dim db As Database
Dim tdf As TableDef
Dim strCon As String
Dim strPath As String

Set db = CurrentDb()
Set tdf = db.TableDefs("tablename")
strCon = tdf.Connect
strPath = Mid(strCon, 11)

Set tdf = Nothing
Set tdf = Nothing

But, if the link is no good and needs changing, what good is
it to get the bad path??

You may also want to look at:
http://www.mvps.org/access/tables/tbl0009.htm
and Google for other relinking procedures.
 
Why not simply get the code to check that the link is
correct and if not then bring up the open file dialog box
to allow the user to find the database to link to.

Someone had the code for this in this forum recently.

BOL
DavidC
 
Actually, I do this sort of thing frequently. It's very
desirable to have this functionality available when
rolling out a frontend/backend application to an external
customer who will need to be able to change the path to
the backend on demand.

The solution I use combines the code examples provided in
the following KB articles:

How To Use a Common Dialog File Open Dialog with Win32 API
http://support.microsoft.com/default.aspx?scid=kb;en-
us;161286

ACC2000: How to Use ADOX to Create and Refresh Linked Jet
Tables
http://support.microsoft.com/default.aspx?scid=kb;en-
us;275249&Product=acc

Basically, just replace the reference to Northwind with a
call to the function that makes use of the Common Dialog
tool above.

If you don't want to use ADO, but rather DAO:
http://www.mvps.org/access/tables/tbl0009.htm (this site
also has really good sample code for using the
comdlg32.dll api)

More info on using the Common Dialog API:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnovba00/html/CommonDialogsPartI.asp

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnima01/html/ima0187.asp

Hope that helps!

DBS
 
Back
Top