ORDS data in Access is displaying as deleted

[MerielP]

I've just set up an ODBC connection to the database 'CSV upload test 2' in the project 'Staging test 2'. This has three tables, all created by uploading CSV files.

When I open the tables in Access, two of them display just as I'd expect. The third one, however, looks like this:

If I go back to the ORDS interface, the data is displaying normally there.

[thestoat]

I don't have access to Access on Windows so will have trouble reproducing this problem - whatever the problem is. Have you tried clicking on "Enable Content"? Does this problem happen on Dev?

[MerielP]

Clicking 'Enable Content' doesn't help. That button only appears after one has closed and re-opened a database (which I can apparently do without stopping the ODBC connection from working - that problem only arises when I close and re-open Access), and the content shows as deleted even when the ODBC connection is first set up.

This problem doesn't affect all tables within a database, and some databases seem completely fine. I haven't so far spotted a pattern.

I've just tried to test this on Dev, and found I couldn't establish an ODBC connection at all there. I'll do some further testing of this when I'm back in the office after Easter.

[MerielP]

It took a few goes to find a database on Dev where this happens - one of the annoying things about this bug is that there's no obvious pattern to it. In the end, I reproduced the contents of the database where I first noticed this on Dev - and thereby succeeded in reproducing the bug.

The database this affects is called 'CSV upload test' in the project 'Sample project' on Dev. As on Staging, two of the three tables are fine, but all the data in 'places' shows as deleted.

[thestoat]

I have looked at the database using the native ODBC client and all the data looks fine - indeed, the table is a pretty simple one.

[MerielP]

It's still showing as deleted for me when I view the tables via Access - I'm seeing the same thing as in the screenshot in the original issue report above. As before, the data is fine when I view it via the ORDS web interface.

Excuse my ignorance, but what do you mean by the native ODBC client? Is that viewing data via Access, or something else?

Anything else I might be able to try to help track down the cause of this bug?

[thestoat]

Dev: DB Server: ords-application-database-04.ords.ox.ac.uk DB: main_1979382_1979380

Staging: DB Server: ords-application-database-03.ords.ox.ac.uk DB: main_8470_8468

[thestoat]

Ok, it would appear this is due to inadequacies with MS Access. From http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html I see the text appended below. I guess we'll need a bite-sized easy to deploy solution for users who experience this, which I shall prepare when I get time.

I experienced the following issue: Upon inserting a new row Access then displays every field in the row as "#Deleted". However the insert has not failed, requerying the table displays the newly inserted row.

This was due to the fact that after every insert operation access performs a query to verify the insert.

It attempts the verification twice, once using a SELECT based on the primary key, if that fails it performs a SELECT using every other field it inserted in the row. See:

http://support.microsoft.com/kb/128809

My issue arose because the primary key in the table is a sequence and access doesn't by default know the next value (it's generated by a trigger upon insert) so the first verification attempt failed.

The second verification also failed in my case as a different trigger on my table validates and changes one of the fields before insert (so the value in that field doesn't match the value Access used in the insert command).

I fixed this problem by writing a vba module that fetches the next sequence value from postgres with a passthrough query so Access can set the primay key directly rather than relying on the upon insert trigger. I followed the example on this website (it's for oracle but can very easily be modified for postgres):

http://www.techonthenet.com/access/queries/passthrough2.php

[MerielP]

This issue certainly sounds related, but it's not quite the same: it looks as though it only affected a new record that had been added to the table, whereas the problem I had affects the whole table, and was present before I attempted to edit anything.

[thestoat]

I suspect that under the covers, the operation is the same: ORDS is doing the record adding. But I don't yet know why it only affects some tables.

[thestoat]

The problem appears to be your use of a text field as a primary key in the 'places' table. Access is not very good at handling some data types for primary kay. Varchar (max 255 bytes) should be ok, as should Ints. As a test, I copied your database to one called 'dptest' on the same server, removed the primary key and replaced it with an integer, created a new ODBC connection and all now works fine. I think this is simply an MS Access limitation - is the above workaround ok?

[MerielP]

Hurrah! Changing the primary key field type from text to varchar does indeed seem to fix the problem.

I think this is an acceptable workround (especially as it doesn't stop people having non-numerica primary keys if they need them), so I'll make sure the documentation for connecting to ORDS via ODBC covers this. I'll close this issue now, and create a new issue to remind myself about the documentation.