Multiuser Interface on shared servicer with SQL Backend

[JR_06062005]

I have an Access application sitting on a shared server. The data tables
site on an SQL server and are linked to the Access application. There are
about 50 users. It seems that when the application is in heavy use, users
have a difficult time getting into the application. They get the message
that the application or database has been locked by the administrator. I am
the adminstrator and most of the time when a user gets this message I am not
even in the application. In fact some user will be identified as locking the
database. A fix I've recommended for those locked out is to log off their
PCs and log back on again. Most of the time this works, but not all the time.

My theory is that Access has reached it's capacity for number of users for
the application as configured/written and will not let in additional users.
I thought that having the data actually on the SQL server rather in Access
would help, but it appears not enough. Any suggestions to solve this
problem will be appreciated.

 

[NetworkTrade]

do all the users have their own front end??

 

[Tony Toews [MVP]]

I have an Access application sitting on a shared server. The data tables
site on an SQL server and are linked to the Access application.

Actually it's the other way around. Access is linked to the SQL
Server tables.

They get the message
that the application or database has been locked by the administrator. I am
the adminstrator and most of the time when a user gets this message I am not
even in the application.

You should be giving each user thier own copy of the FE>

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for reasons why including
the message you are seeing. See the Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

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/

 

[JR_06062005]

If you are asking if the database has been split, the answer is No. Do you
think this would help?

 

[Keith Wilby]

If you are asking if the database has been split, the answer is No. Do
you
think this would help?

It's already split by virtue of the fact that it has a FE & BE. Give each
user their own copy of the FE.

Keith.
www.keithwilby.com

 

[JR_06062005]

There is one copy of an mde file on a server. The tables are linked to an
SQL server, but there is only one mde file. So are you saying that because
the tables are sitting on the SQL server, the file is already split? If so,
then I'm not sure what you mean by give each user a copy of the FE. Each
user has Access 2003. I guess I don't know how to do what you are suggesting.

 

[JR_06062005]

What I had been doing was letting the users link to single front end on a
server. I tried what you suggestesd and actually gave some users a copy of
the FE to put on their desktop. The same error occurred. I am thinking that
the problem may be with the way the data is setup on the SQL server rather
than Access?

 

[Tony Toews [MVP]]

What I had been doing was letting the users link to single front end on a
server. I tried what you suggestesd and actually gave some users a copy of
the FE to put on their desktop. The same error occurred. I am thinking that
the problem may be with the way the data is setup on the SQL server rather
than Access?

This sounds much more like an Access problem on the FE rather than a
SQL Server problem.

Giving *all* users their own copy of the FE MDB/MDE is the highly
recommended practice. If only some users then you're no better off.

Now if you get this message again double check that there is no LDB
file present in the same folder as the FE MDB/MDE with the same name.
This would then mean that someone snuck into the server and is sharing
the MDB/MDE.

If you still get this message then please post back. Also what is the
exact message?

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/

 

[Keith Wilby]

There is one copy of an mde file on a server. The tables are linked to an
SQL server, but there is only one mde file. So are you saying that
because
the tables are sitting on the SQL server, the file is already split? If
so,
then I'm not sure what you mean by give each user a copy of the FE. Each
user has Access 2003. I guess I don't know how to do what you are
suggesting.

The front end file is the mde file. Each user should have their own copy of
it, ideally on their local drive.

Keith.

 

[JR_06062005]

I can see where each user having his/her own copy of the mde file on his
computer would speed up things, especially loading forms. In fact I recently
had a case where a form was so slow in loading that it really was useless for
the end user. However, the error that users who persistently cannot get in
is the one below:
____________
You do not have exclusive access to the database at this time. If you
proceed, you may not be able to save them later.

Details
When attempting to open a database object in Design view, you may encounter
this error message. This occurs because Access requires an exclusive to the
database for some object types since other users may attemp to use the
objects. If you change the name of a table , or its definition of the fields
while someone has the table open, this will result in a serious error for the
user. Therefore, you must acquire sole access to the database while you
attempt to update the object. When you release control of the object, other
users will be allowed to use the database (release your exclusive lock).
_____________

There is an OK button at the bottom of the message, but clicking it only
places the user in an endless loop. The message come back again and again.
The only way to break the loop is with the Microsoft Windows Task Manager.
This only affects 3 or 4 of the 55 or so users, but it creates a serious
problem.

 

[JR_06062005]

I'm still getting the error messages. The less serious one is below:

The database has been placed in a state by user 'Admin' on machine [computer
name] that prevents it from being opened or locked.

Usually after a few minutes, the users who get this message can try again
and get in. Most of the 55 or so users have gotten this message at one time
or another. It's aggravating, but not catastrophic. The second error
message only affects 3 or 4 of the users, but is catastrophic in that they
cannot get into the database at all. In fact it puts the user in an endless
loop. The user is presented an OK button. Clicking this button only makes
the message reappear. The only way for the user to get out of this loop is
to use the Microsoft Windows Task Manager. The error message is below:

