Access Table Linking Problem

[Mike]

I have a multiuser access database to which I have split into fe &
be. The system refreshes the links at each log-on between the fe & be
automatically via code.

PROBLEM:

Locally it runs perfectly... in the client server setting the re-
linking is extremely slow...
DETIALS:

FE: 80MB

BE: 30MB

#of Tables... 120

time to load locally 5-10 seconds..

time to load via netword 5-7 Minutes

SubDataSheets - Set to [NONE]

Any help would be appreciated.

[Andy]

I've noticed this before. If ANY user is in the BE database, then you
attempt to relink tables on another machine to the BE, you'll notice the
extreme slowness. I believe Access needs to get locks on the tables to
refresh the links, and there is a major delay if the tables are opened by
another user (not so if no one else is in the BE database).

Couple of work-arounds:

1) When you are compiling an .mde, make sure the tables are already linked
to the same database they will be connecting to (if possible). Then when you
distribute the .mde to each machine, the program is already linked to the
proper location.

2) If # 1 is not possible, make sure to NOT relink the tables each time the
program starts, but only if you determine that relinking is necessary (see
code sample below), such as the linked table database is no longer valid,
etc.

Even with #2 above, one person at that location could launch the program to
relink to the back-end, then distribute his/her .mde to all other pc's (the
..mde would now be correctly linked). If you had the code put in that would
only relink if the current links were invalid, this would solve the issue,
as the program they save would already be linked to the proper BE.

Here's an example of a check in your relinking code where you only refresh
if the link is not valid:

(you could test one table's link and see if it's correct, and set the proper
path to variable: datapath)

If MyTable.Connect <";DATABASE=" & datapath Then
MyTable.Connect = ";DATABASE=" & datapath
MyTable.RefreshLink
End If

Hope this makes sense,

Andy
"Mike" <in**@baltworld.comwrote in message
news:41**********************************@c36g2000 prc.googlegroups.com...

>I have a multiuser access database to which I have split into fe &
be. The system refreshes the links at each log-on between the fe & be
automatically via code.

PROBLEM:

Locally it runs perfectly... in the client server setting the re-
linking is extremely slow...
DETIALS:

FE: 80MB

BE: 30MB

#of Tables... 120

time to load locally 5-10 seconds..

time to load via netword 5-7 Minutes

SubDataSheets - Set to [NONE]

Any help would be appreciated.

[Mike]

On Nov 3, 4:43*pm, "Andy" <[e-mail]:

I've noticed this before. If ANY user is in the BE database, then you
attempt to relink tables on another machine to the BE, you'll notice the
extreme slowness. I believe Access needs to get locks on the tables to
refresh the links, and there is a major delay if the tables are opened by
another user (not so if no one else is in the BE database).

Couple of work-arounds:

1) When you are compiling an .mde, make sure the tables are already linked
to the same database they will be connecting to (if possible). Then when you
distribute the .mde to each machine, the program is already linked to the
proper location.

2) If # 1 is not possible, make sure to NOT relink the tables each time the
program starts, but only if you determine that relinking is necessary (see
code sample below), such as the linked table database is no longer valid,
etc.

Even with #2 above, one person at that location could launch the program to
relink to the back-end, then distribute his/her .mde to all other pc's (the
.mde would now be correctly linked). If you had the code put in that would
only relink if the current links were invalid, this would solve the issue,
as the program they save would already be linked to the proper BE.

Here's an example of a check in your relinking code where you only refresh
if the link is not valid:

(you could test one table's link and see if it's correct, and set the proper
path to variable: datapath)

If MyTable.Connect <";DATABASE=" & datapath Then
* * * * *MyTable.Connect = ";DATABASE=" & datapath
* * * * *MyTable.RefreshLink
End If

Hope this makes sense,

Andy"Mike" <[e-mail] in message

news:41**********************************@c36g2000 prc.googlegroups.com...

I have a multiuser access database to which I have split into fe &
be. *The system refreshes the links at each log-on between the fe & be
automatically via code.

PROBLEM:

Locally it runs perfectly... *in the client server setting the re-
linking is extremely slow...

DETIALS:

FE: 80MB

BE: 30MB

#of Tables... 120

time to load locally 5-10 seconds..

time to load via netword 5-7 Minutes

SubDataSheets - Set to [NONE]

Any help would be appreciated.- Hide quoted text -

- Show quoted text -

I see your solution says "make sure to NOT relink the tables each time
the program starts"... i am confused a little about linked tables..
does the link need to be re-established each time the FE is opened or
not??? So threads I see say yes.. Some seem to say no however I cannot
get a definative answer....

THanksagain for the help..

[Andy]

No, if a table is already linked to the proper location, you should not
relink it, as this will be slow with other user's in the db. Check the
..connect property to see if it's linked to the correct place. If it is,
don't refresh the link, and the speed will be much better.

Hope this helps,

[Tony Toews [MVP]]

Mike <in**@baltworld.comwrote:

>Locally it runs perfectly... in the client server setting the re-
linking is extremely slow...

Andy's got some very valid points. No need to relink the tables if the the link to
the BE works. I just open a recordset against a one record table linked to the back
end such as my always present GlobalOptions table. If the appropriate error message
is received then I know I need to relink to the BE. If it opens cleanly then I exit
this logic.

However the real answer to your problem is to either open a database object to the BE
database before you start relinking the tables in code. Or, once you've relinked the
first table open a recordset based on that table.

Now if you want to leave the database or recordset object open for performance
reasons that's fine. Or you can open a hidden form with the record source set to a
linked table such as I do with my GlobalOptions table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/