Thread: Views and Access 2000

Views and Access 2000

From

Ian Sealy

Date:

14 February 2003, 11:41:08

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

Re: Views and Access 2000

From

"Tambet Matiisen"

Date:

14 February 2003, 15:28:12

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.Sealy@bristol.ac.uk>
To: <pgsql-odbc@postgresql.org>
Cc: "Ed Crewe" <ed.crewe@bristol.ac.uk>; "Jan Grant"
<jan.grant@bristol.ac.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
>

Re: Views and Access 2000

From

Ian Sealy

Date:

17 February 2003, 16:40:31

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