1

Microsoft Access can't update large Memo Fields (Long Text) on the ODBC linked SQLite table. The limit is somewhere between 2300 and 5152 characters. It can be an Update Query, DAO or ADO rs.Edit or manual copy-paste using Ctrl-C / Ctrl-V + mouse directly into the linked table - it doesn't matter.

Error messages:

Update query or manual copy-paste:

ODBC-update on a linked table 'Adobe_AdditionalMetadata' failed.
sequence error (#-1)

DAO, VBA on the rs.Edit line:

Run-time error '3027':
Cannot update. Database or object is read-only.

ADO, VBA on the rs.Update line:

Run-time error '-2147217887 (80040e21)':
ODBC-update on a linked table 'Adobe_AdditionalMetadata' failed.

Installed driver:
http://www.ch-werner.de/sqliteodbc/ (SQLite3 ODBC Driver)
Access linked field properties: Long Text
the original SQLite field properties: TEXT NOT NULL DEFAULT ''
(Access see almost all fields as Memo)
This is the "xmp" field in the "Adobe_AdditionalMetadata" table of the Adobe Lightroom "Lightroom Catalog.lrcat" file.

Smaller Memo fields can be updated easily.

darekk
  • 71
  • 1
  • 13
  • Don't know if this is suitable in your case, but I seem to have avoided the issue by enabling "No WCHAR" in the DSN properties and then deleting and re-creating the linked table. – Gord Thompson Dec 12 '16 at 14:07
  • Thank you, it was so easy ... Updating using the Update Query, copy-paste and ADO VBA work right now, DAO VBA displays still the same error message. Could you explain why to set No WCHAR ? – darekk Dec 12 '16 at 17:32
  • Interesting. A `DAO.Recordset` update (`rs.Edit` and `rs.Update`) works for me. – Gord Thompson Dec 12 '16 at 17:42
  • Just in case: Data uploading or modifying in .lrcat files like the "Lightroom Catalog.lrcat" using applications other than Lightroom (or its SDK) is not only risky (can corrupt such file if you did it improperly), but you also probably would not get any support from Adobe in such case: https://forums.adobe.com/message/9210122#9210122 Data modifing in the Lightroom Catalog.lrcat file and the Lightroom license – darekk Dec 18 '16 at 17:20

1 Answers1

1

One possible workaround is to enable the "No WCHAR" option for the ODBC DSN:

DSN.png

(Existing ODBC linked tables in Access may have to be re-created for the change to take effect.)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • > Existing ODBC linked tables in Access may have to be re-created - all tables, not only that with Memo field to update, because Access crashes on some of queries when only one table is recreated. It looks like some sort of inconsistency. – darekk Dec 12 '16 at 18:42