• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

MSAccess - Detecting Record Locks

I want to be able to detect a record that is open by another user before they actually open the record. This is a shared database. Currently using Access 2000.

I could have the app create a log file so when a user opens a record, it is written to a table. When another user tries to open that same record, I can check the table and see if it's open. But that's problematic in case the other app locks up and crashes, and I don;t have the chance to clear the log table. I've seen other solutions but they require that you get into the record first before finding if another user is in it. I want to pre-empt that and detect it 'before' the user tries to access the record. There must be someway to check the record in the Jet engine to see if someone else has it open. Access knows when you open the record especailly with pessismistic locking. So where is the flag that tells me the record is locked?

0
askolits
Asked:
askolits
  • 5
  • 2
  • 2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<So where is the flag that tells me the record is locked?>>
  The flag is a lock which is placed on the .LDB file by JET and is virtualized in the OS/NOS that is sharing the MDB file.  In other words, it's never written to disk, but simply something that is held in memory by the OS.
  For you to check on those locks is some what problematic  as JET 4.0's locking scheme is not fully documented.
  Your best approach is to try and edit the record using pessimitic locking and then trap the error if one occurs indicating that someone else has the record locked.
JimD.
0
 
peter57rCommented:
'There must be someway to check the record ....'

Do you really believe that if this was possible , that there would be any developer in the world who would not know the solution.

When you find it, please publish your findings so that mankind (well, Access developers) can benefit.
0
 
askolitsAuthor Commented:
Whoa! I guess I nailed a good one eh? I better up the points.
 
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
askolitsAuthor Commented:
Ponits increased!
0
 
askolitsAuthor Commented:
Oh, I guess I can open a recordset and try to do an update, then do the trap as you suggested. That's a pretty simple thing to do. Surprised I didn't think of it.
Yet, although I set the form that will acess the record, to pessimistic, should I aslo set the table being access on the backend for "row level tracking"? I've actuall never used that parameter before.
 
0
 
peter57rCommented:
"I've seen other solutions but they require that you get into the record first before finding if another user is in it. I want to pre-empt that "

I thought you had ruled out that solution by your comment above.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I thought you had ruled out that solution by your comment above.>>
 So did I.
  As to:
<<Yet, although I set the form that will acess the record, to pessimistic, should I aslo set the table being access on the backend for "row level tracking"? I've actuall never used that parameter before.>>
  A lot depends on the app (number of users, what they do, size of tables,etc), but in general I would avoid row level locking.  First there is this:
"A limitation to record-level locking is that users will not be notified when another user is locking the record. Also, record-level locking is not enabled for Memo data types."
  Which Microsoft has never really seen fit to explain.  There have been quite a few questions on EE where row level locking was involved and it didn't seem to work right.  Then there is the fact hat not all operations use row level locking anyway and there is a performance hit to boot.  There is also reference to a problem with DAO and if you use it, you must first open a connection through ADO in order to get DAO to lock at the row level.
  Overall, it just seems to be more pain then it is worth.  You would think something as fundamental as locking in a database you would need to get 100% right, but that doesn't seem to be the case with Microsoft (at least with JET anyway).
 For my money (and those of my clients), if concurrency issues really are a concern, then I would use the old technique of padding a record out so that only one record will fit on a page and thus you will get "record level" locking.  To me, this gives me the best of both worlds being that disk space in today's times is fairly cheap.
JimD.
0
 
askolitsAuthor Commented:
What I meant was to check it before the user actually got into a form or query to work on the record.  The solutions I saw said that you would place something on the OnCurrent or OnOpen event. I don't want them to get that far beofre I test the record.
I have a form that displays a product to update. Before they enter the product form, they have to select the product from a list in a combo box on a separate form. On the BeforeUpdate (or some event) of the CBO I'll write a bit of code that simply tries to open and edit the record. If it's being used, I'll deliver  a message that tells them they can't open that record right now.
I just wasn't thinking about checking the records in 'code' first before allowing them to open the form. Pretty obvious solution, I was just heading down the wrong path.
Yet, I still wonder about "row level tracking"? I'll have to check into that further.
0
 
askolitsAuthor Commented:
Padding the record to the page size sounds interesting. Don't think I need to do that. I'll only have one or two concurrent users, but a concept that I've never actually  heard of before and may have to use in the future. Sorry that I may have confused you with the question. I split the points. Thanks!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now