重要的Access Client / Server提示 [英] Important Access Client/Server tip
问题描述
我刚刚得到报酬为一个客户解决这个问题，所以我可能会通过分享这个秘密来阻碍未来的商机，但是涨潮会上涨
all船，所以...我在许多Access C / S
应用程序中看到了这个神秘的问题。通常一些随机的GUI或工作流程更改会产生问题，
但至少对我的一个客户来说，问题是长期存在的。
您有一个Access表单绑定到链接的SQL Server表。你试图从Access中保存一个
记录，并且它会挂起，最终可能会出现错误
，或者可能直到你杀死Access或终止
服务器端（无论Access中的超时设置如何）。在这个
锁定期间，有一个SQL Server进程阻塞了另一个 - 从同一客户端工作站的
开始。这个问题通常是断断续续的，并且难以重现。
当然，如果2个进程在服务器端相互阻塞，那么SQL
服务器会检测到它是一个死锁，然后选择其中一个失败，但在这种情况下，在这个案例中，我们发现我们有进程A（一个SELECT查询返回行到
Access）阻止进程B（更新）在服务器上，并且挂起
等待进程B继续从进程A读取行，
因此允许A完成并停止阻止进程B.
事实证明，进程A总是查询组合框或列表框，
和答案结果是总是强制组合和列表框
通过在表单加载时立即读取ListCount属性来填充，
以及每次之后一个组合框被重新获得或者其行源被更改。
Presto - 问题解决了。
Steve Jorgensen写道：我刚收到一个客户解决这个问题的报酬，所以我可能会通过分享这个秘密来阻碍未来的商机，但是一个上升的潮流漂浮在所有船只上，所以......我现在在许多Access C / S应用程序中看到了这个神秘的问题。通常一些随机的GUI
或工作流程更改会产生问题，但对于我的至少一个客户来说，问题是长期的。
您有一个绑定到链接的Access表单SQL Server表。你尝试从Access保存一条记录，并且它会挂起，最终可能会出现错误，或者可能直到你杀死Access或从服务器端终止
连接（无论如何）
Access中的超时设置。在这个锁定期间，有一个SQL Server进程阻止另一个 - 都是从同一个客户端工作站启动的。
这个问题通常是间歇性的，很难重现。
当然，如果2个进程在服务器端相互阻塞，SQL Server会将其检测为死锁，并选择其中一个失败，但在这种情况下，事实证明我们有一个进程A（一个
SELECT查询返回行到Access）阻塞进程B（一个更新）
在服务器上，而Access挂起等待进程B才会继续读取来自进程A，从而允许A完成并停止阻塞进程B.
事实证明进程A总是查询组合框或
列表框，并且答案结果是总是强制组合和
列表框通过在表单加载时立即读取ListCount属性来完全填充，并且在每次组合框被重新获取或具有其行之后来源改变了。 Presto - 问题解决了。
如果使用List / Combo的传递和
也可以指定NO LOCK防止这个问题。
-
我没有查看电子邮件帐户附上
到此消息。发送给... ...
在Hunter dot com的RBrandt
2005年2月15日星期二15:44:15 -0600，" Rick Brandt < ri ********* @ hotmail.com>
写道：
Steve Jorgensen写道：我刚收到一个客户解决这个问题的报酬，所以我可能会通过分享这个秘密来阻碍未来的商机，但是一个上升的潮流漂浮在所有的船上，所以......我已经现在在许多Access C / S应用程序中看到了这个神秘的问题。通常一些随机的GUI
或工作流程更改会产生问题，但对于我的至少一个客户来说，问题是长期的。
您有一个绑定到链接的Access表单SQL Server表。你尝试从Access保存一条记录，并且它会挂起，最终可能会出现错误，或者可能直到你杀死Access或从服务器端终止
连接（无论如何）
Access中的超时设置。在这个锁定期间，有一个SQL Server进程阻止另一个 - 都是从同一个客户端工作站启动的。
这个问题通常是间歇性的，很难重现。
当然，如果2个进程在服务器端相互阻塞，SQL Server会将其检测为死锁，并选择其中一个失败，但在这种情况下，事实证明我们有一个进程A（一个
SELECT查询返回行到Access）阻塞进程B（一个更新）
在服务器上，而Access挂起等待进程B才会继续读取来自进程A，从而允许A完成并停止阻塞进程B.
事实证明进程A总是查询组合框或
列表框，并且答案结果是总是强制组合和
列表框通过在表单加载时立即读取ListCount属性来完全填充，并且在每次组合框被重新获取或具有其行之后来源改变了。 Presto - 问题解决了。
如果使用List / Combo的传递和
也可以防止问题，也可以指定NO LOCK。
你在哪里指定NO LOCK？指定No Locks在表格上肯定
没有帮助。
" Rick Brandt" < RI ********* @ hotmail.com>在消息中写道
news：37 ************* @ individual.net ...Steve Jorgensen写道：
如果使用List / Combo
和
的传递也可以指定NO LOCK，这也可以防止出现问题。
我可以看到使用NO LOCK提示的两个潜在问题。首先，它只是SQL Server支持的
。其次，结果集变为只读 - 所以你
以后不能重复使用相同的程序进行记录更新功能。
虽然它涉及编写更多代码，但我认为更好的方法可能是使用
回调函数和仅向前记录集来填充列表和组合
框。
I just got paid to solve this problem for one client, so I might be impeding
future business opportunities by sharing this secret, but a rising tide floats
all boats, so... I''ve seen this mysterious problem now in many Access C/S
applications. Usually some random GUI or workflow changes make the problem,
but for at least one of my clients, the problem was chronic.
You have an Access form bound to a linked SQL Server table. You try to save a
record from Access, and it hangs, possibly timing out with an error
eventually, or possibly until you kill Access or kill the connection from the
server-side (regardless of the timeout setting in Access). During this
lock-up, there is one SQL Server process blocking another - both intiated from
the same client workstation. The problem is usually intermittent, and hard to
reproduce.
Of course, if the 2 processes were blocking each other on the server-side, SQL
Server would detect that as a deadlock, and pick one of them to fail, but in
this case, it turns out we have process A (a SELECT query returning rows to
Access) blocking process B (an update) on the server, and Access hanging
waiting for process B before it will continue reading rows from process A,
thus allowing A to complete and stop blocking process B.
It turns out that process A is always the query for a combo box or list box,
and the answer turns out to be to always force combo and list boxes to
populate completely by reading the ListCount property immedately on form load,
and after every time a combo box is requeried or has its rowsource changed.
Presto - problem solved.
Steve Jorgensen wrote:I just got paid to solve this problem for one client, so I might be
impeding future business opportunities by sharing this secret, but a
rising tide floats all boats, so... I''ve seen this mysterious
problem now in many Access C/S applications. Usually some random GUI
or workflow changes make the problem, but for at least one of my
clients, the problem was chronic.
You have an Access form bound to a linked SQL Server table. You try
to save a record from Access, and it hangs, possibly timing out with
an error eventually, or possibly until you kill Access or kill the
connection from the server-side (regardless of the timeout setting in
Access). During this lock-up, there is one SQL Server process
blocking another - both intiated from the same client workstation.
The problem is usually intermittent, and hard to reproduce.
Of course, if the 2 processes were blocking each other on the
server-side, SQL Server would detect that as a deadlock, and pick one
of them to fail, but in this case, it turns out we have process A (a
SELECT query returning rows to Access) blocking process B (an update)
on the server, and Access hanging waiting for process B before it
will continue reading rows from process A, thus allowing A to
complete and stop blocking process B.
It turns out that process A is always the query for a combo box or
list box, and the answer turns out to be to always force combo and
list boxes to populate completely by reading the ListCount property
immedately on form load, and after every time a combo box is
requeried or has its rowsource changed. Presto - problem solved.
You can also specify NO LOCK if using a pass-through for the List/Combo and
that will also prevent the problem.
--
I don''t check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Tue, 15 Feb 2005 15:44:15 -0600, "Rick Brandt" <ri*********@hotmail.com>
wrote:
Steve Jorgensen wrote:I just got paid to solve this problem for one client, so I might be
impeding future business opportunities by sharing this secret, but a
rising tide floats all boats, so... I''ve seen this mysterious
problem now in many Access C/S applications. Usually some random GUI
or workflow changes make the problem, but for at least one of my
clients, the problem was chronic.
You have an Access form bound to a linked SQL Server table. You try
to save a record from Access, and it hangs, possibly timing out with
an error eventually, or possibly until you kill Access or kill the
connection from the server-side (regardless of the timeout setting in
Access). During this lock-up, there is one SQL Server process
blocking another - both intiated from the same client workstation.
The problem is usually intermittent, and hard to reproduce.
Of course, if the 2 processes were blocking each other on the
server-side, SQL Server would detect that as a deadlock, and pick one
of them to fail, but in this case, it turns out we have process A (a
SELECT query returning rows to Access) blocking process B (an update)
on the server, and Access hanging waiting for process B before it
will continue reading rows from process A, thus allowing A to
complete and stop blocking process B.
It turns out that process A is always the query for a combo box or
list box, and the answer turns out to be to always force combo and
list boxes to populate completely by reading the ListCount property
immedately on form load, and after every time a combo box is
requeried or has its rowsource changed. Presto - problem solved.
You can also specify NO LOCK if using a pass-through for the List/Combo and
that will also prevent the problem.
Where do you specify NO LOCK? Specifying "No Locks" on the form definitely
does not help.
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:37*************@individual.net...Steve Jorgensen wrote:
You can also specify NO LOCK if using a pass-through for the List/Combo
and
that will also prevent the problem.
I can see two potential problems with using a NO LOCK hint. First, it''s only
supported by SQL Server. Second, the resultset becomes read-only - so you
can''t re-use the same procedure for a records-updating function later on.
Although it involves writing more code i think a better way might be to use
callback functions and a forward-only recordset to populate lists and combo
boxes.
这篇关于重要的Access Client / Server提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
