运行时错误"3197". MS Access 2010和SQL Server 2008R2 [英] run-time error '3197'. MS Access 2010 and SQL Server 2008R2

问题描述

我有一个用MS Access 2007-2010编写的应用程序，后端是SQL数据库.

I have an application written in MS Access 2007-2010 and a back end is an SQL database.

建立新数据库后，当我在Access报表中查看来自SQL的记录时，尝试编辑或输入新的输入结果，并出现运行时错误3197.

After building a new database, when I view the records from SQL in the Access report, trying to edit or enter new input results with a run-time error 3197.

具体错误为: 自您开始编辑以来，该记录已被其他用户更改

The specific error says: This record has been changed by another user since you started editing it

然后，当保存记录"显示为灰色时，我具有复制到剪贴板"和放置更改"选项. 单击拖放"更改会带来错误:

Then I have Copy to Clipboard and Drop Changes options, while the Save Record is grayed out. Clicking on the Drop changes brings the error:

运行时错误'3197" Microsoft Access数据库引擎停止了该过程，因为您和另一个用户试图同时更改相同的数据."

"Run-Time Error '3197' The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."

然后，我查看了SQL，并使用EXEC sp_who2'Active'查询了数据库，我发现当Access应用程序(用Access VBA编码)使用的用户为SUSPENDED时，我用于SQL的用户名是RUNNABLE.挂起的命令是SELECT).

I then looked in the SQL and queried the database with EXEC sp_who2 'Active' and I can see that the user name I use for SQL is RUNNABLE while the user used by the Access application (coded in Access VBA) is SUSPENDED (the command it is suspended on is SELECT).

我还有其他以相同方式构造的数据库，并且没有任何问题.当使用Access运行时，我已经检查了工作数据库中的sp_who2，并且被问题"数据库挂起的用户正在与此数据库一起睡觉.

I have other databases that are constructed the same way and work with no issues. I've checked sp_who2 on the working database when it's running with Access, and the user that is SUSPENDED for the "problematic" database, is sleeping with this one.

我已经检查了两个数据库的权限，并在安全性"->登录"中检查了用户的权限，但看不到我可以标记为问题的任何内容(也许其他人可以?).

I've checked the permissions for both databases, and checked the permissions for the users in Security--> Logins and don't see anything that I can flag as an issue (maybe someone else can?).

不确定为什么会发生这种情况，以及为什么数据库被SQL用户锁定并且不会让Access用户更新相关记录.

Not sure why this is happening and why the database is locked by the SQL user and won't let the Access user update the relevant records.

任何人都可以阐明这个问题吗?

Can anyone shed some light on this issue?

谢谢.

推荐答案

正如Remou所说，将所有位数据类型更改为smallint，并在Null起作用的地方填充0.我不需要将1更改为-1，因为我的字段处于未选中状态(= 0)的初始状态.

As Remou commented, changing all bit data types to smallint and populating with 0 where Null did the trick. I didn't need to change 1 to -1 as my fields where to be in the initial state of unticked (=0).

这篇关于运行时错误"3197". MS Access 2010和SQL Server 2008R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！