Re: Views and Access 2000

From: Ian Sealy <Ian(dot)Sealy(at)bristol(dot)ac(dot)uk>
To: Tambet Matiisen <tambet(dot)matiisen(at)mail(dot)ee>
Cc: pgsql-odbc <pgsql-odbc(at)postgresql(dot)org>, Ed Crewe <ed(dot)crewe(at)bristol(dot)ac(dot)uk>, Jan Grant <jan(dot)grant(at)bristol(dot)ac(dot)uk>
Subject: Re: Views and Access 2000
Date: 2003-02-16 20:18:52
Message-ID: Pine.GSO.4.44.0302162013520.4385-100000@mail.ilrt.bris.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

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

In response to

Browse pgsql-odbc by date

  From Date Subject
Next Message Kulaghin Yuri 2003-02-17 10:22:03 ODBC connection restriction
Previous Message Hiroshi Inoue 2003-02-16 10:33:19 Re: slowness in fetch from the psqlodbc driver