Linked Access DB“记录已被另一用户更改”
我正在维护一个链接到 MSSQL2000 数据库的多用户 Access 2000 DB，这不是我编写的。
数据库设计很差，所以你必须忍受我。
在“客户”表单上，有一个“Customer_ID”字段，默认情况下需要获取下一个可用的客户 ID，但用户可以选择使用现有客户 ID 覆盖此选择。
现在，Customer_ID 字段不是 Customer 表的 PK。 这也不是独一无二的。
如果客户两次调用提交作业，该表将获得两条记录，每条记录都有相同的客户信息和相同的客户 ID。
如果用户创建新票证，Access 会快速查找下一个可用的客户 ID 并填写它。但它不会保存记录。 显然是一个问题 - 两个编辑用户必须跟踪彼此的工作，这样他们就不会欺骗客户 ID。
所以我想修改“新记录”按钮，以便它在创建新记录后立即保存票据。
问题是，当我测试更改时，我得到“自从您开始编辑该记录以来，该记录已被其他用户更改”。
数据库上绝对没有其他用户。 “其他用户”大概是我的强制保存。
有任何想法吗？
I'm maintaining a multiuser Access 2000 DB linked to an MSSQL2000 database, not written by me.
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.
Now, the Customer_ID field is not the PK of the Customer table. It's also not unique.
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.
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.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
看一下 SQL Server 2000 中的链接表。它是否有一个包含 bit 数据类型的字段？ 如果您的位字段没有默认值，Access 将在链接表方案中向您显示此错误消息。
您的情况可能不是问题，但我在 Access 2007 数据库中也遇到过同样的情况，并将问题跟踪到一个没有默认值的位字段。
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.
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.
我以前见过这种行为，这为我修复了它：
尝试向表中添加一个时间戳字段（只需添加此字段并更新链接的表。您不需要使用任何类型的数据填充此字段）。
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).
您遇到的错误通常发生在以下情况：
并且
对于 Jet 来说，这是两个“用户”，因为这是两个不同的编辑操作。 基础表已通过 SQL 更新进行更新，而表单缓冲区中的数据现已过时。
通常的解决方案是在运行 SQL 更新之前强制保存：
但是如果您使用表单来编辑记录，则应该在表单中进行所有更新，而不是诉诸 SQL 来进行更新。
您描述的生成自己的序列的情况应该以这种方式完成：
RecordSource即可仅加载新记录。另一种替代方法是避免 SQL
INSERT和重新查询（或重置RecordSource），只需在现有表单中添加新记录，将序列字段设置为新值，然后立即保存记录。关键点是，要在多用户环境中工作，必须在为记录分配序列值后立即保存记录 - 您不能让记录悬在那里未保存，因为这意味着相同的序列值可供其他用户使用，这只会带来灾难。
The error you're getting usually happens when:
AND
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.
The usual solution is to force a save before running the SQL update:
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.
The situation you describe with generating your own sequence ought to be done in this fashion:
INSERT.RecordSourceof the form to load only the new record.Another alternative is to avoid the SQL
INSERTand requery (or resetting theRecordSource) and simply add a new record in the existing form, set the sequence field to the new value and immediately save the record.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.
这是一个老问题，我是从谷歌上遇到的，所以我会提交我的答案。
在 ODBC 驱动程序中，确保打开行版本控制。 如果该表已在 Access 中，则必须删除它并重新链接到源表。
您应该能够判断是否启用了行版本控制，因为 Access 应该向表中添加一个名为
xmin的列。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.
You should be able to tell if you have row versioning enabled because Access should add a column to your table called
xmin.我会跟踪用户是否用自己的值覆盖了新的 customer_id。 如果没有，那么您的应用程序应该能够在保存之前检查重复项，然后再次自增量，并且用户不介意采用默认值。 甚至可能向用户发出一些指示，表明您必须自动选择不同的值。
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.
我反复阅读了这篇文章和其他一些文章，以找到解决此行为的方法。 我有一个链接到 MySQL 数据库的 MS Access 数据库。 该问题是由 MySQL phpmyadmin 中现有的 BeforeUpdate 触发器引起的。 在 phpmyadmin 中，转到表的结构，在屏幕底部您将看到触发器。
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.
我也有同样的问题。 我试图使用 Spring MVC 和 hibernate 更新表。 在我的例子中，表中的版本列包含大于 1 的值（即 3），但是我的更新查询中的更新信息的版本值是 1。
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.
我们的问题是访问前端试图将 int （是/否）保存到 mssql 位（0/1）字段中。 将 mssql 数据库更改为 int 字段非常有效。
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.
我刚刚遇到另一种情况会产生此错误。 在 mysql 表中，我有两个日期列，最初的默认值是“0000-00-00”。 后来它被更改为默认 NULL，但许多行保留值“0000-00-00”。 我必须手动将值重置为 NULL 才能停止错误。
我花了很多时间才弄清楚是什么触发了错误，HTH 是其他人。
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.
It took a lot of time to figure out what was trigering the error, HTH someone else.
如果 SQL Server 表包含 datetime2 列（在我的例子中使用默认值 sysdatetime()），也会引发此错误。 将数据类型更改回日期时间默认 current_timestamp 会停止错误。
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 表，这些表具有使用 getdate() 的默认约束。 我发现，当打开具有毫秒日期/时间值的表进行编辑时，MS Access 将抛出此错误（我假设 MS Access 不喜欢毫秒，并且正在更新打开的记录集以删除它们）。 为了避免这个问题（对于不需要毫秒的数据），我从现有行中删除了毫秒并将默认约束更改为以下内容：
It took me days to find this, but I also encountered another situation which produces this error. I have many SQL tables with date/time fields which have a default constraint using getdate(). I have found that when a table which has date/time values with milliseconds is opened for editing, MS Access will throw this error (I am presuming that MS Access doesn't like the milliseconds and is updating the opened recordset to remove them). To avoid this problem (for data where the milliseconds is unnecessary), I stripped the milliseconds off the existing rows and changed my default constraint to the following: