Thread: Bad locking with MS-Access

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.

Wild guess - 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.

--
   Richard Huxton
   Archonet Ltd

Ets ROLLAND schrieb:

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


As Richard wrote in his mail, do set row versioning in the ODBC setup.
Have a primary key in every table and a timestamp.
Be careful not to use to big data types in PG that aren't supportet by
Access.

Use   timestamp(0)   to get timestamps compatible to Access' DateTime
values.
Use   serial or serial4   to create auto-values.   Don't use any
bigint-types like bigserial. Access doesn't like 8-byte-ints. Keep in
mind that Access' autovalues are signed, so they'll roll over at about 2
billion.

Hello, Andreas!

You mentioned: "Use   serial or serial4   to create auto-values.   Don't use
any bigint-types like bigserial. Access doesn't like 8-byte-ints.".
Could you please explain why you don't recommend bigserial for primary key ?
I use bigserial primary keys in Postgres tables, and din't realise problems
with MS Access front-end. What problems could I expect ?
Thanks,

Zlatko



----- Original Message -----
From: "Andreas" <maps.on@gmx.net>
To: "Ets ROLLAND" <ets@rolland-fr.com>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, July 24, 2005 11:01 PM
Subject: Re: [GENERAL] Bad locking with MS-Access


> Ets ROLLAND schrieb:
>
>> 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.
>
>
> As Richard wrote in his mail, do set row versioning in the ODBC setup.
> Have a primary key in every table and a timestamp.
> Be careful not to use to big data types in PG that aren't supportet by
> Access.
>
> Use   timestamp(0)   to get timestamps compatible to Access' DateTime
> values.
> Use   serial or serial4   to create auto-values.   Don't use any
> bigint-types like bigserial. Access doesn't like 8-byte-ints. Keep in mind
> that Access' autovalues are signed, so they'll roll over at about 2
> billion.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Hi Zlatko,

> You mentioned: "Use   serial or serial4   to create auto-values.   Don't
> use any bigint-types like bigserial. Access doesn't like 8-byte-ints.".
> Could you please explain why you don't recommend bigserial for primary
> key ?
> I use bigserial primary keys in Postgres tables, and din't realise
> problems with MS Access front-end. What problems could I expect ?

It depends on your application.
In general you would chose datatypes only as big as you will likely need.
PG can handle those huge 64 bit integers.
big-serials get stored in big-integers. They are signed so the highest
count is 2^63 = 9223372036854775808.

As long as server and client can handle the size, use bigint if you
consider this helpful. But if you NEED those big numbers, you are
screwed with Access as client because it'll throw an error for values
above 2^31.

Below that limit all seems to be ok, since PG handles the bigints and
ODBC translates them transparently to 4-byte-integers to keep Access happy.

If you know definitely that this procedure will allways work because the
limit won't be reached then you don't need the bigints at all.

Depending on the way ODBC translates the numbers you might end up with
negative values above 2^31 in Access though in PG the same field would
be still positive of course.
If you try to push a real Access longint above 2^31 you get an error and
the field is empty afterwards.

Perhaps one could map a PG bigint to text(20) or decimal(20).
I guess there would be a performance penalty though.