TOO MANY LINKS TO BACK-END

[Glint]

Hi Guys,
I am really sorry guys for posting this twice but I did not get a response
the first time.
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?

 

[Dirk Goldgar]

Hi Guys,
I am really sorry guys for posting this twice but I did not get a response
the first time.
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?

Is there some other code in the database that is relinking tables, or
linking new tables, or invoking the Linked Table Manager? I don't see how
the code you posted could create any new tabledefs, so I'm not convinced
that it is the source of the problem.

 

[John Spencer]

Strange.
I responded over three hours ago prior to this post and Armen Stein responded
over an hour prior to this post.

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

 

[Glint]

Thanks, Dirk.

I don't think there is another code, because I was not able to link as
easily as before I used this code. What I used to do was to specifically
request that all files reside in a folder that I have coded into the
application: C:\ECK ADMIN. That way there was never a need to link, except in
a network which I could not handle until now. But someone has pointed out
that that path may not be possible on his machine. So the need for re-linking
once the files are placed anywhere.

What I found is that the first linkage gets 75 tables. If I move the
back-end to another location and re-link, the figure becomes 156, 232 etc.
Meanwhile, the Link Manager keeps the old links in the front-end (tblArea
with path C:\ECK ADMIN, tblArea1 same path, tblArea2 same path etc).
I hope I have been able to present the problem.

 

[Dirk Goldgar]

Thanks, Dirk.

I don't think there is another code, because I was not able to link as
easily as before I used this code. What I used to do was to specifically
request that all files reside in a folder that I have coded into the
application: C:\ECK ADMIN. That way there was never a need to link, except
in
a network which I could not handle until now. But someone has pointed out
that that path may not be possible on his machine. So the need for
re-linking
once the files are placed anywhere.

What I found is that the first linkage gets 75 tables. If I move the
back-end to another location and re-link, the figure becomes 156, 232 etc.
Meanwhile, the Link Manager keeps the old links in the front-end (tblArea
with path C:\ECK ADMIN, tblArea1 same path, tblArea2 same path etc).

I'm puzzled because there is nothing in that code that create a new
tabledef, nor changes the name of a tabledef. Furthermore, when I test that
code in my own database, it does not have the effect you report.

When you "move the back-end to another location and re-link", what exactly
do you do to relink? Just open this form and click the cmdLink button?

Do you have full permissions on the database?

 

[Glint]

Thanks again, Dirk.
I set the LinkForm as the starting form and on load, if the string in
OldPath (which is the last connection used) does not match the connection
string, then the form stays open for me to update and run the code through
the cmdLink button.

If Len(Dir(OldPath)) > 0 Then
DoCmd.OpenForm "Open Sesame"
DoCmd.Close acForm, "LinkForm"
Else
MsgBox "Enter New Link", vbInformation, "ECKANKAR AREA ADMIN"
End If

 

[Glint]

I am sure I don't know what I am doing, but I changed this line:
If Tdf.SourceTableName <> "" Then 'If the table source is
other than a base table

to this one that I also found going through the forum:
' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then

and it appeared to work. There are no duplicate links anymore. I need to
find out the difference between a tdf SourceTableName property and its
Connect property.

I am grateful for your assistance.

 

[Dirk Goldgar]

Thanks again, Dirk.

You're welcome, though we don't seem to be making much progress.

I set the LinkForm as the starting form and on load, if the string in
OldPath (which is the last connection used) does not match the connection
string, then the form stays open for me to update and run the code through
the cmdLink button.

If Len(Dir(OldPath)) > 0 Then
DoCmd.OpenForm "Open Sesame"
DoCmd.Close acForm, "LinkForm"
Else
MsgBox "Enter New Link", vbInformation, "ECKANKAR AREA ADMIN"
End If

Well, I'm still puzzled. Is this database one that you could zip up and
send to me? If so, I'll take a look at it, time permitting. You can send
it to the address derived by removing NO SPAM and ".invalid" from the reply
address of this message. If that address isn't visible to you, you can get
my address from my web site, which is listed in my sig. Do *not* post my
real address in the newsgroup -- I don't want to be buried in spam and
viruses. Please compact it before zipping it, and send me the compressed
zip archive.

 

[Glint]

Ok. I will get a zipping utility and send it (with the back end).

 

[carlos gouveia]

Hi Guys,
I am really sorry guys for posting this twice but I did not get a response
the first time.
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?

 

[Glint]

Sorry Carlos, but I did not see your post.

 

[Dirk Goldgar]

Ok. I will get a zipping utility and send it (with the back end).

I've received and tested the database you sent me, both with and without the
fix you posted in your subsequent message: changing your test from
Tdf.SourceTableName to Tdf.Connect. But I haven't been able to get it to
misbehave in the way you describe. When I move the back-end around and
force a relink, the number of tables linked is the same every time, and no
new linked tables are produced.

The version of the database you sent me appears to be the one in which you
made your fix. Was there anything else you did in making that fix besides
that simple code change?

 

[Glint]

First, Dirk, let me express my deep gratitude for your effort. I really
appreciate it.

To answer your question, No, I did not do anything else apart from the
simple code change. And I am surprised the effect of new links were not
reproduced.
The old code produced this effect on my tabletop which runs WinXPll, Access
2002 with Access 2007 Runtime. My laptop is also WinXPlll with Access 2007.

 

[Dirk Goldgar]

First, Dirk, let me express my deep gratitude for your effort. I really
appreciate it.

You're welcome. I wish I could have reproduced the problem.

To answer your question, No, I did not do anything else apart from the
simple code change. And I am surprised the effect of new links were not
reproduced.
The old code produced this effect on my tabletop which runs WinXPll,
Access
2002 with Access 2007 Runtime. My laptop is also WinXPlll with Access
2007.

And the problem occurs on both systems? It makes me think there's something
in the OS or permissions configuration that is involved here, but I can't
think what that would be. I'm testing with full Access 2007 under Vista.
As I said, there's nothing in the code I've seen that *should* be causing
the creation of new TableDefs, which appears to be what was going on. I
even searched your database for all occurrences of the string "TableDef",
and found nothing out of the ordinary.

I hate unresolved puzzles like this, but if it's working now and we can't
reproduce it, I guess we'll just have to leave it alone. Your fix -- using
the .Connect property instead of the .SourceTableName property -- is the way
I would have done it in the first place, so it's not anything odd.

 

[Glint]

Thanks again, Dirk. I will take your advice to let sleeping dogs lie for now.
If and when the problems shows up again, I will holler.