Check for a locked record (1 Viewer)

[mloucel]

Hello ALL:

I have my database for sharing as:

Default Open Mode = Shared
Default Record Locking = No Locks (I guess Access do the job here based on other posts)

now this seems to be great, but ... then why should I NOT use:

"Default Record Locking = Edited Record"

and how can I check and display a message to the EU (End User) if that record is locked ... (Using VBA of course)

Thanks Maurice.

 

[Pat Hartman]

You could use Edited Record as the default but I wouldn't. Think about this - how would other users be potentially affected if Mary opens a form at 9 AM, makes one small change but doesn't save, gets distracted and goes on to work on other stuff? Access doesn't actually lock ONE record as we know it, it actually locks the smallest unit of disk storage that can be defined by any particular device. So if the smallest is a sector and that is 4800 bytes and your average record length is 200, you would always lock as many as 24 records which spreads the disruption.

If you don't hide the record selector, it will tell you the status of the selected record. I can't show the third option which is a circle with a line through it and that is what you would see if you opened a record that someone else was currently editing. To see that open the database from two different computers on the LAN. I'm working stand alone

 

[Isaac]

Recipe 10.9 Determine if a Record Is Locked and by Whom :: Chapter 10. Multiuser Applications :: Access :: Microsoft Products :: eTutorials.org

In Access 2000, Microsoft added an important new capability to the Jet database engine: record-level locking. In previous versions, if you locked the record being edited, you would also lock any other records that happened to be on the same data page as the edited one. A data page held 2,048 characters, so it was likely that locking would affect more than one record.

In Access 2000, Microsoft increased the size of data pages from 2,048 characters to 4,096 characters in order to support Unicode characters, which each consume 2 bytes. With such large pages, Microsoft decided that it needed to allow you to lock single records. In the Advanced page of the Tools

Options dialog, you can now choose to open the database using record-level locking. This avoids locking entire pages when locking the edited record

 

[mloucel]

Recipe 10.9 Determine if a Record Is Locked and by Whom :: Chapter 10. Multiuser Applications :: Access :: Microsoft Products :: eTutorials.org

Ok got that and it is a default now, but I still have a doubt, what will be better:

Default record locking:
[ ] No locks
[ ] All Records
[X] Edited Record

my gut says #3 Edited record is the best answer, instead of No locks...

And using VBA how can I tell the EU that the record he tries to open is OPEN by another user?

 

[Isaac]

Instead of trying to figure out a way to determine when access says the record is locked internally, you could approach it differently and simply capture the event based on whenever a user goes into a record through your interface. Not sure if that will be something you can implement in your case or not, just something to consider.

I guess it might be kind of hard to figure out when they leave that record, but possibly doable.

If you're having a lot of problems with users who lock records and leave their workstation for long periods of time, you may also want to consider implementing a timeout database exit approach.

 

[arnelgp]

use the Best choice (No Lock).
this will Let MSA handle the lock.
you will get Deadlock when Edited Record is used.

imagine this scenario. someone is editing a record, goes to the CR
because of natural calamity, while another who
want to edit same record, is still waiting for 20min
for the record to be free.

 

[mloucel]

Instead of trying to figure out a way to determine when access says the record is locked internally, you could approach it differently and simply capture the event based on whenever a user goes into a record through your interface. Not sure if that will be something you can implement in your case or not, just something to consider.

I guess it might be kind of hard to figure out when they leave that record, but possibly doable.

If you're having a lot of problems with users who lock records and leave their workstation for long periods of time, you may also want to consider implementing a timeout database exit approach.

Thanks but I am not worried about someone leaving the desk, there's gotta be a way to display a message and I will find it.
Thanks anyhow.

 

[mloucel]

use the Best choice (No Lock).
this will Let MSA handle the lock.
you will get Deadlock when Edited Record is used.

imagine this scenario. someone is editing a record, goes to the CR
because of natural calamity, while another who
want to edit same record, is still waiting for 20min
for the record to be free.

Yes I got you, but there's gotta be a way, if I found a way I'll post my solution. I know someone somewhere already did it, is just a matter of looking deep and learn.
Thanks Arnel.

 

[mloucel]

Recipe 10.9 Determine if a Record Is Locked and by Whom :: Chapter 10. Multiuser Applications :: Access :: Microsoft Products :: eTutorials.org

Thanks I checked the link, good advice, but not practical, gotta be a way....

 

[Isaac]

I just tested using Edited Record setting, and then having a button on a form that tried to update that same record - WHILE I was in the middle of editing it (I had dirtied the form, and the little Pencil showed me I was editing it) - and the code-based Update still successfully took place.

Can anyone explain that to me? I would have thought I would get an error. In the code I used currentdb.execute
edit never mind - I had forgot to set Edited Record setting on the form itself. disregard

 

[Isaac]

@mloucel

If you are determined to go this route, see attached demonstration of what you might do.
I'm NOT recommending this, as I haven't thoroughly thought through the consequences - just an example of what does, indeed, seem to work.

