|
|
After much searching for an answer, I thought I would post this question in
the hope someone will lead this old horse to the water. I am using Access
2003 runtime for distribution.
I have a BE MDB with FE MDE on over a dozen workstations. Each workstation
is scanning bar codes and updating two tables. One field in MASTER is
updated at each scan (incrementing the value of that field with the item
value just scanned). A record is added to the DETAIL if the item does not
exist or updating an existing record for that item. There are up to twelve
workstations scanning items for hours. The Master table record is updated at
each scan on each workstation.
Here is the code snippet:
RS1.Edit
RS1.Fields("Scanned_Value").Value = Round(RS1.Fields("Scanned_Value").Value +
_ Round((Me.numPrice_Special * Me.txtQuantity), 2), 2)
RS1.UPDATE
When .Edit is executed it locks that record. I trap the locking error and
retry. The entire system locks up in my loop. Should I try a different
approach to updating the fields? Are there settings that I am not aware of.
Any help is welcome.
Earl G
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200811/1
|
|
While it shouldn't be necessary, what happens if you assign the calculation
to a variable?
Dim sngValue As Single
sngValue = Round(RS1.Fields("Scanned_Value").Value +_
Round((Me.numPrice_Special * Me.txtQuantity), 2), 2)
RS1.Edit
RS1.Fields("Scanned_Value").Value = sngValue
RS1.UPDATE
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"egarber via AccessMonster.com" <u10839[ at ]uwe> wrote in message
news:8dbc2693f3e86[ at ]uwe...
[Quoted Text] > After much searching for an answer, I thought I would post this question > in > the hope someone will lead this old horse to the water. I am using Access > 2003 runtime for distribution. > I have a BE MDB with FE MDE on over a dozen workstations. Each > workstation > is scanning bar codes and updating two tables. One field in MASTER is > updated at each scan (incrementing the value of that field with the item > value just scanned). A record is added to the DETAIL if the item does not > exist or updating an existing record for that item. There are up to > twelve > workstations scanning items for hours. The Master table record is updated > at > each scan on each workstation. > > Here is the code snippet: > > RS1.Edit > RS1.Fields("Scanned_Value").Value = > Round(RS1.Fields("Scanned_Value").Value + > _ Round((Me.numPrice_Special * Me.txtQuantity), 2), 2) > RS1.UPDATE > > When .Edit is executed it locks that record. I trap the locking error and > retry. The entire system locks up in my loop. Should I try a different > approach to updating the fields? Are there settings that I am not aware > of. > > Any help is welcome. > > Earl G > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200811/1>
|
|
Thanks for the reply Douglas.
In my testing, I am 99.99% sure that the issue is with the Jet Database
locking mechanism when .edit is issued. It is released when .update is
issued. I don't think that using a variable will shave any significant time
off the process.
The error trap for "record in use" takes 4-5 seconds before it triggers the
error.
Earl
Douglas J. Steele wrote:
[Quoted Text] >While it shouldn't be necessary, what happens if you assign the calculation
>to a variable?
>
>Dim sngValue As Single
>
> sngValue = Round(RS1.Fields("Scanned_Value").Value +_
> Round((Me.numPrice_Special * Me.txtQuantity), 2), 2)
> RS1.Edit
> RS1.Fields("Scanned_Value").Value = sngValue
> RS1.UPDATE
>
>> After much searching for an answer, I thought I would post this question
>> in
>[quoted text clipped - 27 lines]
>>
>> Earl G
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200811/1
|
|
|
[Quoted Text] > The error trap for "record in use" takes 4-5 seconds before it triggers
> the error.
That can be changed: you can change the way Access/Jet handles
locking, and you can change the way Windows filesystem client/server
handles locking.
>When .Edit is executed it locks that record. I trap the locking error
> and retry. The entire system locks up in my loop. Should I try a
Are you saying that if a record is locked it never sees the unlock?
or only that you system locks up while it waits for the unlock?
(david)
"egarber via AccessMonster.com" <u10839[ at ]uwe> wrote in message
news:8dbc98222b1d3[ at ]uwe...
> Thanks for the reply Douglas.
> In my testing, I am 99.99% sure that the issue is with the Jet Database
> locking mechanism when .edit is issued. It is released when .update is
> issued. I don't think that using a variable will shave any significant
> time
> off the process.
> The error trap for "record in use" takes 4-5 seconds before it triggers
> the
> error.
>
> Earl
>
> Douglas J. Steele wrote:
>>While it shouldn't be necessary, what happens if you assign the
>>calculation
>>to a variable?
>>
>>Dim sngValue As Single
>>
>> sngValue = Round(RS1.Fields("Scanned_Value").Value +_
>> Round((Me.numPrice_Special * Me.txtQuantity), 2), 2)
>> RS1.Edit
>> RS1.Fields("Scanned_Value").Value = sngValue
>> RS1.UPDATE
>>
>>> After much searching for an answer, I thought I would post this question
>>> in
>>[quoted text clipped - 27 lines]
>>>
>>> Earl G
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-multiuser/200811/1
>
|
|
Each workstation gets caught in the retry loop while waiting for the first .
edit to be released by the .update.
I don't know, but it appears that the database is too slow and with many
workstations updating the same field... well it seems to overload the
database. This is just a guess. But past experience tells me that the
workstations should only be held up for an instant.
I would say yes, the record never gets unlocked.
Thanks for you time and insight.
Earl
david wrote:
[Quoted Text] >> The error trap for "record in use" takes 4-5 seconds before it triggers
>> the error.
>
>That can be changed: you can change the way Access/Jet handles
>locking, and you can change the way Windows filesystem client/server
>handles locking.
>
>>When .Edit is executed it locks that record. I trap the locking error
>> and retry. The entire system locks up in my loop. Should I try a
>
>Are you saying that if a record is locked it never sees the unlock?
>or only that you system locks up while it waits for the unlock?
>
>(david)
>
>> Thanks for the reply Douglas.
>> In my testing, I am 99.99% sure that the issue is with the Jet Database
>[quoted text clipped - 25 lines]
>>>>
>>>> Earl G
--
Message posted via http://www.accessmonster.com
|
|
Can't release the record until it has been written back to the database,
so do you have a flush after every update?
RS1.UPDATE
Application.DBEngine.Idle DAO.dbRefreshCache.
You may also wish to change some of the dbengine options
Application.DBEngine.SetOption LockRetry,20
Application.DBEngine.SetOption LockDelay,100
Windows now has a lock retry setting as well, so each Jet lock
retry can generate several Windows lock retries.
20 * 0.100 = 2 seconds if Windows fails each lock attempt, but
when Windows also waits for a couple of tries on each try, it takes
longer. (You can change that behaviour too).
Also perhaps
Application.DBEngine.SetOption ImplicitCommitSync,Yes
I can't try any of those today: SetOption is returning error 3001
on my PC.
The actual effect is a bit uncertain, even at the time the Access 97
help file was being released, changes had already been made to
both the Engine and the Operating System, and, as demonstrated with
the LockRetry/LockDelay, what you get could be unexpected.
By the way, claims to the contrary, SetOption was not a new method
in DAO 3.6:
http://office.microsoft.com/en-us/access/HP010321711033.aspx
"DAO version 3.6 provides a new way to modify default settings"
If they were giving away copies of new versions of Office, that would
just be an embarrassing mistake. Since they are selling Office, that
is false and misleading advertising. I can't believe how careless they
are about things like that.
(david)
"egarber via AccessMonster.com" <u10839[ at ]uwe> wrote in message
news:8de29e00ad21a[ at ]uwe...
[Quoted Text] > Each workstation gets caught in the retry loop while waiting for the first > . > edit to be released by the .update. > I don't know, but it appears that the database is too slow and with many > workstations updating the same field... well it seems to overload the > database. This is just a guess. But past experience tells me that the > workstations should only be held up for an instant. > > I would say yes, the record never gets unlocked. > > Thanks for you time and insight. > > Earl > > david wrote: >>> The error trap for "record in use" takes 4-5 seconds before it triggers >>> the error. >> >>That can be changed: you can change the way Access/Jet handles >>locking, and you can change the way Windows filesystem client/server >>handles locking. >> >>>When .Edit is executed it locks that record. I trap the locking error >>> and retry. The entire system locks up in my loop. Should I try a >> >>Are you saying that if a record is locked it never sees the unlock? >>or only that you system locks up while it waits for the unlock? >> >>(david) >> >>> Thanks for the reply Douglas. >>> In my testing, I am 99.99% sure that the issue is with the Jet Database >>[quoted text clipped - 25 lines] >>>>> >>>>> Earl G > > -- > Message posted via http://www.accessmonster.com>
|
|
Can't release the record until it has been written back to the database,
so do you have a flush after every update?
RS1.UPDATE
Application.DBEngine.Idle DAO.dbRefreshCache.
You may also wish to change some of the dbengine options
dao.SetOptionEnum.
Application.DBEngine.SetOption dao.SetOptionEnum.dbLockRetry,20
Application.DBEngine.SetOption dao.SetOptionEnum.dbLockDelay,100
Windows now has a lock retry setting as well, so each Jet lock
retry can generate several Windows lock retries.
20 * 0.100 = 2 seconds if Windows fails each lock attempt, but
when Windows also waits for a couple of tries on each try, it takes
longer. (You can change that behaviour too).
Also perhaps
Application.DBEngine.SetOption dao.SetOptionEnum.dbImplicitCommitSync,"Yes"
The actual effect is a bit uncertain, even at the time the Access 97
help file was being released, changes had already been made to
both the Engine and the Operating System, and, as demonstrated with
the LockRetry/LockDelay, what you get could be unexpected.
By the way, claims to the contrary, SetOption was not a new method
in DAO 3.6:
http://office.microsoft.com/en-us/access/HP010321711033.aspx
"DAO version 3.6 provides a new way to modify default settings"
If they were giving away copies of new versions of Office, that would
just be an embarrassing mistake. Since they are selling Office, that
is false and misleading advertising. I can't believe how careless they
are about things like that.
(david)
"egarber via AccessMonster.com" <u10839[ at ]uwe> wrote in message
news:8de29e00ad21a[ at ]uwe...
[Quoted Text] > Each workstation gets caught in the retry loop while waiting for the first > . > edit to be released by the .update. > I don't know, but it appears that the database is too slow and with many > workstations updating the same field... well it seems to overload the > database. This is just a guess. But past experience tells me that the > workstations should only be held up for an instant. > > I would say yes, the record never gets unlocked. > > Thanks for you time and insight. > > Earl > > david wrote: >>> The error trap for "record in use" takes 4-5 seconds before it triggers >>> the error. >> >>That can be changed: you can change the way Access/Jet handles >>locking, and you can change the way Windows filesystem client/server >>handles locking. >> >>>When .Edit is executed it locks that record. I trap the locking error >>> and retry. The entire system locks up in my loop. Should I try a >> >>Are you saying that if a record is locked it never sees the unlock? >>or only that you system locks up while it waits for the unlock? >> >>(david) >> >>> Thanks for the reply Douglas. >>> In my testing, I am 99.99% sure that the issue is with the Jet Database >>[quoted text clipped - 25 lines] >>>>> >>>>> Earl G > > -- > Message posted via http://www.accessmonster.com>
|
|
|