You do not have exclusive access to the database at this time. If you
proceed, you may not be able to save them later.

Details
When attempting to open a database object in Design view, you may encounter
this error message. This occurs because Access requires an exclusive to the
database for some object types since other users may attemp to use the
objects. If you change the name of a table , or its definition of the fields
while someone has the table open, this will result in a serious error for the
user. Therefore, you must acquire sole access to the database while you
attempt to update the object. When you release control of the object, other
users will be allowed to use the database (release your exclusive lock).

 

[Armen Stein]

The only way I can see these particular errors happening is if somehow
users are sharing the same front-end.

Make absolutely sure that each user is the ONLY one using that
front-end, and that they have only ONE intance of it open on their pc.
I agree with Tony that this doesn't look like a SQL Server issue at
all.

I'm still getting the error messages. The less serious one is below:

The database has been placed in a state by user 'Admin' on machine [computer
name] that prevents it from being opened or locked.

Usually after a few minutes, the users who get this message can try again
and get in. Most of the 55 or so users have gotten this message at one time
or another. It's aggravating, but not catastrophic. The second error
message only affects 3 or 4 of the users, but is catastrophic in that they
cannot get into the database at all. In fact it puts the user in an endless
loop. The user is presented an OK button. Clicking this button only makes
the message reappear. The only way for the user to get out of this loop is
to use the Microsoft Windows Task Manager. The error message is below:

You do not have exclusive access to the database at this time. If you
proceed, you may not be able to save them later.

Details
When attempting to open a database object in Design view, you may encounter
this error message. This occurs because Access requires an exclusive to the
database for some object types since other users may attemp to use the
objects. If you change the name of a table , or its definition of the fields
while someone has the table open, this will result in a serious error for the
user. Therefore, you must acquire sole access to the database while you
attempt to update the object. When you release control of the object, other
users will be allowed to use the database (release your exclusive lock).

This sounds much more like an Access problem on the FE rather than a
SQL Server problem.

Giving *all* users their own copy of the FE MDB/MDE is the highly
recommended practice. If only some users then you're no better off.

Now if you get this message again double check that there is no LDB
file present in the same folder as the FE MDB/MDE with the same name.
This would then mean that someone snuck into the server and is sharing
the MDB/MDE.

If you still get this message then please post back. Also what is the
exact message?

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/

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[JR_06062005]

I tried your solution and it worked. Several people in this post have been
telling me the same thing and it took a while for it to sink in. (Actually
some else was telling me something else. But you guys in this group were
right). Thanks for the help.

The only way I can see these particular errors happening is if somehow
users are sharing the same front-end.

Make absolutely sure that each user is the ONLY one using that
front-end, and that they have only ONE intance of it open on their pc.
I agree with Tony that this doesn't look like a SQL Server issue at
all.

I'm still getting the error messages. The less serious one is below:

The database has been placed in a state by user 'Admin' on machine [computer
name] that prevents it from being opened or locked.

Usually after a few minutes, the users who get this message can try again
and get in. Most of the 55 or so users have gotten this message at one time
or another. It's aggravating, but not catastrophic. The second error
message only affects 3 or 4 of the users, but is catastrophic in that they
cannot get into the database at all. In fact it puts the user in an endless
loop. The user is presented an OK button. Clicking this button only makes
the message reappear. The only way for the user to get out of this loop is
to use the Microsoft Windows Task Manager. The error message is below:

You do not have exclusive access to the database at this time. If you
proceed, you may not be able to save them later.

Details
When attempting to open a database object in Design view, you may encounter
this error message. This occurs because Access requires an exclusive to the
database for some object types since other users may attemp to use the
objects. If you change the name of a table , or its definition of the fields
while someone has the table open, this will result in a serious error for the
user. Therefore, you must acquire sole access to the database while you
attempt to update the object. When you release control of the object, other
users will be allowed to use the database (release your exclusive lock).

What I had been doing was letting the users link to single front end on a
server. I tried what you suggestesd and actually gave some users a copy of
the FE to put on their desktop. The same error occurred. I am thinking that
the problem may be with the way the data is setup on the SQL server rather
than Access?

This sounds much more like an Access problem on the FE rather than a
SQL Server problem.

Giving *all* users their own copy of the FE MDB/MDE is the highly
recommended practice. If only some users then you're no better off.

Now if you get this message again double check that there is no LDB
file present in the same folder as the FE MDB/MDE with the same name.
This would then mean that someone snuck into the server and is sharing
the MDB/MDE.

If you still get this message then please post back. Also what is the
exact message?

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/

Armen Stein
Microsoft Access MVP
www.JStreetTech.com