Sqlite linked tables in Access give #deleted values, again

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.