MS Access 2007 update write conflict problem & resolution

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.

---- Message from mailto:DOUGLAS.LITTLE@orbitz.com "Little, Douglas" DOUGLAS.LITTLE@orbitz.com at 03-19-2010 10:35:09 AM ------

Hi,
We’ve been struggling with an MS Access 2007 app that updates a PG table. It was working, and then it wasn’t.
It looks like we recreated the table without specifying the precision on the timestamp columns.

PG is defaulting to timestamp(6), which doesn’t work with MS Access. The finest precision Access can deal with is timestamp(3).
When the record is retrieved from PG it displays 6 microseconds, when Access prepares the update, the value is rounded to 3 microseconds.
The update can’t then locate the row (timestamp mismatch), and displays the ‘write conflict’ error.

Altering the column to timestamp(0) corrected the issue.

2 questions
1- PGAdmin was reporting the precision as 0, while a select was displaying 6 microseconds. Is this a known bug in pgadmin?
2- Is there a way in PG that I can set the default precision on timestamp?
Thanks

I’ll cross post to the pgadmin list for the pgadmin question.

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588| Fax 312.894.5164 | Cell 847-997-5741
mailto:Douglas.Little@orbitz.com Douglas.Little@orbitz.com
http://www.orbitz.com/ orbitz.com | http://www.ebookers.com/ ebookers.com | http://www.hotelclub.com/ hotelclub.com | http://www.cheaptickets.com/ cheaptickets.com | http://www.ratestogo.com/ ratestogo.com | http://www.asiahotels.com/ asiahotels.com

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

On Fri, Mar 19, 2010 at 11:12 AM, justin@magwerks.com
<justin@magwerks.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

When connected to ODBC source Access ignores locking steps. I don't think that's the problem.

If memory serves, when Access is building the UPDATE command automatically the WHERE clause includes every single column being updated when the old values, something like this

Update set col =145, col2 = 'jjj', col4 = '12/01/10 10:05:5.032' where
col =oldvalue1 and col2 = 'jjj' and col4 = '12/01/10 10:05:5.03'

this means it will not find the record returning the error you see.

to over come this very annoying feature you have to manually specify the update commands and the key to use.

---- Message from mailto:richard.broersma@gmail.com Richard Broersma richard.broersma@gmail.com at 03-19-2010 01:12:35 PM ------

On Fri, Mar 19, 2010 at 11:12 AM, justin@magwerks.com
justin@magwerks.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

All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.

On 19/03/2010 11:35 PM, Little, Douglas wrote:

The update can�t then locate the row (timestamp mismatch), and displays
the �write conflict� error.

Change the ODBC driver settings to issue updates by primary key, and the
issue goes away. IIRC it's done in some backward-ish way like turning
off "row versioning".

--
Craig Ringer