Updating Linked Table Connection

[Guest]

I've adapted the code provided by Nikos so that at a click of the button, I
can change the link from the server at the office to my hard drive when I'm
working at home and vice versa.

However, I am using Allen Browne's audit trail tables so that some tables
need to point to a different location (tables with names starting with
AudTmp*). How do I incorporate this into the code:

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


Thanks.
ck

 

[Dirk Goldgar]

See the inserted code in your quoted message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I've adapted the code provided by Nikos so that at a click of the
button, I can change the link from the server at the office to my
hard drive when I'm working at home and vice versa.

However, I am using Allen Browne's audit trail tables so that some
tables need to point to a different location (tables with names
starting with AudTmp*). How do I incorporate this into the code:

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

If tbln Like "AudTmp*" Then
' update lnk to whatever it should be for these tables
Else
lnk = Replace(lnk, cp, np)
End If

 

[Guest]

Thanks for the super-quick response, Dirk...
ck

See the inserted code in your quoted message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



If tbln Like "AudTmp*" Then
' update lnk to whatever it should be for these tables
Else
lnk = Replace(lnk, cp, np)
End If