Situation: MS Access (happens to be 2010) using SQLite ODBC driver (0.997) to link to tables in a SQLite (3.x) database.
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.
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.
Half of the problem is explained here: http://support.microsoft.com/kb/128809 '"#Deleted" errors with linked ODBC tables.' The above link was no longer available in Jul-2021. However you may find a good explanation for '#DELETED# Records Reported by Access' in https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-errors.html
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.
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.
To make the table updateable you might revise your SQLite code (or using a SQLite tool) to add an index to the table.
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.
The chain of events appears to be:
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.
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.
Hope this helps someone.
