通过Access中的ODBC链接表更新SQLite数据库 [英] Updating an SQLite database via an ODBC linked table in Access

问题描述

我在使用SQLite数据库时遇到问题.我正在从 http://www.ch-werner.de/sqliteodbc/安装了64位版本，并使用以下设置创建了ODBC:

I am having an issue with an SQLite database. I am using the SQLite ODBC from http://www.ch-werner.de/sqliteodbc/ Installed the 64-bit version and created the ODBC with these settings:

我打开Access数据库并链接到数据源.我可以打开表，添加记录，但是不能删除或编辑任何记录.我需要在ODBC方面进行修复以允许此操作吗?尝试删除记录时出现的错误是:

I open my Access database and link to the datasource. I can open the table, add records, but cannot delete or edit any records. Is there something I need to fix on the ODBC side to allow this? The error I get when I try to delete a record is:

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

The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time.

当我编辑记录时，我得到:

When I edit a record I get:

自您开始编辑该记录以来，该记录已被其他用户更改.如果保存记录，则将覆盖其他用户所做的更改.

The record has been changed by another user since you started editing it. If you save the record, you will overwrite the changed the other user made.

保存记录已禁用.仅可复制到剪贴板或删除更改.

Save record is disabled. Only copy to clipboard or drop changes is available.

推荐答案

我最初重新创建问题的尝试未成功.我在32位测试虚拟机上使用了以下内容:

My initial attempt to recreate your issue was unsuccessful. I used the following on my 32-bit test VM:

• 访问2010
• SQLite 3.8.2
• SQLite ODBC驱动程序0.996

我按照此处中的记录创建并填充了测试表[tbl1].我创建了一个Access链接表，并在出现提示时选择了两个列([one]和[two])作为主键.当我在数据表视图中打开链接表时，我可以添加，编辑和删除记录，而不会发生意外.

I created and populated the test table [tbl1] as documented here. I created an Access linked table and when prompted I chose both columns ([one] and [two]) as the Primary Key. When I opened the linked table in Datasheet View I was able to add, edit, and delete records without incident.

我可以看到的设置与您的设置之间的唯一区别(除了我使用的是32位，而您使用的是64位)是在ODBC DSN设置中，我将Sync.Mode设置保留在它的默认值NORMAL，而您的默认值似乎设置为OFF.

The only difference I can see between my setup and yours (apart from the fact that I am on 32-bit and you are on 64-bit) is that in the ODBC DSN settings I left the Sync.Mode setting at its default value of NORMAL, whereas yours appears to be set to OFF.

尝试将Sync.Mode设置为NORMAL，看看是否有区别.

Try setting your Sync.Mode to NORMAL and see if that makes a difference.

编辑以下内容:评论

在这种情况下，解决方案如下:

The solution in this case was the following:

一种可能的解决方法是使用所有相同的列以及一个新的INTEGER PRIMARY KEY列创建一个新的SQLite表，Access会将其视为"自动编号.您可以在前四列(当前)上创建唯一索引，以确保它们保持唯一，但是新的新身份"(ROWID)列是Access用来标识CRUD操作行的内容.

One possible workaround would be to create a new SQLite table with all the same columns plus a new INTEGER PRIMARY KEY column which Access will "see" as AutoNumber. You can create a unique index on (what are currently) the first four columns to ensure that they remain unique, but the new new "identity" (ROWID) column is what Access would use to identify rows for CRUD operations.

这篇关于通过Access中的ODBC链接表更新SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！