SQL 链接访问数据库“记录已被另一个用户更改”

Linked Access DB "record has been changed by another user"

提问by ChristianLinnell

I'm maintaining a multiuser Access 2000 DB linked to an MSSQL2000 database, not written by me.

我正在维护一个链接到 MSSQL2000 数据库的多用户 Access 2000 DB，不是我写的。

The database design is very poor, so you'll have to bear with me.

数据库设计很差，所以你必须忍受我。

On the 'Customer' form there's a 'Customer_ID' field that by default needs to get the next available customer ID, but the user has the option of overriding this choice with an existing customer ID.

在“Customer”表单上有一个“Customer_ID”字段，默认情况下需要获取下一个可用的客户 ID，但用户可以选择使用现有客户 ID 覆盖此选择。

Now, the Customer_ID field is not the PK of the Customer table. It's also not unique.

现在，Customer_ID 字段不是 Customer 表的 PK。它也不是独一无二的。

If a customer calls twice to submit a job, the table will get two records, each with the same customer information, and the same customer ID.

如果客户调用两次提交作业，该表将获得两条记录，每条记录具有相同的客户信息和相同的客户 ID。

If a user creates a new ticket, Access does a quick lookup for the next available customer ID and fills it in. But it doesn't save the record. Obviously a problem - two users editing have to keep track of each others' work so they don't dupe up a customer ID.

如果用户创建了新票证，Access 会快速查找下一个可用的客户 ID 并填写它。但它不会保存记录。显然是一个问题——编辑的两个用户必须跟踪彼此的工作，这样他们就不会欺骗客户 ID。

So I want to modify the "new record" button so it saves the ticket right after creating a new one.

所以我想修改“新记录”按钮，以便在创建新记录后立即保存票证。

Problem is, when I test the change, I get "This record has been changed by another user since you started editing it".

问题是，当我测试更改时，我收到“此记录自您开始编辑以来已被其他用户更改”。

Definitely no other users on the DB. The 'other user' was presumably my forced save.

数据库上绝对没有其他用户。“其他用户”大概是我强制保存的。

Any ideas?

有任何想法吗？

回答by Istari

Take a look at your linked table in SQL Server 2000. Does it have a field containing the bit datatype? Access will give you this error message in a linked table scenario if you have a bit field which does not have a default value.

看看您在 SQL Server 2000 中的链接表。它是否有包含位数据类型的字段？如果您有一个没有默认值的位字段，Access 将在链接表方案中为您提供此错误消息。

It might not be what's wrong in your case, but I've experienced the same in an Access 2007 database and tracked the problem to a bit field with no default value.

您的情况可能不是问题，但我在 Access 2007 数据库中遇到了同样的问题，并将问题跟踪到没有默认值的位字段。

回答by Birger

I have seen this behaviour before and this fixed it for me:

我以前见过这种行为，这为我解决了这个问题：

Try to add a TimeStamp field to the table (just add this field and update your linked tables. You do not need to fill this field with any kind of data).

尝试向表中添加一个 TimeStamp 字段（只需添加此字段并更新您的链接表。您不需要用任何类型的数据填充此字段）。

回答by David-W-Fenton

The error you're getting usually happens when:

您遇到的错误通常发生在：

1. you are editing a record in a form and the form is dirty (i.e., edits not saved),

1. 您正在编辑表单中的记录并且表单是脏的（即未保存编辑），

AND

和

1. you run code that uses DAO or ADO to run SQL to update the same record.

2. 您运行的代码使用 DAO 或 ADO 来运行 SQL 以更新相同的记录。

To Jet, that's two "users", because it's two different edit operations. The underlying table has been updated by the SQL update, while the data in the form buffer is now out of date.

对 Jet 来说，这是两个“用户”，因为这是两个不同的编辑操作。SQL 更新已更新了基础表，而表单缓冲区中的数据现在已过期。

The usual solution is to force a save before running the SQL update:

通常的解决方案是在运行 SQL 更新之前强制保存：

  If Me.Dirty Then
     Me.Dirty = False
  End If
  [run your SQL update here]

But if you're using forms to edit the record, you ought to do all updates in the form, rather than resorting to SQL to do the update.

但是如果您使用表单来编辑记录，您应该在表单中进行所有更新，而不是求助于 SQL 来进行更新。

The situation you describe with generating your own sequence ought to be done in this fashion:

您描述的生成您自己的序列的情况应该以这种方式完成：

1. user hits NEW RECORD button.

2. calc next sequence value and store it in a variable.

3. insert a new record with that sequence value via a SQL INSERT.

1. 用户点击新记录按钮。

2. 计算下一个序列值并将其存储在变量中。

3. 通过 SQL INSERT 插入具有该序列值的新记录。

4a. if your form is bound to all the records in the table, requery the data editing form (assuming the NEW RECORD button is on the form where users edit the data), and use bookmark navigation to move to the new record with the sequence value that you stored in the variable in step 2.

