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

Re: Row versionning between MS-Access and PostgreSQL 8

From: "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: Ets ROLLAND <ets(at)rolland-fr(dot)com>
Cc: pgsql ODBC <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Row versionning between MS-Access and PostgreSQL 8
Date: 2005-07-22 19:30:58
Message-ID: 42E14972.8050004@us.michelin.com (view raw or flat)
Thread:
Lists: pgsql-odbc
You should not need a rowversioning function on your PostgreSQL server.

It is a setting in the ODBC driver. If you are using a DSN, when
configuring your PostgreSQL data source, in the PostgreSQL ODBC Data 
Source applet window,  use the datasource button, then Page
2, and click in the checkbox for  row versioning.
If you are using a DSN-less connection, for the Connection String
include the RowVersioning parameter (=1).

Access/OCBC wants to know if some other users has updated the record 
since you started viewing it -- it may query every field to see if they 
matched the state when you started to view.

The automatically assigned row version number will show up as xmin in 
any linked tables.

But primarily it wants to know if it has the correct record, and will 
query each field to see if it matches the last known state,.... unless 
it has an identifiable primary key.

Good primary keys, and row versioning solve many of problems.

Give every table a good primary key  (a key with no floats, no text/memo 
fields, no LOB/BLOB fields), even if the key is an otherwise unused 
serial (auto-number). Re-link fields if necessary. Access should have no 
problem determining the update state of records. If it does use the ODBC 
Administrator to turn on lo turn on tracing and capture a log file of 
typical activity.

The PostgreSQL documentation talks about how to set the resolution of 
your timestamp fields.



Ets ROLLAND wrote:
> 
> Hello !
>  
> I made the port of an Access 2002 Database to PostgreSQL 8, it seems to 
> work.
> I still use MS-Access 2002 for the application, using ODBC with linked 
> tables.
>  
> For all the data created BEFORE the transfert to PostgreSQL, all works fine.
> For the records created SINCE this transfert, it is impossible to modify 
> or delete these records !?
> MS-Access say that "The record is acceded by an other user", even I am 
> the only user.
>  
> Someone tell to me, on the general forum, that :
> "Access is trying to identify rows by looking at the data, which 
> includes a timestamp.
>  PG supports fractional-seconds which is confusing Access.
> All your old timestamps aren't fractional because they were imported 
> from Access.
> When you define your timestamps in PG you can control how much accuracy 
> you want
> - might be worth dumping/restoring without the fractions.
> Look for a "row versioning" option in your ODBC settings. Also, there's
> an ODBC list too - check the mailing-list archives for that."
>  
> So I understand the problem, but not how to fix it.
> I was using psqlODBC v8.00.0101,
> I've downloaded the new snapshot v8.01.0002 without any change.
> I try to create row versioning function as explain at :
> http://docman.sourceforge.net/home_html/projects/sql/access_fix.sql
> but now MS-Access crash when I try to modify any record !
> How can I fix the Row versionning between MS-Access and PostgreSQL 8 ?
>  
> Thanks for your help...
>  
> Bests regards.
>  
> Luc



Attachment: greg.campbell.vcf
Description: text/x-vcard (241 bytes)

In response to

pgsql-odbc by date

Next:From: Greg CampbellDate: 2005-07-22 21:38:02
Subject: Re: Row versionning between MS-Access and PostgreSQL 8
Previous:From: Ets ROLLANDDate: 2005-07-22 17:21:13
Subject: Row versionning between MS-Access and PostgreSQL 8

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