askolits
asked on
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?
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?
'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.
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.
ASKER
Whoa! I guess I nailed a good one eh? I better up the points.
ASKER
Ponits increased!
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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!
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.