Locking a record in a table

[Larsson]

Hi!

First some background info.
I have an application for employee administration. I only use unbound controls on the main form. These controls get their data from a record set via vb code.
Then they can look at the data and changed it if they want, but it is first when they hit the button “save” that the changes get saved to the record.
These works just fine. But the problem arises when there is more than one user editing the some record the some time.

[ul]For example: User A is looking at employee 161 and
needs to check some information.
[ul]The record: Employee 161, name Markus Larsson, phone
number 168108, email markus.larsson@momenta.se[/ul]
Some time later user B takes a look at employee 161 and changes the phone number. (Saving the record.)
[ul]The record: Employee 161, name Markus Larsson, phone
number 160010, email markus.larsson@momenta.se[/ul]
Now user A gets back and shall change the email. But when he saves the record he also saves the old phone number.
[ul]The record:Employee 161, name Markus Larsson, phone
number 168108, email markus@momenta.se[/ul]
[/ul]

So user A is undoing the changes that user B has done.

I have been thinking on some possible solutions to this problem without using bound controls. And the best solution (that I can think of) is to lock the record for a small amount of time, 2 minutes, after that the user cannot save the record.

But how do I lock the record?
Can I do it in code?
I have tried with a bound control on the form but the record only gets locked when that control is getting edited, not always.

Any suggestions are welcome, Larsson

 

[JoanneM]

Lots have been documented so to give you some pointers I've done a search and gleaned some points of reference for you.

the first is a discussion with a similar problem
thread222-119395 choosing a record-locking strategy in an Access database in a multiuser environment

http://msdn.microsoft.com/library/e...ingRecordLockingStrategyInMultiuserEnvirS.asp

You'll want to select record locking rather than page locking. Check this link.

Page-Level Locking vs. Record-Level Locking

http://msdn.microsoft.com/library/en-us/odeopg/html/deovrpagelevellockingvsrecordlevellocking.asp

Multiuser Settings

http://msdn.microsoft.com/library/en-us/odeopg/html/deovrmultiusersettings.asp

Additionally one of the guys on here summed locking up rather nicely
Locking, regardless of optimistic, pessimistic, or at the record level is all tracked by Jet and is not recorded in anyway that you can access. Remember that locking is always page based even if you are using record locking.


I hope this helps

jo

 

[oharab]

Nice links Jo.
Larsson, Why don't you just check to see if the data you are changing is different to the data you are saving over.
You could create an IsDirty property for each field on the form (best way would be to change the tag to "Dirty" on the AfterUpdate event of each control) and then update only the dirty fields.
You would need to have a routine that resets every tag to "NotDirty" that you would call on undos and when a new record is loaded.

HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[Larsson]

Yes, That has been one of my strategies, but I thought that if I just could lock the record, then that would be enough.

I shall take a look at the links and then we will see.

Larsson

 

[Larsson]

Now when I have read the good links, Thread222-119395 was very good, I have decided to go with the method to only save the changes and not the whole record.

I now, this isn’t the best method, but I can’t have the form bound to the table. So this is the only solution. Hopefully this “error” will not happen so often, it will just be around five users that will be allowed to change these records.

One other thing, all changes get logged, so it is possible to see what went wrong, when something goes wrong.

Thanks for your help, Larsson

 

[SgtJarrow]

Larsson,

Just a quick thought here.....as your user opens the form and the unbound controls are filled, also populate a public (maybe private) variable with the same data for each. Then at save, check the variables versus the data in the table and if any are different flag the user....

Open form -> get data -> put data in variable and also in unbound controls

At save:
Check variable versus table data -> if any different tell user -> allow user to update this field or not

In you example:

For example: User A is looking at employee 161 and needs to check some information.

The record (unbound controls and variable}: Employee 161, name Markus Larsson, phone number 168108, email markus.larsson@momenta.se

Some time later user B takes a look at employee 161 and changes the phone number.

The record (unbound controls and variable}: Employee 161, name Markus Larsson, phone number 160010, email markus.larsson@momenta.se

At save, data in table and variable are same so there is no error message and the data is updated.

Now user A gets back and shall change the email. But when he saves the record he also saves the old phone number.

The record:Employee 161, name Markus Larsson, phone number 168108, email markus@momenta.se

Data in the table does not match data in variable for phone number, so MsgBox user (displaying data from table and varible in the msgbox) asking if they want to overwrite this new number. If no, update the unbound control from the table, then save as normal. If yes, save as normal, but the phone number will be changed back to the old number

I am kind of startingt o talk circles, so I hope you understand my suggestion.....Hope it helps.


If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Larsson]

Yes, I am already doing that, saving the data in some private controls. And I will be using those to check with the table to see if they have been changed.

But it is so much coding. And it is that that I am being paid for...

Larsson