Record Locking Problem

[Grendall5383]

Hi all,



First time poster on these forums and part-time Access junkie.

I have built a very simple DB designed to allow multiple users to record the outcomes of different client incoming calls. Each user (7 in total) has a copy of the FE on their local machine and the BE is stored on a locally shared network drive.

Each staff member uses a simple bound form to record the outcome of their incoming calls then simply adds the record to a table and then moves onto the next call. We are looking at about 400 calls per day, lots of new records ....

Each user has their own "Staff Number" attached to each record they add and (using the form's filter) they can only scroll through records they have added to the DB themselves. So in short, each user cant be in another’s records through the data capture forms we use.

Once or twice per day, it appears that one user will "lock" the others from adding new records or amending old records? I have attached a Word doc with some screen shots to illustrate the error and the set-up I am using.

I have searched high and low for an answer to this problem. Record locking is set to "No Locks" in both the options and the form's properties, Default open mode is set to "Shared", Open the DB using Record Level locking is enabled and randomly I am still getting "Runtime error -2147352567 (80020009)"

This is beyond me I am afraid.

Any help you may have will brighten my life

Graham

[RuralGuy]

How about binding your form to a query of the table rather than directly to the table?

[Grendall5383]

Never even thought of that approach ....

Would I still be able to add new records to the table even though the forms would be based on a query?

Sorry for the "beginner" level questions, I am self-tutored and still getting the hang of this.

Thanks for the reply

Graham

[RuralGuy]

A Form can not tell the difference between a table and a query. Both act the same.

[Grendall5383]

Right,

I am going to go away and research how i could do this and come back to post the results.

Thanks again.

Graham

[RuralGuy]

Just create a query of all of the fields in your table and then save it. Then in your Form, set the RecordSource to your query instead of the table.

[Grendall5383]

Thanks for the information,

I will give this a go over the weekend and see if it solves my problem.

Graham

[Grendall5383]

I am back ....

I have done what you suggested and will be battering the set-up over the next few days.

After testing it quickly this afternoon, it appear to be holding up.

I will post back when i can get more users testing this.

Graham

[RuralGuy]

Thanks for the update.

[Grendall5383]

Hi there,

I had 3 users simultaneously adding records to the table through a query and it held up much better than without using the query but we still managed to recreate the error.

We were entering new records very quickly (one every 10 seconds or so times 3 users).

Any more thoughts as to what's going on? I am guessing that this has to be an error in the way I have created the forms / tables as Access has to be able to handle people adding records quite quickly for databases with many users?

When a user locks the table, all they need to do is “add a new record” or scroll through their old records to “release” the lock?

Don’t know if this helps?

Cheers

Graham