I planning to use MS Access (2013) as a front end to a PostgreSQL database server (version 9.5.2). However, when updating a table in Access the new row appears with “#deleted” in every column until I manually click the “refresh all” button (Home tab). At which point the data appears as entered. Further, all new data saves successfully to the db, but the constant need to refresh will frustrate my users.
My research found that the problem relates to the primary key and the data type – apparently Access is picky about the data type and the presence of an auto-increment field (PG sequence). This post is helpful, but changing the data type to integer or varchar does not fix the problem (indeed, I have tried integer, big integer, varchar, etc.). This post from MS is not helpful, but provides some insight into the root of the problem.
Here are the steps taken:
- I installed the most recent ODBC driver from here (version: 09_05_0300)
- Set up ODBC connection as a data source (using Unicode x64)
Create a simple table with an auto-increment (i.e. bigserial) primary key. For example:
CREATE TABLE test.user ( user_id bigserial, username varchar(300), firstname varchar(300), lastname varchar(300), CONSTRAINT user_pk PRIMARY KEY (user_id) );
Open linked ODBC table in Access.
- Enter new row of data save.
- "#deleted" appears where data was just entered.
- Press refresh and entered data appears. All data are saved to DB.
Does anyone know how to fix this problem and eliminate the need to continually refresh the linked ODBC table?