Search code examples
sqlitems-accessodbcms-access-2010sqlite-odbc

Updating an SQLite database via an ODBC linked table in Access

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:

enter image description here

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:

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.

Solution

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

    • Access 2010
    • SQLite 3.8.2
    • SQLite ODBC Driver 0.996

    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.

    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.

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

    Edit re: comments

    The solution in this case was the following:

    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.