find user who has the record open

[megatronixs]

Hi all,

is there a way to find out who has a record open and send him message or someting similar?

It happens from time to time that some one has a record open (selected in a form) and then is doing other things while the record is locked and a second person can't do nothing to the record.

It would be great if the user could be shown in a message box or something.

Greetings.

 

[megatronixs]

Hi,

Yes I have.

 

[burrina]

Do you have the form's Record Locks set to Edited Record?

 

[megatronixs]

Ho,

I'm not sure, need to check that one out. (the database was not created by me)

Greetings.

 

[The_Doc_Man]

If you use queries that set locking to "optimistic" then you minimize the frequency of this particular issue. You can't totally get rid of it, but you can cut it down a LOT.

 

[gemma-the-husky]

I agree with the doc man. other than for very specialised apps, I can see no need to ever need pessimistic locking, so this never ought to be an issue.

And if you are designing a high-end app, you probably would not need advice!

 

[The_Doc_Man]

There is one more speed-bump in this road, related to the "Old Programmer's Rules." The one in question is that Access can't tell you anything you didn't tell it first, or at least tell it how to do it first.

Access doesn't lock records. It locks disk buffers that hold records. This sounds like I'm quibbling, but I'm not. If a user has a block of 2048 bytes locked (the typical size of a locked block) and that block contains more than one record (completely or in part), then it is possible that the record you want is locked because someone else has its nearest neighbor locked, and that nearest neighbor could be ANYTHING - including a record from a totally unrelated table.

Therefore, the only way to know if a user has locked a record is if you TELL Access to remember the user ID of the user who locked the record. And you have to hope that you remember to unlock the record when the user exits, or that you CAN unlock the record - since it is possible that some other user has locked the block for the user-lock record.

In my case, I take the coward's way out I note all users who appear to be in the database at the time and use that information to build a mailing list. Then I send out a message that says "If you are the person who still has form MumbleFratz open, please either close that form or navigate it to a different record." Note that doing it this way does not eliminate the difficulties of failing to properly mark a closed session. However, I have ways to doing that separately and therefore still end up with a smaller mailing list by limiting the message to current users.

 

[megatronixs]

Hi all,

Thank you all for you comments and tips
Maybe it can be done in a different way to catch the one that has that particular record open as The Doc Man mentions.
I also had the problem that when I needed to do some maintanance on the back-end and asked all the users to close the database, there where still people out there that did not see the email and kept it open. I added a extra field to the users table that will write In when they open the database and Out when they close the database. This way I know who has it open and can ask the person/s directly via mail.
Maybe I can do something similar, when some one opens a record, it will put the user name in a table along with the record ID nr. Now all I have to do is to check who has for example ID 398 open and comunicate this to the person directly and no need to write to the whole team and maybe that one person is on a meeting or for lunch.
I guess that would be a good starting point to add the function.

any one with a different idea?

Greetings.

 

[spikepl]

@The_Doc_Man

Are you sure about this block locking thing? AFAIK it is about three Access versions ago that that was superseded, and Access now locks single records.

Update: apparently the issue is not straightforward: http://www.tek-tips.com/viewthread.cfm?qid=1613387 block-level locking seems to occur but record-level locking can be achieved with some effort.

@megatronixs

You can do it like that.

If you allow users to only open one record at a time, then you can just note, in your login table, the recordID the given user operates on. You need to do something extra in case of a system crash (so that the information about a record lock gets cleared). What I do is I check for existence of locking file for the backend after closing all forms in the front end (and thus disconnecting all tables), In such case I then clear the information about locked records. In case of crash, all that is required is that one logs on and off again to clear all records,

If that isn't there, that means no-one is connected, and then I clear the table.

 

[gemma-the-husky]

OP

you really really do not want to be doing this. Unless you are doing something exotic in financial services, just turn off pessimistic locking.

"optimistic locking" is not quite "no locking". It allows multiple readers and a single writer to work together. It only ever becomes an issue with 2 concurrent writers, and still solves the problem - without giving you the general locking issues you are getting.

I hope that makes sense. It avoids the semi deadlock situation you are getting. The solution to what you are getting is for the affected person to backout of the process, and unwind his actions, which is why it becomes tricky, and is why it is really best used only on special circumstances.

 

[The_Doc_Man]

spikepl - even with "Edited Record" I have seen some minor evidence that block locks still occur, though now that I finally have gotten my primary DB moved to a faster network, the problem is far less significant. When we were using a remote networked drive for the BE file, those locked blocks tended to stand out like sore thumbs.

As to how maintenance downtime can be managed, I take this approach. First, EVERYONE (including me) logs in through a switchboard form. The switchboard never goes away, though it can go into the background when it opens a form based on the switchboard actions. Of course, the database options have been chosen to limit the user's ability to blithely bypass that startup form. I can click a button that would

Second, there is an On_Timer event for that switchboard. It checks my "scheduled event" table to see if the current time of day is between the start and stop times for any scheduled event. (I maintain the event table with a dedicated form.) The On_Timer computes the difference between the current time and the start/stop times. If STARTTIME is in the past and ENDTIME is in the future, you are smack-dab in the middle of the scheduled event. Once a scheduled event expires (both STARTTIME and ENDTIME in the past), it is marked in a way to prevent it from showing up again. If the next available scheduled event is in the future (both STARTTIME and ENDTIME in the future), the timer used for the form's new interval might change from 60,000 milliseconds to 30,000 msec to as little as 5,000 msec just to assure that people don't hang on to the session too long.

Third, the timer for the switchboard tests the event table at least once per minute but the switchboard's OnOpen event checks the scheduler every time. If a user tries to log in during the event, they immediately get a "you cannot log in now" message and then they get logged out. If a user is already logged in when the event starts, the timer code wakes up and sends a pop-up that says "you are logged in during a scheduled down-time event." The message goes away within 30 seconds after which the timer code triggers a sequence of actions that results in the switchboard form issuing the Application.Quit command. Problem solved within 2 minutes after event start, approximately.

 

[megatronixs]

Hi spikepl,
I will try to implement that part that when a user opens the form, it will take the userlogin to write it on the table and then clear it when he closes the form. I hope this way will make it esier for me and avoid sending emails to over 80 persons to ask.

Thank all of you

 

[gemma-the-husky]

Hi spikepl,
I will try to implement that part that when a user opens the form, it will take the userlogin to write it on the table and then clear it when he closes the form. I hope this way will make it esier for me and avoid sending emails to over 80 persons to ask.

Thank all of you

Good luck - the only caveat here is a "false marker"

At some point you will undoubtedly have a phantom lock indicator left on the record when none exists. You need a way to reset/ignore such markers. Also, I am not sure about this - but a slightly different issue - if you cannot read the record, how can you read the marker?

 

[megatronixs]

Hi,

I solved the "false marker" with the "on close form" to run a update query so it will just leave the marker field empty when the user closes the form. All users are very happy as they don't have to sent emails to the whole group and the whole group is also happy not to receive from time to time an email for fun.

Greetings.