Thread: Bad locking with MS-Access

Bad locking with MS-Access

From
"Ets ROLLAND"
Date:
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.
 
I look for the the options / advanced tab of that MS-database, I don't find an issue.
I look for the parameters of my ODBC system daya source without success.
I don't find any solution with PostgreSQL...
 
BUT if I modify any field in one of these locked records with the browser of pgAdmin III,
then MS-Access accept to modify or delete the corresponding record !?
If I write an SQL script to do the same in pgAdmin III (ex. add zero to an integer value),
it don't works !
 
I'm going crazy !...
HELP !
 
Luc

Re: Bad locking with MS-Access

From
Richard Huxton
Date:
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

Re: Bad locking with MS-Access

From
Andreas
Date:
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.



Re: Bad locking with MS-Access

From
"Zlatko Matic"
Date:
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


Re: Bad locking with MS-Access

From
Andreas
Date:
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.