Link Table Manager

C

CK

I'm helping someone with their access database and they have split the
database into FE and BE. The BE is on a server. The problem is that when I
made a copy of the FE and BE and bring it home to modify the forms, I have to
change the links of the tables to my hard drive. Then when I copied the FE
back to the server, I have to update the links to point to the server again.
Is there an easier way to do this? Thanks.
ck
 
N

Nikos Yannacopoulos

I have a similar situation, and have put together the following piece of
code to change my links in seconds, with one move. The trick is that my
Windows user name is differect in each location, so checking it the code can
determine where it's running. Just make sure you put in the correct (office)
username and the correct path for both sites.

Function change_links()
Dim db As Database
Dim tbl As TableDef
Dim cp As String 'current path
Dim np As String 'new path
Dim lnk As String 'link string

Set db = CurrentDb()
usr = Environ("UserName")
If usr = "OfficeUserName" Then
cp = "C:\HomePath\"
np = "\\ServerName\OfficePath\"
Else
cp = "\\ServerName\OfficePath\"
np = "C:\HomePath\"
End If

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, cp, np)
tbl.Connect = ""
tbl.Connect = lnk
tbl.RefreshLink
End If
Next
End Function

HTH,
Nikos
 
Top