Exclusive lock (2000)

Topic

New Reply

We have a multiuser database, which is split into a front end app and back end data.
Workgroup security is used (although everyone uses the same username to log)

We are using a thin client setup (Citrix) and all users are opening the same copy of the front end app database (previously a copy of this would have been placed on each users computer – but they don’t have computers anymore!)

Users are continually locked out with the following message which I have looked up on Microsoft’s Knowledgebase:

ACC2000: “The Database Has Been Placed in a State by User…” Error When You Open a Database Containing a Form That Has a Subform (Q248967)
SYMPTOMS
When a form is open in a database, and if that form has a control on it that references a subform control, when you try to open a second instance of the database, you may receive the following error message:
The database has been placed in a state by user ‘ username ‘ on machine ‘ machinename ‘ that prevents it from being opened or locked.
CAUSE
This problem can occur when the following three conditions are true:

A form contains a subform.
A control on the main form references one of the controls on the subform.
The form is open in a single instance of the database.

Our database doesn’t lock until users start doing things (not entirely sure what though).

Has anyone else had problems using databases in this way?
Is there a consensus on whether splitting databases is good or bad?
A colleague thinks we should ditch the front end and write it purely in VB or another language (quite who would do this I don’t know!).

Any thoughts

Darsha

Reply | Quote

Replies

People who recommend ditching Access and rewriting it in VB usually know VB and are clueless in Access or are repeating what someone else has told them. Access and VB use the SAME database engine, and the userforms and reports will drive an Access developer to weep, since they lack all the nifty things built into Access forms/reports and controls.

I haven’t worked in the Citrix environment, although some of our clients are asking about it now, but splitting a front and back end is always the best way to reduce the risk of corrupting the interface and losing the entire database as a result. Citrix runs a client on the workstation, so you have a virtual installation running there, sort of like the previous local front end.

If the issue is a Citrix one, Wendell or one of the others who are familiar with it may be able to help. However, make sure the problem doesn’t lie in the way the application was built first. You’ll need to provide more information on what things the users are doing that triggers the error, though. Otherwise this is just speculation.

Reply | Quote

Even with my limited knowledge, your first comment is just what I was expecting to hear.
Creating fairly simple databases I am beginning to rely heavily on the features built in to Access. I couldn’t imagine trying to recreate the functionality in VB (mind you, my VB knowledge is less than basic!)

Again, the second comment refering to front and back ends was not a surprise. I have a difference of opinion with a colleague so its usefull for me to get feedback from an experienced developer (particularly when I agree with them).

Regarding the Citrix issue, my task is trying to fix a particular database which is causing problems.
My personal belief is that the problem lies in the architecture of the application. We have a number of users trying to use the thing at the same time, pre-Citrix I’m pretty certain it was never used like this.

Unfortunately, for all its perceived faults, the coding is way beyond my understanding right now.
What I do know it that data is gathered with a series of wizard style forms, compared to other tables to produce scores (or rankings) and then committed to the database. I’m sure this is twhen a conflict occurs.

If I ever get this sussed it’ll be a miracle.

Darsha

Reply | Quote

I wish I knew more about Citrix, but then I’d have to work for large clients I suppose. Based on the KB article, I suspect you might see this situation in other cases as well.

Your post doesn’t indicate whether or not you are running SR-1/SR-1a of Access – if you aren’t you definitely should be. For one thing, that should fix this problem. In addition, there were lots of pretty significant bugs fixed in the service release. The KB article also gives a work-around of leaving one open instance of the database open but not having the problem form open. You could try that quickly to see if it solves the problem. (I’m assuming you do have a form containing a subform, and a control on the main form references one of the subfrom controls.)

As to rewriting your app in VB, have you ever tried to do a subform in VB? It’s a lot of WORK! Our experience is that you can develop an Access based app in a quarter to a third of the time that doing the same thing in VB takes, and you often have a much more sophisticated and user friendly interface. And we always split all but the most trivial of Access databases. Hope this helps.

Reply | Quote