Access 2000 with SQL Server - MultiUsers

  • Thread starter Thread starter Rhovey
  • Start date Start date
R

Rhovey

I have an Access 2000 front-end running on 25 computers
which has ODBC links to two different databases on a SQL
Server 2000 back-end. At greater than 20 users, the
application freezes up on all the client computers for 2-3
minutes. What are the concurrency issues with Access/SQL?
I have it set on record locking, not page, and no one
needs to udpate the same record at the same time, but they
do hit the same tables at the same time. Any suggestions
for keeping our application running smoothly? We need to
get up to 50 users very quickly.
 
Rhovey said:
I have an Access 2000 front-end running on 25 computers
which has ODBC links to two different databases on a SQL
Server 2000 back-end. At greater than 20 users, the
application freezes up on all the client computers for 2-3
minutes. What are the concurrency issues with Access/SQL?
I have it set on record locking, not page, and no one
needs to udpate the same record at the same time, but they
do hit the same tables at the same time. Any suggestions
for keeping our application running smoothly? We need to
get up to 50 users very quickly.
Click to expand...

I currently help support a multi-user application deployed in a similar way
but using linked tables in Access 97 and SQL 7 via ODBC. We regularly
experience similar freezing issues that we have never got to the bottom of.
We have also found that with >30 users the application becomes unusable.

I envisage that we will eventually move over to VB as we also have a
requirement to increase the number of concurrent users.

Sorry I can't offer any help but I thought I would let you know that someone
else is experiencing similar difficulties!

Chris
 
Rhovey said:
I have an Access 2000 front-end running on 25 computers
which has ODBC links to two different databases on a SQL
Server 2000 back-end. At greater than 20 users, the
application freezes up on all the client computers for 2-3
minutes. What are the concurrency issues with Access/SQL?
Click to expand...

Does each user have their own copy of the FE? This is very likely
your problem.

However you really want to put the FE on each machine or place in a
user specific directory on the server. This will help avoid some
weird error messages when users are changing the same forms record
source, filters and such as well as corruptions.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

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
 
Chris Howarth said:
I currently help support a multi-user application deployed in a similar way
but using linked tables in Access 97 and SQL 7 via ODBC. We regularly
experience similar freezing issues that we have never got to the bottom of.
We have also found that with >30 users the application becomes unusable.
Click to expand...

This is happening even with giving each user their own copy of the FE?

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
 
Hi, Rhovey.

Dose Access is only provide form for user input data just like the form
which created by Visual Basic?

Dose everyone have different "userid" to logon on Server? Dose Access
Form use Ado to aceess data to Sql server?

Your's difficulties I think is only the Locks on the Sql server Table.
And I think the way is the user "logon off" when front application (Access)
does not access data to Sqlserver ,it must script code in the Access
application.


Loadhigh
From China
 
Rhovey,

If you use linked tables then you still use the Jet database engine and it
does not understand record locking scheme and locks the whole page anyway.
If you upgrade to an ADP project, that might help.
Other replies also have good points.

Alex.
 
Every machine does have its own version of the front end.
One thing we noticed is that after we stopped and
restarted the SQL server, performance improved for a
while - we were able to have 26 people working
simultaneously. One theory we now have is the connections
opening and closing are clogging everything. We read to
open a connection to an empty table when the initial form
opens and hold that connection open. We're going to
deploy that solution today and see what happens. I'll let
y'all know if it helps.
 
I tried upgrading to an ADP project, but couldn't figure
out how to connect different tables to the two different
databases. Any advice?
 
I've run into your problem in the past. If I'm using Access 2000 I've found
that the front end needs to be on the user's machine when more than 15
people are active. I believe this relates to multiple users sharing and
changing the same front end. The probablity of something becoming corrupted
increases dramatically. If I'm using Visual Basic as the front end I've run
at least three times that number connecting to the same front end on the
server. However the drawback is more active connections to the server, more
locks being placed on the server and a lot of network traffic. There is a
limit to the number of connections a server can handle. So the conclusion
I've come to, if at all possible place the front end on the user's machine.
As far as the backend goes I've run 50 to 200 people updating a MS Access 97
and 2000 backend. The only way you can do this is similar to MTS. Connect,
Update, then disconnect from the backend. Ado's disconnected recordset works
great for this. I've applied the same principals to an Oracle backend.
According to all the things I've read, using Access to link to an Oracle
database is very inefficient. Jet doesn't disconnect you from the database
right away. (I believe one of the other people addressed this issue.).
Incidentally I've found better performance in Access by not setting the
recordsource with your sql string until after the form has opened up. This
was another source of the machine sitting for a couple minutes everytime I
would open a form up in design view. Doing it this way potentially can speed
things up.

Well try some of these ideas we've given you and let us know if it's working
better.

Art.
 
The front-end has been on the client machines from the
beginning, so that hasn't been the issue. Could you
explain the following in a little more detail? I'm not
familiar with MTS.

"The only way you can do this is similar to MTS. Connect,
 
In Access project, I think, you can create ADODB.Connection object in VBA
code to connect to other database on the same SQL Server, or different SQL
Server, by

Dim cn AS ADODB.Connection
Set cn =New ADODB,Connection
cn.Open cnString 'cnString specify connection credential to the other
database
'Data access here
cn.Close
Set cn=Nothing

Of course, the CurrentProject.Connection is still there that connected to
the *.adp file's default database.

I think you can even change CurrentProject.Connection to connect to other
database programmatically.

From what I saw from your original post, I'd choose Access Project instead
of linked table through ODBC, at least there is one data access layer less
if you compare SQL OLE DB provider and ODBC.

I am not sure whether up to 50 users holding a connection to the SL Server,
when using the Access front end (be it ODBC linked tables or *.adp) , is too
stressful to the SQL Server (depending on how the data is queried,
pessimistic or optimistic, and the hardware condition). If keeping everyone
connected prove to be a problem, you may have consider to implement a "not
opening connection until really needed and closing connection ASAP"
approach. But I'd bet 50 users would be OK if the hardware is good and data
queries/table index and so on are designed well.
 
Jonny Smith said:
Incidentally I've found better performance in Access by not setting the
recordsource with your sql string until after the form has opened up. This
was another source of the machine sitting for a couple minutes everytime I
would open a form up in design view. Doing it this way potentially can speed
things up.
Click to expand...

I suspect keeping a table view open, or a hidden form, at all times in
the background would also have solved this problem while working in
design view.

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
 
Back
Top