Changing path of a linked table.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb

How would I do this?
 
Josh said:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb
Click to expand...


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing
 
Doug
I have a similar problem with Access 2000, however, all I really want to be
able to do is to have the front end linked to tables that are in the same
location as the front end. I have a database that is to be accessed by
various people over a network and using VPN, and they will all have mapped
the database location to a different drive letter. For whatever reason it
seems it is not possible to simply specify the link as a relative location,
and as far as I know the linked table manager will not accept a UNC path
either. This must be a very common requirement, but I don't seem to be able
to find a simple solution. (Presumably one solution is to have a single back
end but everyone has their own copy of the front end - is this the
recommended solution? The main problem with that is maintence upgrades)
 
To use a UNC with the linked table manager, navigate to the back-end table
through the Network Neighborhood, rather than through a mapped drive.

To automatically map the front-end to a back-end contained in the same
folder, replace this section of code in the module to which I pointed you:

strMsg = "Do you wish to specify a different path for the Access
Tables?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") =
vbYes Then
strNewPath = fGetMDBName("Please select a new datasource")
Else
strNewPath = vbNullString
End If

to set strNewPath to what you expect:

strNewPath = Application.CurrentProject.Path & "\MyBackendDatabase.mdb"
 
It is certainly possible to specify a UNC in the Linked Table Manager. When
you get the Select New Location dialog, click the Look In drop-down and
choose Network Places. Then browse to the server and file you want.

The recommended solution IS, however, to have individual Front-Ends on each
workstation liked to a common Back-End. Keeping Front-Ends updated is a
problem here, but there are several solutions available. Here's one:
http://www.granite.ab.ca/access/autofe.htm

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks for that, I'll have a look at both those solutions. The UNC one might
be the best. I'm wondering what will happen with a front-end that
automatically remaps itself if two users try to run it both at the same time.
 
Thanks Roger. Yes, having separate front ends may well be the best solution.
I'm also concerned about speed - since posting I have tried running a local
front-end against a VPNed back-end and it is horrendously slow (over ADSL). I
haven't tried running the remote front-end against it's own back-end yet - I
don't know if it would be any faster.
 
Back
Top