Changing path of a linked table.

[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?

 

[Douglas J. Steele]

Check http://www.mvps.org/access/tables/tbl0009.htm for an extensive example
of how to relink tables through VBA.

 

[Marshall Barton]

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

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

 

[Guest]

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)

 

[Douglas J Steele]

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"

 

[Roger Carlson]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Douglas J Steele]

You should never have two users trying to run the same front-end
concurrently!