From Access, selecting against a table works while a view does not.
Postgres, v9.4
New version of MS-Access 365, v2303
Driver=PostgreSQL Unicode, v13.02 9/22/2021
Parameters: PORT=5432;A1=7.4-1;B0=255;B1=8190;B6=0;C2=dd_;
Selecting against the Deposit table in Postgres returns the correct data and the correct number of rows. 5 records, all different, as expected.
SELECT *
FROM "Deposits"
WHERE "Deposit_Slip_Number"='2305-6';
Selecting against a view in Postgres returns the correct data and the correct number of rows. 15 records, all different, as expected.
SELECT *
FROM "vw_rptDeposits_OnBankStatement"
WHERE "Deposit_Slip_Number"='2305-6';
Selecting against the Deposit table in Access returns the correct data and the correct number of rows. 5 records all different, as expected.
SELECT *
FROM Deposits
WHERE Deposit_Slip_Number='2305-6';
Selecting against the view in Access returns incorrect data but the correct number of rows. 15 records all the same as the first record, NOT as expected.
SELECT *
FROM vw_rptDeposits_OnBankStatement
WHERE Deposit_Slip_Number='2305-6';
I've rebooted my machine, restarted the postgres service.
The driver hasn't changed in about a year.
If I point Access at a different server, same results, table works, view does not.
The only thing that has changed is that I'm working on the view, but that shouldn't affect how it transfers data. And the other database has the original view.
The views and tables are just pass-thru tables, linked to Postgres, no dependency on anything local to Access.
These are DNS-less tables linked through code.
All other views look correct.
When linking ODBC Views in Access, you need to specify a unique PK for them to work correctly.
The usual problem if no PK is given is that the linked view is read-only.
But if you specify a non-unique column as PK, all sorts of problems can occur.
For linked ODBC tables, the PK is identified automatically (at least for MSSQL).