Windows Server 2012
MS Access 2010
psqlODBC_x64 v9.2.1 (Unicode)
PostgreSQLx64 9.1.13, and 9.2.x
db: encoding=UTF8; collation and charater type = English_United States.1252
I have a database which has some composite primary keys that contain character varying fields. Some of these fields have unicode characters from a foreign language. I have used this configuration for about 1 year without any problems. All of the table data were visible through an Access front end over the unicode 64 odbc connection, it worked great. Yesterday I reinstalled only the postgresql software itself. Neither the OS, nor the odbc driver, nor Access was changed. I restored my database and within Pgadmin everything looks fine including my data. Then I went back to my Access frontends and at first everything still looked fine - without having changed anything the data appeared. However, then I saw that in the limited cases where my primary key does contain a unicode character, those rows now appear with the #deleted in all fields.
The database encoding is set to UTF8 and I have tried setting the client_encoding variable to UTF8 in the database, in the login and group roles and in the .conf file. There is no change, I still get #deleted. In fact, I've attempted to set the client_encoding variable on the database to sql_ascii, but running show client_encoding from any client still shows utf8, so I don't even know if that variable is having any effect. This is all very reproducible; I took a new Windows 7 machine, did a fresh install of Access, postgresql 9.1.3, odbc x64 unicode 9.3.2.1, create a db with utf8 encoding, add table with a primary key of character varying and add some foreign characters, back in the Access linked table, same behavior.
My assumption is that the problem is that Access is assuming it needs to read the primary keys in utf16 or something similar. When it goes back to requery the db to display the table, then it can no longer find these keys and I get the #deleted. When I create an Access query on the same table and set the Recordset Type to Snapshot, all of the data are displayed without any problems.
I did see this same problem over a year ago when I first started using Access and Postgresql together. I must have fixed it about a year ago, at which point the problem went away and I have been happily using this configuration ever since. Unfortunately, I can neither remember nor find on the internet whatever solution I implemented back then. It's probably something in postgresql itself since that is the only part of the system I changed, either in the server configuration or in the database configure that wasn't brought over through my backup/restore process.
Thank you to all for any help you can provide!
-TJ
