Access 2007 DB Row Locking
-
I am working on an Access 2007 project that I inherited. I am pulling recordsets using
Dim Command as String
Command = "select storename from stores where storeid = " + CStr(selectedStoreId)
Set rs = CurrentDb.OpenRecordset(Command)and the I populate the fields on the form using
txtStoreName.Value = rs!StoreName
I have 7 forms, and they're all very simple, and the edit mode on all of them is done this way. The question is, when the user clicks the Edit button, before I enable the fields I want to check to make sure no one else is editing the row. How do I do this in Access 2007?
Everything makes sense in someone's mind
-
I am working on an Access 2007 project that I inherited. I am pulling recordsets using
Dim Command as String
Command = "select storename from stores where storeid = " + CStr(selectedStoreId)
Set rs = CurrentDb.OpenRecordset(Command)and the I populate the fields on the form using
txtStoreName.Value = rs!StoreName
I have 7 forms, and they're all very simple, and the edit mode on all of them is done this way. The question is, when the user clicks the Edit button, before I enable the fields I want to check to make sure no one else is editing the row. How do I do this in Access 2007?
Everything makes sense in someone's mind
Implementing locking is probably the last resort of a bad design. Think about it. A client locks a row, then loses network connectivity. The row is still locked, and the client that locked the row can't unlock it. Now what do you do? The rest of your clients that still do have connectivity are screwed until that row or table is unlocked. You may want to read up on all of your options before doing something so drastic. Google for "Access database concurrency".
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
I am working on an Access 2007 project that I inherited. I am pulling recordsets using
Dim Command as String
Command = "select storename from stores where storeid = " + CStr(selectedStoreId)
Set rs = CurrentDb.OpenRecordset(Command)and the I populate the fields on the form using
txtStoreName.Value = rs!StoreName
I have 7 forms, and they're all very simple, and the edit mode on all of them is done this way. The question is, when the user clicks the Edit button, before I enable the fields I want to check to make sure no one else is editing the row. How do I do this in Access 2007?
Everything makes sense in someone's mind
Most developers ignore this problem, last in has the power is the general rule. There are so many different scenarios that can be envisaged that to try and deal with them will very quickly have you biting you're own arse. Make sure it is a real world problem not something thought up by a manager trying to cover all bases. I have often responded to this requirement with 'if we get a race condition then I'll deal with it otherwise let me do something productive'. Then when they insist do an estimate of the cost and performance hit!
Never underestimate the power of human stupidity RAH
-
I am working on an Access 2007 project that I inherited. I am pulling recordsets using
Dim Command as String
Command = "select storename from stores where storeid = " + CStr(selectedStoreId)
Set rs = CurrentDb.OpenRecordset(Command)and the I populate the fields on the form using
txtStoreName.Value = rs!StoreName
I have 7 forms, and they're all very simple, and the edit mode on all of them is done this way. The question is, when the user clicks the Edit button, before I enable the fields I want to check to make sure no one else is editing the row. How do I do this in Access 2007?
Everything makes sense in someone's mind
Here's a possible solution... You can implement your own locking mechanism simply by having a lock table. (this way you can have an editable table that, in case the client loses connectivity, another user can go to the administrator edit screen and clear the lock, if necessary) tblLocks: TableName , IDNum, LockedBy (primary key: TableName,IDNum) when the edit is about to take place, try adding a record with the table/id/username if you get an error, that record for that table is locked. otherwise leave that entry there until they quit or save and exit. then delete the record. The LockedBy field is not necessary, but then you can see who has it locked, if you so desire. Just use the windows login name or if your software has a login, use that.