UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Address Delay In Record Update, Access 2010    
 
   
enjoyjoshua
post Sep 13 2017, 12:42 AM
Post#1



Posts: 8
Joined: 13-September 17



Good evening.. Looking for help. This one has stumped me for a long time.

Access 2010, Split DB

We use my Access app for a call center. There's a form (callQueue) that resides in the corner of every user's screen, with a continuous subform contained within it. Each continuous subform record represents an outgoing phone call that needs to be made.

When a record is opened, it turns yellow on the continuous form based on an isLocked field and conditional formatting.

My problem is that there can be 10+ records in this queue at any given time and there can be up to four employees working the queue.

About 20% of the time, my locking method doesn't work. It seems there's a delay situation (3-5 seconds) between when a very simple update query is run and the time it actually reflects in the table.

CODE
'This sub runs when a user clicks on a record to open it

'check to see if locked
If Me.isLocked = -1 Then
    MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
    Exit Sub
End If

'check to see if locked another way
If DLookup("isLocked", "callQueue", "[id]=" & Me.id) = -1 Then
    MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
    Exit Sub
End If
          
'check to see if locked another way
SQL = "SELECT * FROM callQueue WHERE id=" & Me.id & ";"
Set callQueueData = CurrentDb.OpenRecordset(SQL)
If Not callQueueData.eof Then
    callQueueData.MoveFirst
    If callQueueData!isLocked = -1 Then
        MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
        Exit Sub
    End If
End If
          
'lock record
SQL = "UPDATE callQueue SET isLocked=-1 WHERE id=" & Me.id & ";"
CurrentDb.Execute SQL

'lock record another way
SQL = "SELECT * FROM callQueue WHERE id=" & Me.id & ";"
Set callQueueData = CurrentDb.OpenRecordset(SQL)
If Not callQueueData.eof Then
    callQueueData.MoveFirst
    callQueueData.Edit
    callQueueData!isLocked = -1
    callQueueData.Update
End If

'here is where it is assumed that no other user is working this call and the record is opened in a separate popup form.


As you can see, I've tried many and multiple methods to check for a lock and to perform a lock. Still, at least 20% of the time, it's letting two users into the same record.

Bottom line: If user A updates a record via SQL or DAO or whatever and that SQL or DAO process is complete, why wouldn't a DLOOKUP by user B reflect the changes for another 3-5 seconds?

Any ideas?

Thank you!
This post has been edited by enjoyjoshua: Sep 13 2017, 12:43 AM
Go to the top of the page
 
theDBguy
post Sep 13 2017, 09:35 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,676
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

This might be an overkill but another way to check for a lock is to Requery the form when the user clicks the button.

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
enjoyjoshua
post Sep 13 2017, 09:38 AM
Post#3



Posts: 8
Joined: 13-September 17



Thanks theDBguy.. I did try that as well. I tried a requery and a refresh. I also have a button on the form that is just there solely to perform a requery. If user A locks the record, it takes about 3 presses of the requery button for user B to see the change. Grrrrr.. Thank you though!
Go to the top of the page
 
PhilS
post Sep 13 2017, 09:51 AM
Post#4



Posts: 365
Joined: 26-May 15
From: The middle of Germany


What about reversing the process?

Lock the record first (if not locked by someone else) and then query the record. You can detect success of the locking attempt by checking RecordsAffected of the update statement.

In any case, I would recomend not to use just the isLocked-flag in your table but store the user and date/time of the acquired lock.

--------------------
The shocking revelation about digital signatures for accdb files.
Go to the top of the page
 
enjoyjoshua
post Sep 13 2017, 10:01 AM
Post#5



Posts: 8
Joined: 13-September 17



Thanks, PhilS.

I have never used RecordsAffected. I'm looking it up and giving it a shot.
Go to the top of the page
 
PhilS
post Sep 13 2017, 10:19 AM
Post#6



Posts: 365
Joined: 26-May 15
From: The middle of Germany


While evaluating RecordsAffected would be the more elegant way, you would not even depend on it if you write the username of the user holding the lock to the table. You can compare them to make sure the current user is actually owning the lock.

--------------------
The shocking revelation about digital signatures for accdb files.
Go to the top of the page
 
enjoyjoshua
post Sep 13 2017, 11:14 AM
Post#7



Posts: 8
Joined: 13-September 17



Yeah, username lock makes a lot of sense. I didn't think about it being necessary because all 4 thin clients are in the same room, but since the records aren't updating immediately, the solution to lock by user, then make sure the lock has taken, then move forward makes a lot of sense. The only thing I'm worried about is making sure the lock is able to be detected before a new lock is initiated. If that doesn't work, I can check again to make sure the lock is for the current user, throughout the process. This is one of those things that I really thought would be cut and dried, working as is. We've dealt with it for 3 years, but I need a solution now that I'm thoroughly annoyed with my staff having to yell across the room to tell eachother which one they are working. Thanks for all the help. I'll report back later when I have time to test it out.
Go to the top of the page
 
enjoyjoshua
post Sep 13 2017, 05:37 PM
Post#8



Posts: 8
Joined: 13-September 17



PhilS (or anyone),

In your suggestion to implement recordsAffected, do you have a suggestion on how to effectively check for the lock. The first half of my code on the OP is checking for a lock. Using that method, I'm experiencing the same issues when using a lockedBy field rather than an isLocked field. Which I guess makes sense.

The problem here is really the lock check, rather than the lock itself.

So I could .execute then check for recordsAffected to ensure it takes, but another client may be doing the same thing a the same time.

