G
Glint
Its me again, Guys.
I had been having problems with renewing connections to the back-end when
someone else uses my application in a network environment. I needed something
that was easy enough for anyone to set up. So I got this code to link the
back end tables from this forum:
Private Sub cmdLink_Click()
On Error GoTo Err_cmdLink_Click
'RelinkTables...Just as the name suggests pass a path to a database
to this sub
'eg RelinkTables("c:\windows\test.mdb")
'and it will go through all the tables in your
'database and link them to the new location
'Written by John Hawkins 20/9/99 www.fabalou.com
'Public Sub RelinkTables(NewPathname As String)
Dim CountOfTables As Integer
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
CountOfTables = 0
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is
other than a base table
Tdf.Connect = ";DATABASE=" & NewPath 'Set the new source
Tdf.RefreshLink 'Refresh the link
CountOfTables = CountOfTables + 1
End If
Next 'Goto next table
MsgBox CountOfTables & " Table(s) Linked successfuly",
vbInformation, "ECKANKAR AREA ADMIN"
DoCmd.GoToRecord acActiveDataObject, , acNewRec
OldPath.Value = NewPath
PDate = Date
DoCmd.GoToControl "Venue"
Exit_cmdLink_Click:
Exit Sub
Err_cmdLink_Click:
MsgBox err.Description
Resume Exit_cmdLink_Click
End Sub
Basically, I made this LinkForm write to a table where I keep the data of
the connection. I tested the code and it appeared to work fine. When the form
loads and the back-end file has moved, the form prompts for another link. I
supply a connection string to NewPath, and another link is made on clicking
cmdLink.
Initially, CountOfTables was accurate in that it told me 75 tables were
linked successfully. After I have moved the back-end a couple of times, I
find figures of 156, 232, etc. I also notice that most of the linked tables
are doubling (as in tblAreas, tblAreas1, tblAreas2), some with the same
paths.
How can I ensure that only the last connection is retained so that I dont
end up with hundreds (or possibly thousands at the rate it is going) of
linked tables in future? Can you suggest a neater way of going about it?
I had been having problems with renewing connections to the back-end when
someone else uses my application in a network environment. I needed something
that was easy enough for anyone to set up. So I got this code to link the
back end tables from this forum:
Private Sub cmdLink_Click()
On Error GoTo Err_cmdLink_Click
'RelinkTables...Just as the name suggests pass a path to a database
to this sub
'eg RelinkTables("c:\windows\test.mdb")
'and it will go through all the tables in your
'database and link them to the new location
'Written by John Hawkins 20/9/99 www.fabalou.com
'Public Sub RelinkTables(NewPathname As String)
Dim CountOfTables As Integer
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
CountOfTables = 0
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName <> "" Then 'If the table source is
other than a base table
Tdf.Connect = ";DATABASE=" & NewPath 'Set the new source
Tdf.RefreshLink 'Refresh the link
CountOfTables = CountOfTables + 1
End If
Next 'Goto next table
MsgBox CountOfTables & " Table(s) Linked successfuly",
vbInformation, "ECKANKAR AREA ADMIN"
DoCmd.GoToRecord acActiveDataObject, , acNewRec
OldPath.Value = NewPath
PDate = Date
DoCmd.GoToControl "Venue"
Exit_cmdLink_Click:
Exit Sub
Err_cmdLink_Click:
MsgBox err.Description
Resume Exit_cmdLink_Click
End Sub
Basically, I made this LinkForm write to a table where I keep the data of
the connection. I tested the code and it appeared to work fine. When the form
loads and the back-end file has moved, the form prompts for another link. I
supply a connection string to NewPath, and another link is made on clicking
cmdLink.
Initially, CountOfTables was accurate in that it told me 75 tables were
linked successfully. After I have moved the back-end a couple of times, I
find figures of 156, 232, etc. I also notice that most of the linked tables
are doubling (as in tblAreas, tblAreas1, tblAreas2), some with the same
paths.
How can I ensure that only the last connection is retained so that I dont
end up with hundreds (or possibly thousands at the rate it is going) of
linked tables in future? Can you suggest a neater way of going about it?