TOO MANY BACK END LINKS


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

Advertisements

M

Mr. B

Glint,

You would only see table naming like you describe when you have created a
second link to the same tables.

You do not need nor should you do this.

You simply need to refresh the existing link. There is a big difference.
There are several topics on this subject. I suggest that you do a search on
this forum and/or do a Google search for more information or refreshing the
links to linked tables.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top