Re: Views and Access 2000

Dear Tambet,

> > 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.
>
> 7.3 fixes this. See
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=rules-status.html.

Thanks very much for that. Much appreciated. Everything is now working
perfectly.

Cheers,
Ian

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