pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAccess Update error: solution.
2+ messages / 2 participants
[nested] [flat]
* Access Update error: solution.
@ 2010-05-21 15:23 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 1 reply; 2+ messages in thread
From: Little, Douglas @ 2010-05-21 15:23 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; pgsql-sql
A comment on the MS access/PG problem.
We experienced this problem as well and I isolated the problem to access's support of timestamp.
PG defaults to timestamp(6), While access only supports timestamp(2).
When access fills the grid from the table, the values are truncated to ts(2).
As the grid is updated, a sql update statement is prepared in the background.
all fields except for those updated(by the grid) are used in the construction of the where clause.
For most cases this means all of timestamp columns are compared. Ie
Update remote x
Where local.ts1 = remote.ts1 and .....
The PG timestamp is compared to the local access copy.
Because of the truncation no row is found in PG, and Access reports that someone else updated the record.
We found the solution to be that all timestamp columns needed to be set to timestamp(0). Once we did this the issue went away.
Back to the original problem.
Serial is just a notational convenience. When created the pg engine will rewrite the statement to 1) Generate a sequence, then 2) create column as int and a default value as nextval(seqName). The datatype remains recorded as Serial, which is not a standard SQL datatype.
You can manually do the same yourself - create the sequence, then assign the column default, or easier - create the table as serial, then change the datatype to int.
PG will automatically create the seq and assign the default. Changing the datatype will leave the default and sequence intact.
Once datatype is int, Access will recognize it.
Doug
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer
Sent: Friday, May 21, 2010 3:16 AM
To: Richard Broersma
Cc: tlange@gwdg.de; pgsql-sql@postgresql.org
Subject: Re: [SQL]
On 21/05/2010 9:56 AM, Richard Broersma wrote:
> On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de> wrote:
>
>> I'm new to triggers in PostgreSQL. I have to create a trigger on insert to
>> increment a sequence to overcome MS-Access' limitation in acknowledging
>> serial "datatype".
>
> Uh? Access doesn't need to acknowledge the serial datatype.
> At-least in recent versions auto increment is recognized by MS-Access
> just fine (I'm guessing this is due to the Return clause which the
> ODBC driver automatically calls).
Really?
I had problems with Access complaining that the object it just inserted
had vanished, because the primary key Access had in memory (null) didn't
match what was stored (the generated PK). I had to fetch the next value
in the PK sequence manually and store it in Access's record before
inserting it to work around this.
I wouldn't be surprised if this actually turned out to just require some
bizarre ODBC driver parameter change, but I never figured it out and I
couldn't find any info about it on the 'net.
For the original poster: I posted some information about this at the
time I was working on it, so search the archives of this list for MS Access.
I eventually ditched Access entirely as the user who was demanding the
use of MS Access relented (phew!), so I put together a simple web-app to
do what they wanted in a day. Hopefully I'll never need to go near ODBC
again, because it's a truly "special" way to talk to PostgreSQL.
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Access Update error: solution.
@ 2010-05-21 16:22 Torsten Lange <tlange@gwdg.de>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 0 replies; 2+ messages in thread
From: Torsten Lange @ 2010-05-21 16:22 UTC (permalink / raw)
To: pgsql-sql
I think I also found one useful switch. In the ODBC-driver's setting there is
one "extra" or so field, where 4 possible values can be entered: 0x0, 0x1,
0x2... If I remember right with 0x2 the driver mimics an SQL-Server auto
increment, which Access acknowledges.
However, does anyone knows good resources to learn more about triggers? Can
also be a book to buy. I need an after insert/update trigger to update a
PostGIS geometry column of a table after someone has typed in a x,y-location
into x and y columns.
Thx,Torsten
Am Freitag, 21. Mai 2010 17:23:12 schrieb Little, Douglas:
> A comment on the MS access/PG problem.
>
> We experienced this problem as well and I isolated the problem to access's
> support of timestamp. PG defaults to timestamp(6), While access only
> supports timestamp(2).
>
> When access fills the grid from the table, the values are truncated to
> ts(2).
>
> As the grid is updated, a sql update statement is prepared in the
> background. all fields except for those updated(by the grid) are used in
> the construction of the where clause. For most cases this means all of
> timestamp columns are compared. Ie Update remote x
> Where local.ts1 = remote.ts1 and .....
> The PG timestamp is compared to the local access copy.
> Because of the truncation no row is found in PG, and Access reports that
> someone else updated the record.
>
> We found the solution to be that all timestamp columns needed to be set to
> timestamp(0). Once we did this the issue went away.
>
> Back to the original problem.
> Serial is just a notational convenience. When created the pg engine will
> rewrite the statement to 1) Generate a sequence, then 2) create column as
> int and a default value as nextval(seqName). The datatype remains
> recorded as Serial, which is not a standard SQL datatype.
>
> You can manually do the same yourself - create the sequence, then assign
> the column default, or easier - create the table as serial, then change the
> datatype to int. PG will automatically create the seq and assign the
> default. Changing the datatype will leave the default and sequence intact.
>
> Once datatype is int, Access will recognize it.
>
> Doug
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer Sent:
> Friday, May 21, 2010 3:16 AM
> To: Richard Broersma
> Cc: tlange@gwdg.de; pgsql-sql@postgresql.org
> Subject: Re: [SQL]
>
> On 21/05/2010 9:56 AM, Richard Broersma wrote:
> > On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de> wrote:
> >> I'm new to triggers in PostgreSQL. I have to create a trigger on insert
> >> to increment a sequence to overcome MS-Access' limitation in
> >> acknowledging serial "datatype".
> >
> > Uh? Access doesn't need to acknowledge the serial datatype.
> > At-least in recent versions auto increment is recognized by MS-Access
> > just fine (I'm guessing this is due to the Return clause which the
> > ODBC driver automatically calls).
>
> Really?
>
> I had problems with Access complaining that the object it just inserted
> had vanished, because the primary key Access had in memory (null) didn't
> match what was stored (the generated PK). I had to fetch the next value
> in the PK sequence manually and store it in Access's record before
> inserting it to work around this.
>
> I wouldn't be surprised if this actually turned out to just require some
> bizarre ODBC driver parameter change, but I never figured it out and I
> couldn't find any info about it on the 'net.
>
> For the original poster: I posted some information about this at the
> time I was working on it, so search the archives of this list for MS
> Access.
>
> I eventually ditched Access entirely as the user who was demanding the
> use of MS Access relented (phew!), so I put together a simple web-app to
> do what they wanted in a day. Hopefully I'll never need to go near ODBC
> again, because it's a truly "special" way to talk to PostgreSQL.
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-05-21 16:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-21 15:23 Access Update error: solution. Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-05-21 16:22 ` Torsten Lange <tlange@gwdg.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox