0

I'm using Microsoft Access to connect to a postgres-database. On that postgres database I have a foreign-table via postgres_fdw.

I can select on that table with MS Access, however running updates will throw the error:

Error 3073: Operation must use an updatable query.

The update I'm running is:

UPDATE my_tbl SET my_column = null WHERE id = 1337;

Other applications have no issues doing so, this is limited to Microsoft Access. Any way to get it to work in Access?

octavio
  • 456
  • 4
  • 14
  • If you update a "local" table, not a fdw table does it then work? – jjanes Apr 19 '23 at 14:57
  • Does this answer your question? [Making MS Access queries that allow data entry to PostgreSQL database via an ODBC driver](https://stackoverflow.com/questions/53121865/making-ms-access-queries-that-allow-data-entry-to-postgresql-database-via-an-odb) – June7 Apr 19 '23 at 18:00
  • does the table have a PK defined? Access as a general rule can't update linked tables to a server unless the server has a PK defined for that table. You can quick check what Access sees as the PK by right click on the linked table, and choose design. Ignore the warning about read only. If the linked table in design mode does NOT show a primary key settings, then you need to add one in the PostgreSQL table, and THEN RE-LINK the access table(s). – Albert D. Kallal Apr 20 '23 at 02:29

1 Answers1

0

does the table have a PK defined? Access as a general rule can't update linked tables to a server unless the server has a PK defined for that table.

You can quick check what Access sees as the PK by right click on the linked table, and choose design. Ignore the warning about read only. If the linked table in design mode does NOT show a primary key settings, then you need to add one in the PostgreSQL table, and THEN RE-LINK the access table(s).

So, check the linked table in design mode. You need to ensure that all tables on the server that access will be updating has a PK row setting.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51