2

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:

  1. I installed the most recent ODBC driver from here (version: 09_05_0300)
  2. Set up ODBC connection as a data source (using Unicode x64)
  3. 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) );

  4. Open linked ODBC table in Access.

  5. Enter new row of data save.
  6. "#deleted" appears where data was just entered.
  7. 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?

  • Access can not handle varchar(300), varchar(255) is the max. See the post you referenced. – byrdzeye Jul 18 '16 at 16:38
  • Ooops... you are totally right. That solved the problem! Thanks! – Cliff Jul 18 '16 at 16:50
  • @byrdzeye -- you should post that as the answer. It solved the same issue I had as well – Hambone Mar 28 '18 at 13:59
  • I'm quite sure Access can handle VarChar(300). With MySQL it will get linked as MemoField, should be the same. Problem is BigSerial (use Serial) I think, as Access is limited to Long Integer. And you don't have a timestamp/rowversion field, what indicates that ODBC connection is not set properly. See USING MS ACCESS WITH POSTGRESQL – ComputerVersteher Jul 21 '18 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.