Would a control "Lock" property cause the table to lock up?

[Jesse Jones]

I have an access 2007 database. As many as fifty users may be using it as the same time. We are (as usual) having tons of locking problems. The database was designed to be used by 20, but the additional users are due to sudden, unexpected growth. I'm in the process of designing a SQL Server solution, but I need this to work in the meantime.

The form ALL USERS USE is a call form that populates from a query that pulls information from a clients table. On that form is a subform that populates from a query that pulls information from a call log table. The lock properties are set to NO LOCKS and the client query (which is the parent for the subform as it only shows the calls linked to the client ID on the main form) has parameters we use to ensure no two users are on the same record. The problem is that it still locks constantly. The warning "could not update; currently locked" is in my nightmares these days. I can't figure out how to get rid of it. Again, the lock property is NO LOCKS and no user is on the same record at any time.

The database is split into a FE/BE and each user has been given a local copy of the FE for their PC. This is the only access the have to the BE (which is hosted on a server).

I think I need to have the query on which the client form is based to create an independent recordset when it pulls info from the table. Would this solve my problem? If so, could anyone provide a sample VBA code?

One of the properties on a control was set as locked. Would this have caused the problem?

I am in a terrible spot trying to figure this out. Any help would be great appreciated! Thank you!!!!

[hype261]

How old is this database? I understand that you are using Office 2007, but was the actually database built using an earlier version of Access? I know that earlier versions of the JET engine would actually lock multiple records, I believe 10, when you started editing 1 record. I belive they removed this issue in later versions of Access. I have experienced this issue when running an old Access 2000 format database using Access 2007. I fixed the issue by creating a brand new Access 2007 db and importing all the forms, reports, queries, and modules into that one.

All the locked property does is prevent the user from updating the field. So I don't believe it would cause the issue.

[Jesse Jones]

The actual database is 2007. We are using it through 2010. All front end users are operating 2010.

Okay. I didn't think that lock property was the problem, but it was worth asking. I'm at a total loss.

[NeoPa]

The locking of the control should not effect the locking of any of the underlying records. The two locks refer to two separate concepts (even though the same is the same).

As you don't mention it, I will introduce the fact that for locking to work on multi-user systems the record locking on the client (MS Access) should be set up consistently. If you have 20 different workstations accessing the same database then it's critical that they all have sensible settings for this. Having them all the same makes good sense too. I would suggest setting Default record locking to Edited record and also to check (set to True) Open databases using record-level locking, even though there is much talk of this setting causing performance to drop noticeably in larger databases.

An alternative (or perhaps even a consistent addition) would be to ensure that any processing of recordsets that do not require updating of the data should be set to use a Recordset type of Snapshot. Snapshots never clash with anything, but they cannot be used to update data.

[Jesse Jones]

Thanks, NeoPa. The only problem is that I did have all clients using "Edited Record" as the default. Also, the backend was the same. The database did use record-level locking. The problem is that it was still locking. Badly.

Unfortunately, they do need to be able to update data, so the snapshot recordset type won't work. Thank you for your tips. Do you have any further thoughts? How can I get the forms to not be pulling info live from the query it's on (or the query from the table)? If the controls would populate and then break their connection, only reopening that connection to put the edits back into the query (or table), I really think this would help to.

Any thoughts on this?

[NeoPa]

I would suggest using a snapshot for the main form, then a separate form for when edits are required. Only this other form would need the editable recordset as source. Does this make sense?

[Jesse Jones]

Yes. This makes sense on the main form. However, the subform requires every record be changed.

[NeoPa]

Jesse Jones:
However, the subform requires every record be changed.

Every record? I'm not sure I follow. Forms, in standard usage, maintain a single record at a time. Multiple updates would need to be done in some form of code (which could of course be attached to the form, but would nevertheless not be relevant to a question phrased as you have).

Have I understood you correctly? If so, you need to rephrase your question so the important information is available.

PS. The term subform is generally used for those forms attached to the main form via a Subform control, which is not what I was suggesting. This needs to be understood clearly (although I suspect this is simply a matter of terminology confusion. I thought it best to mention it anyway).