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-ow...@postgresql.org > [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Craig Ringer Sent: > Friday, May 21, 2010 3:16 AM > To: Richard Broersma > Cc: tla...@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,<tla...@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
