Set linked tables on remote PC

[madwoman]

I have an Access database which we inherited and it has linked tables (2 database files, one with data tables and one with forms, queries etc) - the links being set to a path on our local company server. We are in the process of opening another company office in another part of the country and we want to use the same database system. I have taken a copy of the database, cleaned out all the existing data out and now need to send the blank database to the new office. Is there a way of forcing the database to reset its table links when it runs in the new location (or pre-defining the paths before it is installed)...rather than my physically having to go to the new office to use the Link Manager when in situ.
I wondered in fact whether was a way of holding the required path statement in one of the tables so that the links could be easily reset on any future installation. I do not know how to code anything like this or set it up - I am new to Access databases
Could anyone advise please.

 

[Guus2005]

try searching this forum. Lots of code and examples.

 

[DCrake]

Simple Software Solutions

Hi

There is a simple solution to your problem.

The easiest way to do this with minimun knowledge is to place a text file on the PC that has the front end on it called "WhereAmI.Txt"

In the this file place the path to the back end, such as:
J:\MyServer\MyProdFolder\MyProgData\MyProg.mdb


When the MDB opens code it to open the text file get it to read in the line using the Open file for input as #1 command


pass this value to a public variable then create a function similar to the one below.

Function RefreshLinks() As Boolean
' Refresh links to the supplied database. Return True if successful.
DoCmd.Echo True, "Refreshing table links, please wait..."
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim DataPath As String
DataPath = WhereAmI

' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
' Not a MSys type table
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
If InStr(tdf.Name, "MSys") = 0 Then
tdf.Connect = ";DATABASE=" & DataPath
End If
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
DoCmd.Echo True, "Done"

RefreshLinks = True ' Relinking complete.

End Function

Feel free to bastardize the above function to suit your needs

Then on the OpenForm Property of the first screen that opens place the code

X = Call RefreshLinks()

If X =True Then
....Ok
Else
...Not Ok
End If


So lets recap.

When access loads it opens a text file on the pc and passes the path to the back end database to a public variable in the front end. Then it could then test to see if the file actually exists in the specified location (not coded).

Next is loops through all the tables in the front end excluding all tables that are not MSys tables and tables that have not been previously linked.

It then passes the connection string (DataPath) to the link and refreshes it.

When it has finished it tests to see if an error occurred during the refresh (Tables missing etc) if an error occured then you can tell the user that there was a problem and contact SA. Otherwise open the front end as normal.

Hope this is of use.

Code MAster