ODBC linked PostgreSQL tables in queries readonly

[GregoryWest]

I have migrated all my tables onto PostgreSQL, everything is working OK, except for one issue. All my queries that reference two or more tables are now all read only. This is a major hassle, I have got around a lot of the little ones by simply opening the table in question in VBA and update the fields referencing the primary key. There are a few input screens that I would need to rewrite, and would really rather not. If there a trick to writing the queries in Access 2010 for they do not end up being read only?

The project is 90% complete, so even if I have to tweek the queries in SQL code I am OK with that. What ever it takes to get this working.

 

[boerbende]

I am using SQL server, I know you only have readonly when an index is missing, but this is not the case here.
Maybe you can try a workaround: try a pass thru query. This means that not Access, but PostgreSQL is handling the query. This can be done with the query designer in Access

 

[GregoryWest]

Not ever done that, is there anything special I need to do to do a passthrough query? or if it a toggle switch somewhere in Access?

 

[boerbende]

Start Query designer, go to SQL view, click on design. Then you can choose Pass Through.

 

[GregoryWest]

Hmmm I think I am doing something wrong here. I took one of the queries and clicked on the Pass Through option. When I open the form that calls up the query I now have to select the DNS and then I get the following runtime error #3146: replation "partsworkorder" does not exist while executing query (#7).

This is an embedded form with a subform, both have queries on them.

Not sure if this is what I want to do, I am just trying to get a read-write query to my PostgreSQL database.

 

[GregoryWest]

Passthrough is not working for me. Now I have the issue of the queries that have been made passthrough I can not change back to normal queries. Is there a way to do this?

 

[boerbende]

a workaround maybe
copy or save the sql, delete the query, create a new query, goto sqlview and paste the copied sql

 

[Rx_]

Queries that have summary can not be updated, you seem to have eliminated that possibility. Tables that don't have an index (e.g. an autocounter) are read-only. You seem to be aware of that too.

Can you update data on each single table that makes up the query?
Just trying to confirm that each table is Read/Write but a query with the tables are read/only.
Is the Query on the Access side (a query using two linked tables) or is the Query more like a View on the target Database?

 

[GregoryWest]

Sorry to take so long to get back to you. Yes can update either of the two tables if I only pull up one table in a query. Both tables have a Autoinc field (ID in my case). The join is on the Access side, I guess I could look at making it a view, but that means a lot of extra code I am hoping to avoid (mostly the testing of the new code)