Re: Views and Access 2000

7.3 fixes this. See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=rules-status
.html.

Tambet

----- Original Message -----
From: "Ian Sealy" <Ian(dot)Sealy(at)bristol(dot)ac(dot)uk>
To: <pgsql-odbc(at)postgresql(dot)org>
Cc: "Ed Crewe" <ed(dot)crewe(at)bristol(dot)ac(dot)uk>; "Jan Grant"
<jan(dot)grant(at)bristol(dot)ac(dot)uk>
Sent: Friday, February 14, 2003 3:40 PM
Subject: [ODBC] Views and Access 2000

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>