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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Record Collisions When Using Dlookup, Access 2010    
 
   
wbouton
post Feb 22 2018, 04:26 PM
Post#1



Posts: 24
Joined: 20-February 04



I'm using Dlookup code to access a record in a local query. The query is linked to a table in a different database.

There is only one record in the table, which is a daily message. ~500 users have a local front-end and access the back-end daily message record through the linked query each time it scrolls across the screen (about once every 30 seconds).

All of this works fine, except when there is a collision where more than one user tries to access this same daily message record simultaneously. So, I've tried several things, including error handling to retry access to the record until it succeeds, displaying an alternate "updating" message until the next loop, etc. The error message "another user has this record open" has gone away after using error handling, but the app just hangs and never recovers.

A couple of questions:

1) Why are there collisions at all when users are accessing the record using a read-only query? Is there something I should be doing differently when accessing the same record by multiple users simultaneously or is it not possible to do?

2) When there is a collision of user requests, why would the client app permanently hang when using error handling to repeat the record access request until successful (which is only a second or so). Btw, this error handling methodology works when testing from a client machine and opening the back-end record in exclusive mode. The daily message on the client app stops scrolling when it's time to fetch the daily message again (or gives an alternate message depending how I set it up) until access is restored to the back-end DB, at which point it seamlessly loads the message and continues on. It does not hang the client app. It only stops the scrolling message, leaving all other app functionality intact, until access to the back-end record is available to be queried.

Thanks in advance for any ideas or solutions.

Wade.
Go to the top of the page
 
theDBguy
post Feb 22 2018, 04:30 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,951
Joined: 19-June 07
From: SunnySandyEggo


Hi Wade,

It wasn't clear to me but does each user have their own copy of the local query? Or, is everyone using the same exact/actual file where the query is stored?
Go to the top of the page
 
wbouton
post Feb 22 2018, 04:41 PM
Post#3



Posts: 24
Joined: 20-February 04



Each client has their own copy of the query that queries the back-end table.
Go to the top of the page
 
theDBguy
post Feb 22 2018, 04:43 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,951
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for the clarification. How can you tell when there's a "collision?" Do you get an error message? If so, what does it say?
Go to the top of the page
 
zaxbat
post Feb 22 2018, 04:51 PM
Post#5



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Found this in another post....maybe worth looking at...

strSQL = "SELECT Count(CaseNo) As MyCount FROM tblAddresses IN 'D:\Administration\Remote.mdb' WHERE CaseNo = '" & Me.txtCaseNo & "'"


I only use DLOOKUP when im feeling really lazy....it is kind of a pig on efficiency
This post has been edited by zaxbat: Feb 22 2018, 04:56 PM
Go to the top of the page
 
wbouton
post Feb 22 2018, 04:56 PM
Post#6



Posts: 24
Joined: 20-February 04



I can't reproduce exactly, since our users aren't online right now, but it was something like "table locked by user on machine xxxx". I guess I don't understand the fact that a record or table is locked when using a read-only query. Is the table record locked when using a read-only query?
Go to the top of the page
 
zaxbat
post Feb 22 2018, 04:59 PM
Post#7



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I use lock optimistic shared read only.....but system still has to wait when somebody else is accessing it.....do you pause a second in your error handler before hitting the query again? Might help
Go to the top of the page
 
theDBguy
post Feb 22 2018, 05:01 PM
Post#8


Access Wiki and Forums Moderator
Posts: 73,951
Joined: 19-June 07
From: SunnySandyEggo


A table is said to be "locked" depending on what action is being performed at the time. Let's say you have a query "reading" a table and another routine is making changes to the data in the same table, then the table would not be locked in this case because only one of the two connections is modifying the table. However, if one action has a "read-only" connection to the table and another routine is trying to delete the table or modify its design structure, then the table would be considered "locked."

Does that help?
Go to the top of the page
 
wbouton
post Feb 22 2018, 05:02 PM
Post#9



Posts: 24
Joined: 20-February 04



So, you're saying that if I use an SQL statement directly in the VB code (instead of calling a query) that might prevent the record locking I'm running into?
Go to the top of the page
 
zaxbat
post Feb 22 2018, 05:04 PM
Post#10



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I'm thinking it is a faster way to access so may open the bottleneck....if each user is in and out faster it will cut down on the collisions



Hey, by the way....last I heard Access it not recommended for more than 50 user networks
This post has been edited by zaxbat: Feb 22 2018, 05:07 PM
Go to the top of the page
 
theDBguy
post Feb 22 2018, 05:04 PM
Post#11


