14

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Herrozerro
  • 1,601
  • 1
  • 22
  • 37
  • There is a similar question in another forum [here](http://social.msdn.microsoft.com/Forums/office/en-US/85196e70-d77f-4aef-af3f-78e43570f54a/editing-records-in-a-sqlite-backend-db?forum=accessdev). Do any of those answers or suggestions help? – Gord Thompson Oct 29 '13 at 14:08
  • @GordThompson Adding a Timestamp field seems to work. I needed to purge the table afterwards though. The only records Access can change though is records added by access. I have added a record though SQLite Expert Personal edition and Access is unable to change or delete those records. – Herrozerro Oct 29 '13 at 14:38
  • Adding a link to this related question: http://stackoverflow.com/questions/22837151/sqlite-linked-tables-in-access-give-deleted-values-again – gwideman Apr 03 '14 at 12:06

4 Answers4

10

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • would it be possible to add a record using another program like http://www.sqliteexpert.com/ ? My Issue seems to be when you try to edit a record not added by access. With a Timestamp field if i added the record through access, it works fine, but adding a record through another source seems to have the same problem. – Herrozerro Dec 12 '13 at 13:37
  • @Herrozerro I created the first two records in the table using SQLite itself (as per [here](http://www.sqlite.org/sqlite.html)) and I was able to both edit and delete one of those records. If you have a small sample SQLite file that you can share via [wikisend.com](http://wikisend.com) (or similar) I could see if it behaves differently for me. – Gord Thompson Dec 12 '13 at 13:50
  • here is a link to my file. https://www.dropbox.com/s/2o68md7fd1vvr7b/qaqc.sqlite – Herrozerro Dec 13 '13 at 13:25
  • @Herrozerro I was able to recreate your issue using your sample file. I tried a few variations of ODBC driver settings and they didn't resolve the issue. 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. (Unfortunately I can't test this workaround for a few days.) – Gord Thompson Dec 16 '13 at 10:37
4

I had this problem too. I have a table with a primary key on a VARCHAR(30) (TEXT) field.

Adding an INTEGER PRIMARY KEY column didn't help at all. After lots of testing I found the issue was with a DATETIME field I had in the table. I removed the DATETIME field and I was able to update record values in MS-Access datasheet view.

So now any DATETIME fields I need in SQLite, I declare as VARCHAR(19) so they some into Access via ODBC as text. Not perfect but it works. (And of course SQLite doesn't have a real DATETIME field type anyway so TEXT is just fine and will convert OK)

I confirmed it's a number conversion issue. With an empty DATETIME field, I can add a time of 01-01-2014 12:01:02 via Access's datasheet view, if I then look at the value in SQLite the seconds have been rounded off: 

    sqlite> SELECT three from TEST where FLoc='1020';
2014-01-01 12:01:00.000

SYNCMODE should also be NORMAL not OFF.

Update: If you have any text fields with a defined length (e.g. foo VARCHAR(10)) and the field contents contains more characters than the field definition (which SQLite allows) MS-Access will also barf when trying to update any of the fields on that row.

D.Peters
  • 101
  • 4
  • 1
    I had the same issue and D.Peters workaround worked. switching from DATETIME to VARCHAR(19) is the solution. Thanks a lot. – cetipabo Sep 14 '16 at 12:28
0

I've searched all similar posts as I had a similar issue with SQLite linked via ODBC to Access. I had three tables, two of them allowed edits, but the third didn't. The third one had a DATETIME field and when I changed the data type to a TEXT field in the original SQLite database and relinked to access, I could edit the table. So for me it was confirmed as an issue with the DATETIME field.

jroyce
  • 2,029
  • 2
  • 22
  • 45
0

After running into this problem, not finding a satisfactory answer, and wasting a lot of time trying other solutions, I eventually discovered that what others have mentioned about DATETIME fields is accurate but another solution exists that lets you keep the proper data type. The SQLite ODBC driver can convert Julian day values into the ODBC SQL_TIMESTAMP / SQL_TYPE_TIMESTAMP types by looking for floating point values in the column, if you have that option enabled in the driver. Storing dates in this manner gives the ODBC timestamp value enough precision to avoid the write conflict error, as well as letting Access see the column as a date/time field.

Even storing sub-second precision in the date string doesn't work, which is possibly a bug in the driver because the resulting TIMESTAMP_STRUCT contains the same values, but the fractional seconds must be lost elsewhere.

wmorgan
  • 1
  • 1