4a. 如果你的表单绑定了表中的所有记录，重新查询数据编辑表单（假设NEW RECORD按钮在用户编辑数据的表单上），并使用书签导航移动到具有序列值的新记录您在步骤 2 中存储在变量中。

4b. If your form is notbound to all the records (as it shouldn't be if it's a well-designed database), you would just change the recordsource of the form to load only the new record.

4b. 如果您的表单未绑定到所有记录（因为它不应该是设计良好的数据库），您只需更改表单的记录源以仅加载新记录。

Another alternative is to avoid the SQL INSERT and requery (or resetting the recordsource) and simply add a new record in the existing form, set the sequence field to the new value and immediately save the record.

另一种选择是避免 SQL INSERT 和重新查询（或重置记录源），只需在现有表单中添加新记录，将序列字段设置为新值并立即保存记录。

The key point is that for this to work in a multi-user environment, the record has to be saved just as soon as the sequence value is assigned to it -- you can't leave the record hanging out there unsaved, because that means the identical sequence value is available to other users, which is just asking for a disaster.

关键是要使其在多用户环境中工作，必须在将序列值分配给记录后立即保存记录——您不能将记录挂在那里未保存，因为这意味着其他用户可以使用相同的序列值，这只是在要求灾难。

回答by vol7ron

This is an old question, which I've come across from Google, so I will submit my answer.

这是一个老问题，我从谷歌遇到过，所以我会提交我的答案。

In the ODBC driver, make sure to turn on row versioning. If the table is already in Access, you'll have to drop it and re-link to the source table.

在 ODBC 驱动程序中，确保打开行版本控制。如果该表已在 Access 中，则必须删除它并重新链接到源表。

You should be able to tell if you have row versioning enabled because Access should add a column to your table called xmin.

您应该能够判断是否启用了行版本控制，因为 Access 应该向您的表中添加一个名为xmin.

回答by great_llama

I would keep track of whether the user has overridden the new customer_id with a value of their own. If they haven't, then your app should be able to check for a duplicate right before saving and just self-increment again, and the user didn't mind taking the default. Maybe even some indicator to the user that you had to automatically choose a different value.

我会跟踪用户是否用他们自己的值覆盖了新的 customer_id。如果没有，那么您的应用程序应该能够在保存之前检查重复项并再次自增，并且用户不介意采用默认值。甚至可能向用户发出一些指示，表明您必须自动选择不同的值。

回答by Tejbir Singh

I also had same problem. I was trying to update in a table using Spring MVC and hibernate. In my case the version column in the table contains a value more than 1( i.e. 3) however the update information in my update query had version value 1.

我也有同样的问题。我试图使用 Spring MVC 和 hibernate 在表中进行更新。在我的情况下，表中的版本列包含一个大于 1 的值（即 3），但是我的更新查询中的更新信息的版本值为 1。

回答by Dilbert

Our problems was that the access front end was trying to save an int (yes/no) into a mssql bit (0/1) field. Changing the mssql database to int fields worked like a charm.

我们的问题是访问前端试图将 int（是/否）保存到 mssql 位（0/1）字段中。将 mssql 数据库更改为 int 字段就像一个魅力。

回答by koriander

I just came accross another situation that generates this error. In a mysql table I had two date columns which had initially default value '0000-00-00'. Later it was changed to default NULL but many rows kept the value '0000-00-00'. I had to manually reset the values to NULL to stop the error.

我刚刚遇到了另一种产生此错误的情况。在 mysql 表中，我有两个日期列，它们的初始默认值是“0000-00-00”。后来它更改为默认 NULL，但许多行保持值 '0000-00-00'。我不得不手动将值重置为 NULL 以停止错误。

It took a lot of time to figure out what was trigering the error, HTH someone else.

花了很多时间才弄清楚是什么触发了错误，HTH 其他人。

回答by Dead Red

This error can also be thrown if the SQL Server table contains a datetime2 column (in my case with the default value of sysdatetime()). Changing the datatype back to datetime default current_timestamp stops the error.

如果 SQL Server 表包含 datetime2 列（在我的情况下为 sysdatetime() 的默认值），也可能引发此错误。将数据类型改回 datetime 默认 current_timestamp 会停止错误。

回答by Dennison Vincent

I've been back and forth to this post and a few others to find a resolve for this behaviour. I have a MS Access database linked to MySQL database. The problem was caused by an existing BeforeUpdate Trigger in MySQL phpmyadmin. In phpmyadmin go to the Structure of the table and at the bottom of the screen you would see the Triggers.

我一直在这篇文章和其他一些帖子中来回寻找解决这种行为的方法。我有一个链接到 MySQL 数据库的 MS Access 数据库。该问题是由 MySQL phpmyadmin 中现有的 BeforeUpdate 触发器引起的。在 phpmyadmin 中，转到表的结构，在屏幕底部您会看到触发器。