Locking Forms

[AccessEd87]

I am building a database to track, for example, antibiotic use in my patients.

The schema is a main table, which stores information like name, date of birth etc. it also includes a multi-selection combo box look up field that links to another table listing all possible antibiotic choices.

The linked table looks like this:

ID | Antibiotic
1 | Penicillin
2 | Other antibiotic
3 | etc.

I've done it this way as people can take more than one antibiotic simultaneously.

I've written a form which displays the fields of the main table, including the combobox as a dropdown menu to select antibiotics.

My problem is:

When many people are entering data into different records, they can't simultaneously enter information into the antibiotic field as it says its locked by another user. I have "record locks" set to "edited record" on the form. I'm confused as to why this should be happening when people are editing different records.

Am i missing something? Is this a sensible way to construct this database?

Best regards and thank you in advance.

 

[Uncle Gizmo]

Are you using the typical front end back-end arrangement?

 

[AccessEd87]

I've split the database to the back end (tables) are in a database sitting on a network drive, and the front end (forms) are distributed on peoples local PCs. I hope that's the typical arrangement!

 

[arnelgp]

can you set the locks to No locks and let access decide if it needs to be lock or not.

 

[gemma-the-husky]

maybe the lock is a particular feature of a multi-select field. I don't use them, and don't know the answer.

 

[AccessEd87]

arnelgp,

Correct me if i'm wrong. My understanding of locking options are:
No locks - where there is not protection from overlapping edits (corruption highest risk)
Edited Record locking - just the current record is locked (usually most practical)
All record locking - the underlying table is locked (safest against corruption, but impractical).

If I set it to no locks, and multiple people use using the database, wouldn't that be risky?

Many thanks!

 

[arnelgp]

it depends.
if user1 and user2 edited same record and user1 finished and save his record first than user2, user2 when he save his record will be presented a message (access message) that somebody has already made changes to the table. he can either overwrite this or cancel his record save and requery the form to see changes.

on No Locks, MS Access will be the marshall of the locks.

 

[AccessEd87]

Ah I see. I'm working with people who aren't the most tech savvy... so giving them that option could be risky. Do you know if this is an issue with using the multi-value fields? As far as I can tell, the problem never arrises for other fields.

 

[arnelgp]

even when your users are editing different records, still your multi-value field is pointing to same table, same table that other users are using simultaneously. since you have the form locked on edit, other users who need to edit the form through this one field cannot since it is locked.

you might also want to consider flushing all locks and actually saving the record to physical disk. on every save command, issue:

DBEngine.Idle dbFreeLocks