TOO MANY BACK-END LINKS

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

 

[xixi]

²»¶®

 

[John Spencer]

It appears that your code is creating NEW Links instead of resetting the
current links. Here are a couple of alternate solutions that should work for
you to actually reset the existing links.

Armen Stein J-Street Solution

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

David Fenton
http://dfenton.com/DFA/download/Access/Reconnect.html

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Armen Stein]

It appears that your code is creating NEW Links instead of resetting the
current links. Here are a couple of alternate solutions that should work for
you to actually reset the existing links.

Armen Stein J-Street Solution

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

Thanks John. Glint, it's the file called J Street Access Relinker.

Our free relinker handles multiple back-end Access databases, and
definitely doesn't double up on the links each time. <s>

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

Thanks John. Glint, it's the file called J Street Access
Relinker.

Our free relinker handles multiple back-end Access databases, and
definitely doesn't double up on the links each time. <s>

Mine handles multiple back ends, too. That's the whole point of its
existence.

http://dfenton.com/DFA/download/Access/Reconnect.html