Access中的Sqlite链接表再次给出#deleted值 [英] Sqlite linked tables in Access give #deleted values, again

问题描述

情况：MS Access（恰好是2010年）使用SQLite ODBC驱动程序（0.997）链接到SQLite（3.x）数据库中的表。

Situation: MS Access (happens to be 2010) using SQLite ODBC driver (0.997) to link to tables in a SQLite (3.x) database.

问题：所有行中所有列中的数据值显示为#Deleted。

Problem: data values in all columns in all rows display as "#Deleted".

解决方案：这是一个回答我自己的问题类型的帖子，带有解决方案，下面。

Solution: This is a "answer my own question" kind of post, with a solution, below.

编辑：将解决方案移至答案部分。

Edited: to move solution to Answers section.

推荐答案

早些时候，我在stackoverflow中搜索，发现了一个类似的问题（Access中的sqlite链接表给出了#deleted值），并给出了一个很好的答案，结果证明在我的情况下是不适用的。所以我在这里添加一些信息。

Earlier, I searched in stackoverflow, found a similar question (sqlite linked tables in Access give #deleted values) with a good answer that turns out to be inapplicable in my case. So I'm adding some info here.

这里解释了一半的问题： http://support.microsoft.com/kb/128809 #Deleted错误。'

Half of the problem is explained here: http://support.microsoft.com/kb/128809 '"#Deleted" errors with linked ODBC tables.'

这解释了Access（Jet）希望表具有唯一索引，以便能够在必要时插入/更新表。

This explains that Access (Jet) wants a table to have a unique index in order to be able to insert/update the table if necessary.

如果您的SQLite表没有没有唯一索引（或主键），然后Access将只允许对表的读访问 - 您无法在Access中编辑表的数据，但数据显示正常。

If your SQLite table doesn't have a unique index (or primary key), then Access will only allow read access to the table -- you can't edit the table's data in Access, but the data displays fine.

为了使表可更新，您可以修改SQLite代码（或使用SQLite工具）向表中添加索引。

To make the table updateable you might revise your SQLite code (or using a SQLite tool) to add an index to the table.

如果你的PK / unique索引碰巧使用TEXT字段，这对SQLite来说很好。但是，当您在Access中链接到它时，Access将显示#Deleted指示。

If your PK/unique index happens to use a TEXT field, that's fine for SQLite. However, when you link to it in Access, Access will show the #Deleted indications.

事件链似乎是：

Access / Jet会注意到唯一索引，并尝试使用它。但是，SQLite TEXT字段是可变长度的，可能是BLOB。这显然不符合Access对唯一索引字段的要求，因此#Delete指示。

Access/Jet notices the unique index, and tries to use it. However, SQLite TEXT fields are variable length and possibly BLOBs. This apparently doesn't fulfill Access's requirements for a unique index field, hence the #Delete indication.

为了避免这个问题，索引必须是SQLite字段类型，访问权限将接受。我不知道可接受的完整类型列表，但是INTEGER可以工作。

To avoid that problem, the index has to be a SQLite field type that Access will accept. I don't know the complete list of types that are acceptable, but INTEGER works.

希望这有助于某人。

这篇关于Access中的Sqlite链接表再次给出#deleted值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！