Views and Access 2000 - Mailing list pgsql-odbc

Hi,

We're using Access 2000 as a frontend to PostgreSQL, using the latest
PostgreSQL ODBC driver (7.02.00.05).

This was originally just an Access database and I'm using views to mimic
the original Access tables, with rules that update the underlying
PostgreSQL tables when the views are changed. These rules work fine if
the views are changed via psql. They also work fine from Access 2000 if
the PostgreSQL database used is version 7.1.2, but they don't work with
version 7.2.3.

Here's a simple example. Here's the table:

CREATE SEQUENCE "test_table_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1;
CREATE TABLE "test_table" (
    "table_id" integer DEFAULT nextval('"test_table_seq"'::text) NOT NULL,
    "field" character varying(50)
);

Here's the view:

CREATE VIEW "TestView" as SELECT test_table.table_id AS "TableID", field AS "Field" FROM test_table;

And here are the rules:

CREATE RULE test_update AS ON UPDATE TO "TestView" DO INSTEAD UPDATE test_table SET field=new."Field" WHERE
table_id=old."TableID";
CREATE RULE test_insert AS ON INSERT TO "TestView" DO INSTEAD INSERT INTO test_table (field) VALUES (new."Field");
CREATE RULE test_delete AS ON DELETE TO "TestView" DO INSTEAD DELETE FROM test_table WHERE table_id=old."TableID";

As I say, everything works properly with PostgreSQL 7.1.2, but not with
PostgreSQL 7.2.3 (using the Red Hat packages under Red Hat 7.3). If I
try and insert a record then I get an error from Access:

  The field is too small to accept the amount of data you attempted to
  add. Try inserting or pasting less data.

If I try and update an existing record then I get this error:

  Write Conflict

  This record has been changed by another user since you started editing
  it...

If I try and delete a record then I get:

  The Microsoft Jet database engine stopped the process because you and
  another user are attempting to change the same data at the same time.

I don't think the FAQ at
http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=59
explains this, because I get the same error even when updating a row
that doesn't contain empty strings. Also there aren't any timestamps
involved.

Has anyone got any suggestions? I'd prefer to carry on using the Red Hat
PostgreSQL packages, but I'd be happy to upgrade to PostgreSQL 7.3.2 if
someone thought that that would solve the problem.

Cheers,
Ian

--
Dr Ian Sealy
Internet Development
Institute for Learning and Research Technology
University of Bristol