Have to stop two people accessing the sames record at the same time?

[homer2002]

I have a multi user database with a table of accounts (say 1000 account)

I have about 5 people working on these accounts and I have a form where they can go through the accounts change what they need and carry on.

Problem is, when 2 people access the same record.... of course one of them always loses out.

Is there a way that I can stop 2 people viewing the same record at the same time?? ... just being able to see 1 or 10 accounts at a time would be ok too.

I reckon I should write a query to pick 10 accounts for each person, but then I dont know how to stop anyone else access those records when they are already open.

Can anyone help me?

 

[Autoeng]

Under Tool-Options-Advanced select under Record Locking - Edited Record.

 

[missinglinq]

You don't state which version of Access you are using, but Access 2000 provides three lvels of locking records. To set this function goto

Tools-> Options-> Advanced-> Default Record Lock

Your choices are then:

1) No locks: Just what it says (probably what you have set now)

2) Edited Records
If User A is editing a record and User B tries to edit the same record, a special emblem (a circle with a slash thru it) appears to User B alerting him to the fact that the record is already being edited.

3) All Records:
All records accessed by the current form are locked.

# 2 is probably option of choice for your problem.

Could you program a work around? Probably, but I'm not sure it would be worth the effort. Access has been developed over the years by programmers with a lot of years of experience in how databases should be run. Their means of accomplishing a given task is USUALLY (although not ALWAYS) the best way.

The Missinglinq

 

[Tekime]

The default record lock you choose in Options only applies to datasheet views of tables and queries, and to dynasets. If you want record locking on forms and reports, set the Record Locks property in the data tab of the object's properties box.

 

[Autoeng]

Sorry Tekime but you are incorrect.

From Access help...

Note The RecordLocks property only applies to forms, reports, or queries in a Microsoft Access database (.mdb).

 

[Tekime]

I could be wrong, but I actually referred to the Access 2000 complete reference just to double check. I'm not sure how the recordlocks property is associated with the default record lock specified in Access Options though, can you please explain this for me?

 

[Autoeng]

Record locking can be accomplished on the form, query and report level but, also from Access Help...

To change the default RecordLocks property setting for forms, click Options on the Tools menu, click the Advanced tab on the Options dialog box, and then select the desired option under Default Record Locking.

And...

Edited Record 2 (Forms and queries only) A page of records is locked as soon as any user starts editing any field in the record and stays locked until the user moves to another record. Consequently, a record can be edited by only one user at a time. This is also called "pessimistic" locking.

As you typically on run into record locking problems while doing data entry via forms the first statement applies.

 

[Tekime]

Sheesh. I wasn't particularly fond of this book in the first place, but this is grounds for a burning.. I had just finished the chapter on databsae sharing, which covered record locking, and explicitly says that the default record locking setting does not apply to forms. I suggest that everyone steer clear of The Complete Reference: Access 2000 under Osborne.

Thanks for correcting me Autoeng. I did notice though, that if you already have a form with no locking and you enable recordlocks in Options, it does not update existing forms. I guess the default setting is just that - the default record locking for a new object.

 

[missinglinq]

Tekime,

Just goes to show my old man was right; he always encouraged me to read, but almost half a century ago he warned me "Don't always believe everything you see in print; any idiot can set type!"

The Missinglinq