To Test:

1. open the database, enable content
2. open the Form
3. before editing a record, click the button. (shows False - not locked).
4. now, begin editing the record by backspacing or adding text into the column1 textbox
5. now click the button (shows True - locked)

 

[mloucel]

@mloucel

If you are determined to go this route, see attached demonstration of what you might do.
I'm NOT recommending this, as I haven't thoroughly thought through the consequences - just an example of what does, indeed, seem to work.

To Test:

1. open the database, enable content
2. open the Form
3. before editing a record, click the button. (shows False - not locked).
4. now, begin editing the record by backspacing or adding text into the column1 textbox
5. now click the button (shows True - locked)

I'll work with that ..

 

[theDBguy]

Here's what it says on this website:

In a multiuser database, you can use the No Locks setting if you want to use optimistic locking and warn users attempting to edit the same record on a form. You can use the Edited Record setting if you want to prevent two or more users editing data at the same time.

Does that explain it?

RecordLocks Property - Access (microsoft.com)

 

[mloucel]

Here's what it says on this website:

Does that explain it?

RecordLocks Property - Access (microsoft.com)

Yes thanks, it does, I am working on a solution, if I am able to make it I will let you guys know, if not I will look for more help..
FYI..

This is what I need to accomplish, solving the problem, if the user leaves the desk and the record is left open.
my solution
WHO CARES, if the record is open and another user tries to edit that record, don't let hi/her just inform him/her
and leave, let them complain on each other.

' 1) Code before opens a database
' 2) Code search a record and once found
' 3) Displays the record on screen
' 4) User1 starts editing the record
'
' SECOND USER DOES 1 thru 3
' I need to enter some code like this
' after 3 (this is mnemonic)
If IsThisRecordLock() then
    msgbox "This record is in use by another user" & vbcrl _
        & "You cannot edit the record while the" & vbCrl _
        & "the record is in use, going back to MENU", vbOkOnly, "Record Locked"
    me.undo
    ' close the form
    ' return to menu
end if

 

[theDBguy]

Yes thanks, it does, I am working on a solution, if I am able to make it I will let you guys know, if not I will look for more help..
FYI..

This is what I need to accomplish, solving the problem, if the user leaves the desk and the record is left open.
my solution
WHO CARES, if the record is open and another user tries to edit that record, don't let hi/her just inform him/her
and leave, let them complain on each other.

' 1) Code before opens a database
' 2) Code search a record and once found
' 3) Displays the record on screen
' 4) User1 starts editing the record
'
' SECOND USER DOES 1 thru 3
' I need to enter some code like this
' after 3 (this is mnemonic)
If IsThisRecordLock() then
    msgbox "This record is in use by another user" & vbcrl _
        & "You cannot edit the record while the" & vbCrl _
        & "the record is in use, going back to MENU", vbOkOnly, "Record Locked"
    me.undo
    ' close the form
    ' return to menu
end if

Hi. Just thinking out loud, but I wonder if the Updatable property could be used for this?

 

[mloucel]

Updatable property

Interesting, I am a newbie so I've never used that but let's see some examples in the web and let's see if it works.

 

[theDBguy]

Interesting, I am a newbie so I've never used that but let's see some examples in the web and let's see if it works.

First of all, let's use this link as a reference for starters.

Recordset.Updatable property (DAO) | Microsoft Docs

 

[mloucel]

Hello ALL..
I think I found a solution, NOT THE BEST but considering the way access works this could be not the perfect answer but an answer, once I implement the idea I will post it and hopefully that could open the Geniuses here to find a better solution than mine, so far it has me impressed and it works, now the possibilities that 2 or 3 users look for the same record at the exact second is just mind-blowing, so I believe it will work, I check my chances that 2 or 3 people look at a record and even if they are fast the database still will take about 2 seconds or less to update, so it is possible.

my idea is that if someone can make the idea better and more reliable (less than 2 seconds) then it will all be worth it, and will help a few of us that deal with record locking in a multiuser environment.

I will keep you updated.

Thanks for your help.

 

[theDBguy]

Hello ALL..
I think I found a solution, NOT THE BEST but considering the way access works this could be not the perfect answer but an answer, once I implement the idea I will post it and hopefully that could open the Geniuses here to find a better solution than mine, so far it has me impressed and it works, now the possibilities that 2 or 3 users look for the same record at the exact second is just mind-blowing, so I believe it will work, I check my chances that 2 or 3 people look at a record and even if they are fast the database still will take about 2 seconds or less to update, so it is possible.

my idea is that if someone can make the idea better and more reliable (less than 2 seconds) then it will all be worth it, and will help a few of us that deal with record locking in a multiuser environment.

I will keep you updated.

Thanks for your help.

Hi. Can't wait to hear your idea.

 

[Pat Hartman]

You never told us what problem you are trying to solve. Access has no problem with record locks. What are you trying to do "better"?