I made the changes below (not yet trying recordsAffected), but locking it by username, then checking to make sure the lock is there. The problem is, the client that makes the lock sees the lock immediately. Any other clients don't see the lock for 3-5 seconds. It's quite odd to me, as they are all looking at the same backend. I can settle for things not being immediate, but they need to be almost immediate. If two users click on the same record at the same millisecond, I understand that will be hard to catch, but I can literally click on it with one user, walk to the other side of the room and click on it from a different client with different user and both records open. How can client X know that there isn't a current operation happening in the table initiated by someone else, so I can just wait for that? Is there a way? I've been testing this one only one record in the callQueue table. It's such a simple table and a simple operation, which is why it's so frustrating that there is this lag issue. I have searched and I believe the answer is "no", but is there a "passthrough query" solution that can be used with accdb backend rather than only possible with ODBC backend?

I appreciate all the comments.

CODE
'check to see if locked
If Len(Me.lockedBy) > 0 Then
    MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
    Exit Sub
End If

'check to see if locked another way
If Len(DLookup("lockedBy", "callQueue", "[id]=" & Me.id)) > 0 Then
    MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
    Exit Sub
End If
          
'check to see if locked another way
SQL = "SELECT * FROM callQueue WHERE id=" & Me.id & ";"
Set callQueueData = CurrentDb.OpenRecordset(SQL)
If Not callQueueData.eof Then
    callQueueData.MoveFirst
    If Len(callQueueData!lockedBy) > 0 Then
        MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
        Exit Sub
    End If
End If
          
'lock record
SQL = "UPDATE callQueue SET lockedBy='" & Forms![infoStore].username & "' WHERE id=" & Me.id & ";"
CurrentDb.Execute SQL

'lock record another way
SQL = "SELECT * FROM callQueue WHERE id=" & Me.id & ";"
Set callQueueData = CurrentDb.OpenRecordset(SQL)
If Not callQueueData.eof Then
    callQueueData.MoveFirst
    callQueueData.Edit
    callQueueData!lockedBy = Forms![infoStore].username
    callQueueData.Update
End If

If DLookup("lockedBy", "callQueue", "id=" & Me.id) <> Forms![infoStore].username Then
    MsgBox "This record is locked by another user.", vbInformation + vbOKOnly, ""
    Exit Sub
End If
Go to the top of the page
 
orange999
post Sep 13 2017, 05:58 PM
Post#9



Posts: 1,678
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


This may be totally irrelevant, but perhaps some adaptation may be useful.
If you have a number of representatives working simultaneously with a common call queue, is there a way to identify some aspect of the call(s) in the queue such that they "belong" to one of the reps.

For example if a call is identified by time could you assign calls to reps based on the time of the call in the queue. That is calls with times such as 5:44:14 gets assigned to Rep1 --based on the seconds on the queued record 00 thru 15 goes to rep1
16 thru 30 goes to rep2....
There may be some other aspect/attribute that could serve a similar purpose-- especially if the reps are in the same room.

Sometimes a change in process can have benefits.

--------------------
Good luck with your project!
Go to the top of the page
 
enjoyjoshua
post Sep 13 2017, 06:11 PM
Post#10



Posts: 8
Joined: 13-September 17



orange999, I'm open to it, but there are times that we have only one rep, sometimes, 2, sometimes up to 4.

It all depends on the time of day and how busy we are and how many are scheduled.

They also all answer calls as well, so they are working this queue for outgoing calls in between taking incoming calls.

User A may be stuck on the phone for 10 minutes and there's no way for Access to know that, so whoever is available soonest needs to work the call queue, as these are just quick calls and they are time-sensitive.

I could run some timer logic or some kind of round-robin system, but just making this work the way it's supposed to is the best option.

You do give me an idea though. A delete action seems to be effective immediately for the whole system. The update actions are what are having a lag. Perhaps a solution would be to copy the record to a local table on the client and delete it from the linked table when the rep is taking ownership of it. Of course then, other clients lose visibility.

Still, if there's a way for the original system to work, I'd prefer to figure it out as that would be the simplest solution.

Thank you!




Go to the top of the page
 
PhilS
post Sep 14 2017, 03:00 AM
Post#11



Posts: 365
Joined: 26-May 15
From: The middle of Germany


QUOTE
In your suggestion to implement recordsAffected, do you have a suggestion on how to effectively check for the lock. The first half of my code on the OP is checking for a lock. Using that method, I'm experiencing the same issues when using a lockedBy field rather than an isLocked field. Which I guess makes sense.

The problem here is really the lock check, rather than the lock itself.

Well, my advice was to do both in one step.

SQL = "UPDATE callQueue SET lockedBy='" & Forms![infoStore].username & "' WHERE id=" & Me.id & " AND lockedBy IS NULL;"

If after executing this statement RecordsAffected is 1 you have got the lock. If it is 0, someone else locked the record before.

--------------------
The shocking revelation about digital signatures for accdb files.
Go to the top of the page
 
enjoyjoshua
post Sep 14 2017, 01:56 PM
Post#12



Posts: 8
Joined: 13-September 17



Thank you PhilS uarulez2.gif

It's working on initial testing. I'll report back if there are issues. Thanks!
Go to the top of the page
 
enjoyjoshua
post Sep 16 2017, 07:59 AM
Post#13



Posts: 8
Joined: 13-September 17



Two days in and no problems at all. Checking against currentDB.recordsAffected after an SQL statement that does both the check and the lock simultaneously was the solution.

Thanks again everyone!

CODE
Set db = CurrentDb

SQL = "UPDATE callQueue SET lockedBy='" & Forms![infoStore].username & "' WHERE id=" & Me.id & " AND nz(lockedBy,'')='';"
db.Execute SQL

If db.RecordsAffected = 0 Then
    MsgBox "This record is locked by " & DLookup("lockedBy", "callQueue", "id=" & Me.id) & ".", vbInformation + vbOKOnly, ""
    Exit Sub
End If
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2017 - 10:20 AM