Thread: Row versionning between MS-Access and PostgreSQL 8

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

[reply was a little scrambled the first time]

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).

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

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.


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