Access Wiki and Forums Moderator
Posts: 73,951
Joined: 19-June 07
From: SunnySandyEggo


No, it is not what I was saying. Whether you use a query or a SQL statement or a recordset in VBA, you will be making a connection to the table. This connection is required whether you just want to read the data or change it.
Go to the top of the page
 
wbouton
post Feb 22 2018, 05:07 PM
Post#12



Posts: 24
Joined: 20-February 04



Right, but as far as I know no one is opening the record in edit mode, since every client has a copy of the same query. The properties of the query are:

Record Locks - No Locks
Recordset Type - Snapshot
ODBC Timeout - 30000
Go to the top of the page
 
wbouton
post Feb 22 2018, 05:10 PM
Post#13



Posts: 24
Joined: 20-February 04



Thanks. I gotcha, you're saying it's more efficient to do through SQL code, instead of calling query. Something I'll look at changing if I can get the record locking issue solved.
Go to the top of the page
 
zaxbat
post Feb 22 2018, 05:12 PM
Post#14



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Alternately, you could get really creative and make 10 copies of the one-record table with filename suffixes 0-10 and have the updater service all of them on update event.
Then for the 500 users have a random function that selects which table to access.....they all have the same data but the traffic per file (collision possibility) could be cut by 90%
This post has been edited by zaxbat: Feb 22 2018, 05:16 PM
Go to the top of the page
 
theDBguy
post Feb 22 2018, 05:12 PM
Post#15


Access Wiki and Forums Moderator
Posts: 73,951
Joined: 19-June 07
From: SunnySandyEggo


Hi Wade,

Without seeing your application, I can't tell you what may be causing the lock. As far as I know, a table won't necessarily be locked just because more than one user is reading and changing the data at the same time.

In other words, one user reading and another person changing = no lock

Or, one user changing and another user also changing = no lock; however, the second user will get a notice the data has changed, which gives them an option to refresh their data before changing it

Hope it helps...
Go to the top of the page
 
wbouton
post Feb 22 2018, 05:16 PM
Post#16



Posts: 24
Joined: 20-February 04



But in reality, no one is editing the record. Everyone is just taking a snapshot of the record every 30 seconds or so. I don't understand why the record would ever even have the opportunity to be locked, unless I'm not understanding how Dlookup works or some settings with the query is set incorrectly.
Go to the top of the page
 
zaxbat
post Feb 22 2018, 05:24 PM
Post#17



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If each user refreshes that data each 30 seconds....does'nt it follow that the data may be updated each 30 seconds??? No reason to refresh each 30 seconds unless we reasonably expect it to be updated each 30 seconds. And if so...somebody (or some service) is accessing it fairly often.
Go to the top of the page
 
wbouton
post Feb 22 2018, 05:28 PM
Post#18



Posts: 24
Joined: 20-February 04



Here's something else interesting: when I open several client apps using shift key and run the query directly in datasheet view and leave open, I never receive an error. The query just displays a copy of the same record in each client. Here is the code that requests the data.

If Not bInit Then
Static t As TextBox
Set t = Me.tb_Scroller
t.Value = DLookup("DMessage", "DMQueryFinal")
'Set internal leftmargin to match width
t.LeftMargin = t.Width - 1
bInit = True
End If

Dim lngStep As Long
lngStep = 125

If t.LeftMargin - lngStep >= 1 Then
t.LeftMargin = t.LeftMargin - lngStep
Else
If Len(t.Value) > 1 Then
t.Value = Mid(t.Value, 2)
Else
bInit = False
End If
End If
Go to the top of the page
 
wbouton
post Feb 22 2018, 05:38 PM
Post#19



Posts: 24
Joined: 20-February 04



The reason is so that an admin can login to another tool and update the message and have it disseminate the next time the client requests the message (within 30 seconds). If I can't do the instant update, I will continue to just have the user receive updates only when they restart their apps, like I do now.

I still have this record locking problem even when the message isn't changed. I can see the DB lock file being created and then deleted over and over again as each user requests a new copy of the message. I'm assuming that when a client makes a request when there is already a DB lock file created it gives the error.
Go to the top of the page
 
theDBguy
post Feb 22 2018, 06:05 PM
Post#20


Access Wiki and Forums Moderator
Posts: 73,951
Joined: 19-June 07
From: SunnySandyEggo


Hi Wade,

Re: "I'm assuming that when a client makes a request when there is already a DB lock file created it gives the error."

I think this may be an incorrect assumption. The lock file just keeps track of who is connecting to the database. It should not cause a lock error.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2018 - 07:15 AM