Locking records on MSAccess database 1

[perplexd]

Hi guys,
I have a VB frontend on a MSAccess2000 database.

When I want to edit a record I use SQL to call it from the table ie
rs.Source = "SELECT * FROM tblInstance "& _
"WHERE SerialNo = " & txtSerialNo.Text

The problem is that I want to lock the record so that only one user at a time can edit the record.

Using rs.LockType = adLockPessimistic only seems to lock the record whilst the server is updating the record.

It is not particularly important that other users are able to view the record whilst it is being edited. However the main problem is I must not let two people edit the record at the same time.

Thanks in advance!

 

[perplexd]

One way I was thinking that it might be done is to get VB to manually edit the .ldb file. However I have no idea on the syntax which needs to go in there.

 

[GrantDB]

This maybe over the top, but how about setting up a table that could be used as a log file. When a user edits a record the table is checked. This could be useful if you want to track who did what and when.

Just an idea

Grant

 

[Paddyo]

GrantDB's idea is probbaly the best solution you will get.

We use a lock table which records some unique identifier for the account being locked into one field and the user id into another field.

When a second user goes to amend the record, you must check the lock table to see if the unique id exists in the table. If it does then someone else has it locked. If not the you must write to the lock table so that other users cannot edit the record.

When the user is finished editing the record you delete the appropriate record form the lock table.

You must be careful to have proper error traps for Access controlled locks in the code which will lock and unlock accounts.

Hope this helps

 

[sonper]

perplexd,

I've been through this kind of situation when I have to monitor user logs wherein a user is not allowed to log in two computers at the same time. What I did is somewhat similar to what GrantDB and Paddyo have suggested but you have to look into also the possibility if there is power interruption or the computer suffers an abnormal shutdown, the record you last accessed will remain locked.

I suggest that at the start of your application or before accessing the db table, you empty if first.

Happy programming!

sonper

 

[Paddyo]

Hi

Sonper is correct - if you have a crash the lock table will keep the lock information in it.

I dont think however that you can just clear the locktable when you start the application as there maybe legimate locks from other users.

My solution to this, although it is still not 100% correct, is that when a particular user starts the application, I clear that users locks only.

This has further complications - what if the same username is logged onto two workstations. To overcome this we have used a user name or id in conjunction with the bios name of the computer which they are using. However this will not work if the user logs onto another workstation.

The simplest solution, if all of the above fail, is to have a maintenance program which lets you clear unwanted locks from the lock table.

good luck!

 

[perplexd]

In that case is there a way that we can detect how many connections there are to the database?

If I can find this out, then I can ensure that the maintenance program only runs when there are no other users working with the database.

 

[CCLINT]

Try this:
As soon as you want the lock to take place, just start editing one of the fields with out changing the value of it.....

rs("SomeField&quot= rs("SomeField&quot

Then use the CancelUpdate method later.

I haven't tried this, but if it doesn't work because the value hasn't actually changed, try adding a small field on the end of the table and edit that field with a truely different value.

At that point when the first field gets edited, ADO will lock the record.

Under DAO you only needed to call the Edit method, and then the page would get lock.

Both would require Pessimistic locks.