Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] MS Access 2007 update write conflict problem & resolution

From: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
To: "justin(at)magwerks(dot)com" <justin(at)magwerks(dot)com>
Cc: Douglas Little <DOUGLAS(dot)LITTLE(at)orbitz(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>
Subject: Re: [GENERAL] MS Access 2007 update write conflict problem & resolution
Date: 2010-03-19 20:12:35
Message-ID: 396486431003191312q41c5fb32v8355af36403227f5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-supportpgsql-general
On Fri, Mar 19, 2010 at 11:12 AM, justin(at)magwerks(dot)com
<justin(at)magwerks(dot)com> wrote:
>
> The bigger problem is using time stamps to find the record for updating
>
> Timestamps will not be unique as more than 1 record can have the same value
>
> I suggest changing the updating method to use a unique key.

MS-Access achieves optimistic locking by listing all of the columns in
a table in the WHERE clause of the UPDATE statement.  If ms-access
unable to fully represent the data stored in a postgresql table,
Access will throw back update conflict errors when postgres doesn't
update the record due to the truncated data values.  I've also seen
this with null valued boolean fields.

Part of the solution would be configure the ODBC driver to use
PostgreSQL's xmin for optimistic locking.  Of-course this doesn't fix
MS-Accesses inability to handle the ranges of many of PG data-types.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

In response to

pgadmin-support by date

Next:From: justin@magwerks.comDate: 2010-03-19 20:47:52
Subject: Re: MS Access 2007 update write conflict problem & resolution
Previous:From: Dave PageDate: 2010-03-19 18:59:32
Subject: Re: Request: get rid of annoyance with new SQL window

pgsql-general by date

Next:From: Peter EisentrautDate: 2010-03-19 20:38:51
Subject: Call for translations
Previous:From: Joe ConwayDate: 2010-03-19 19:41:05
Subject: Re: Query inside a C-Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group