Can't Use Updatable Views Through ODBC?

[gwinn7]

I am expanding our in-house application on our Postgres database. One really key feature in Postgres is the ability to create Updatable Views.

Unfortunately, I am not having much success when linking my Access 97 front-end to my view.

The form in my Access app displays the records in the view fine, but I can't update any data. Access 97 is reporting it as a non-updatable set. I know that my UPDATE RULE is working properly because I can run UPDATE statements from within pgAdmin.

I am 95% certain that it is not a permissions issue because my linked table was connected through the same superuser account (yes, I plan on changing that later).

If you need to see code, than I can provide it, but I am hoping that someone viewing this post may have seen this problem before and know how to remedy it. Is it a bug/limitation of the psqlODBC driver?

Thanks,
Gary

 

[cravincreeks]

This is more likely to be and ODBC issue rather than a Postgres. We ran into this problem when attempting to do something similar with SQL Server (MSDE). ODBC won't let you update two tables at the same time if both tables have columns on the same form. Instead you can put fields from one table on one form and the fields from the other table on a SUBFORM. Just make sure you have your subforms set up correctly

 

[gwinn7]

That's interesting because I thought that Access/ODBC sees views just like tables when linked. Obviously, this is an incorrect assumption. Hopefully, this will be fixed in the driver soon.

Thank you for your response.

Gary
gwinn7