Re: psqlodbc-08_00_0101 does not work with Ms Access

Hello,

first, I want to thank all these people working on Postgresql and Psqlodbc
in any way for their good job!

The last weekend, through one day and one night, I found out, that the ODBC
Driver psqlodbc-08_00_0101 does not work correct in conjunction with MS
Access 97.

But correct works psqlodbc-07_02_0004!

My database is running on PostgreSQL 8.0.3/Linux. By using MS Access 97
with linked tables together with psqlodbc-08_00_0101 these problems occur:

1. Tables at Postgresql with a varchar(x) primary key or primary keys
combined of several columns: If linked in Ms Access 97 all columns are
marked as "Deleted". Access is not able to use the primary keys.

2. Columns in tables in Postgresql with type "text", when linked in Access
are not recognized correctly as "Memo" by Access. Instead they are
recognized as "Text" by Access, with a maximum of 255 (or 256?) chars.
"Text" in Access is the same type as Varchar(256) in Postgresql. "Memo" in
Access is nearly the same type as "text" or varchar (unlimited) in
Postgreql.

For me it took a long time to figure this out, because whenever i
deinstalled psqlodbc-08_00_0101, the dlls remained on disk. Several times I
installed an other psqlodbc-driver, but every time in reality psqlodbc-08_
00_0101 was used again by Access. I had to delete the psql*.dlls by hand to
be able to reinstall psqlodbc-07_02_0004.

Because the development is going on, it makes me insecure, that the
function of psqlodbc was changed in a way that in newer versions it is
unusable with MS Access.

Alles Gute and have a good time,

Christof
(For mail change "devnull" to my first name.)

--
oans zwoa drei fia
#/&wrxfs$%pff3rx4656tspf&x=#%xkro&(w53x4bl=h)$%

Hi, Christof.

I'm not an expert, but I experienced the same troubles with MS
Access/Postgres in the past, so I think I can help you.
I also experienced the trouble with deinstalation of ODBC driver 08_00_0101
and thought that previous versions of drivers are better...
Well, it is not true! You will experience the same "#Deleted" with older
versions of ODBC driver, just leave Access connected to Postgres for two
hours...
You can't solve "#Deleted" by installing previous versions of ODBC drivers,
because the problem will remain although you will not realise it so
quickly...
You can solve it only by modifying your database on Postgres. You must
provide good primary keys to Access.
First of all, don't use varchar(x) primary keys or primary keys combined of
several columns! You must use non-floating NUMERICAL field (long) as primary
key. The best is to use autoincrement (BIGSERIAL) field as primary key.
So, add bigserial fields to all your tables, make them primary keys and it
will solve your problems with "#Deleted". The resolution is quite simple.
It definitely solved my problems and my Access aplication works quite good
now, with Postgre 8.0.3 and ODBC driver 08_00_0101.

Greetings,

Zlatko

----- Original Message -----
From: "Christof Thalhofer" <devnull(at)deganius(dot)de>
To: <pgsql-odbc(at)postgresql(dot)org>
Sent: Monday, July 18, 2005 7:24 PM
Subject: [ODBC] psqlodbc-08_00_0101 does not work with Ms Access

> Hello,
>
> first, I want to thank all these people working on Postgresql and Psqlodbc
> in any way for their good job!
>
> The last weekend, through one day and one night, I found out, that the
> ODBC
> Driver psqlodbc-08_00_0101 does not work correct in conjunction with MS
> Access 97.
>
> But correct works psqlodbc-07_02_0004!
>
> My database is running on PostgreSQL 8.0.3/Linux. By using MS Access 97
> with linked tables together with psqlodbc-08_00_0101 these problems occur:
>
> 1. Tables at Postgresql with a varchar(x) primary key or primary keys
> combined of several columns: If linked in Ms Access 97 all columns are
> marked as "Deleted". Access is not able to use the primary keys.
>
> 2. Columns in tables in Postgresql with type "text", when linked in Access
> are not recognized correctly as "Memo" by Access. Instead they are
> recognized as "Text" by Access, with a maximum of 255 (or 256?) chars.
> "Text" in Access is the same type as Varchar(256) in Postgresql. "Memo" in
> Access is nearly the same type as "text" or varchar (unlimited) in
> Postgreql.
>
> For me it took a long time to figure this out, because whenever i
> deinstalled psqlodbc-08_00_0101, the dlls remained on disk. Several times
> I
> installed an other psqlodbc-driver, but every time in reality psqlodbc-08_
> 00_0101 was used again by Access. I had to delete the psql*.dlls by hand
> to
> be able to reinstall psqlodbc-07_02_0004.
>
> Because the development is going on, it makes me insecure, that the
> function of psqlodbc was changed in a way that in newer versions it is
> unusable with MS Access.
>
> Alles Gute and have a good time,
>
> Christof
> (For mail change "devnull" to my first name.)
>
> --
> oans zwoa drei fia
> #/&wrx§fs$%pff3örx4656tspf&äx=#%xkr§o&(w53x4bl=äh)$˛˛˛˛ł%
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Hi, Christof.

I'm not an expert, but I experienced the same troubles with MS
Access/Postgres in the past, so I think I can help you.
I also experienced the trouble with deinstalation of ODBC driver 08_00_0101
and thought that previous versions of drivers are better...
Well, it is not true! You will experience the same "#Deleted" with older
versions of ODBC driver, just leave Access connected to Postgres for two
hours...
You can't solve "#Deleted" by installing previous versions of ODBC drivers,
because the problem will remain although you will not realise it so
quickly...
You can solve it only by modifying your database on Postgres. You must
provide good primary keys to Access.
First of all, don't use varchar(x) primary keys or primary keys combined of
several columns! You must use non-floating NUMERICAL field (long) as primary
key. The best is to use autoincrement (BIGSERIAL) field as primary key.
So, add bigserial fields to all your tables, make them primary keys and it
will solve your problems with "#Deleted". The resolution is quite simple.
It definitely solved my problems and my Access aplication works quite good
now, with Postgre 8.0.3 and ODBC driver 08_00_0101.

Greetings,

Zlatko

----- Original Message -----
From: "Christof Thalhofer" <devnull(at)deganius(dot)de>
To: <pgsql-odbc(at)postgresql(dot)org>
Sent: Monday, July 18, 2005 7:24 PM
Subject: [ODBC] psqlodbc-08_00_0101 does not work with Ms Access

> Hello,
>
> first, I want to thank all these people working on Postgresql and Psqlodbc
> in any way for their good job!
>
> The last weekend, through one day and one night, I found out, that the
> ODBC
> Driver psqlodbc-08_00_0101 does not work correct in conjunction with MS
> Access 97.
>
> But correct works psqlodbc-07_02_0004!
>
> My database is running on PostgreSQL 8.0.3/Linux. By using MS Access 97
> with linked tables together with psqlodbc-08_00_0101 these problems occur:
>
> 1. Tables at Postgresql with a varchar(x) primary key or primary keys
> combined of several columns: If linked in Ms Access 97 all columns are
> marked as "Deleted". Access is not able to use the primary keys.
>
> 2. Columns in tables in Postgresql with type "text", when linked in Access
> are not recognized correctly as "Memo" by Access. Instead they are
> recognized as "Text" by Access, with a maximum of 255 (or 256?) chars.
> "Text" in Access is the same type as Varchar(256) in Postgresql. "Memo" in
> Access is nearly the same type as "text" or varchar (unlimited) in
> Postgreql.
>
> For me it took a long time to figure this out, because whenever i
> deinstalled psqlodbc-08_00_0101, the dlls remained on disk. Several times
> I
> installed an other psqlodbc-driver, but every time in reality psqlodbc-08_
> 00_0101 was used again by Access. I had to delete the psql*.dlls by hand
> to
> be able to reinstall psqlodbc-07_02_0004.
>
> Because the development is going on, it makes me insecure, that the
> function of psqlodbc was changed in a way that in newer versions it is
> unusable with MS Access.
>
> Alles Gute and have a good time,
>
> Christof
> (For mail change "devnull" to my first name.)
>
> --
> oans zwoa drei fia
> #/&wrx§fs$%pff3örx4656tspf&äx=#%xkr§o&(w53x4bl=äh)$˛˛˛˛ł%
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Hi Zlatko,

thank you for your reply.

> I'm not an expert, but I experienced the same troubles with MS
> Access/Postgres in the past, so I think I can help you.
> I also experienced the trouble with deinstalation of ODBC driver
> 08_00_0101 and thought that previous versions of drivers are better...
> Well, it is not true! You will experience the same "#Deleted" with
> older versions of ODBC driver, just leave Access connected to Postgres
> for two hours...

we have a couple of Access-Applications running against a 8.03 Postgresql-
DB. With the old ODBC driver they stay connected the whole day long without
any problems. We also use in some tables varchar(x) primary keys and in
some other tables primary keys combined of several columns. Without any
problems for years and with Postgresql 8.xx for months now!

So I stay with my opinion: there are problems with the newer 8.. ODBC
drivers and Access, which do not occur in older versions, as I described in
my former posting.

Alles Gute,

Christof
(Fr Mail "devnull" in meinen Vornamen ndern.)

--
oans zwoa drei fia
#/&wrxfs$%pff3rx4656tspf&x=#%xkro&(w53x4bl=h)$%

Zlatko Matić schrieb:

> several columns! You must use non-floating NUMERICAL field (long) as
> primary
> key. The best is to use autoincrement (BIGSERIAL) field as primary key.
> So, add bigserial fields to all your tables, make them primary keys
> and it
> will solve your problems with "#Deleted".

BIGserials are 8 byte integers.
Access can handle only 4 byte integer fields. The ODBC driver maps the
bigints down to 4 bytes as default setting.
If Access can't actually use the additional 4 bytes why bloat the tables
and add maintenance work to the driver?
It's all fine if your bigserials stay small enough.
Access "Long Integer" are signed and therefore range from
-2.147.483.648 to 2.147.483.647.
That matches "integer" in PG.

OK..thanks for information. I didn't know that.
Greetings,

Zlatko

----- Original Message -----
From: "Andreas" <maps(dot)on(at)gmx(dot)net>
To: "Zlatko Matić" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Sent: Monday, July 25, 2005 4:05 PM
Subject: Re: [ODBC] psqlodbc-08_00_0101 does not work with Ms Access

>
> Zlatko Matić schrieb:
>
>> several columns! You must use non-floating NUMERICAL field (long) as
>> primary
>> key. The best is to use autoincrement (BIGSERIAL) field as primary key.
>> So, add bigserial fields to all your tables, make them primary keys and
>> it
>> will solve your problems with "#Deleted".
>
> BIGserials are 8 byte integers.
> Access can handle only 4 byte integer fields. The ODBC driver maps the
> bigints down to 4 bytes as default setting.
> If Access can't actually use the additional 4 bytes why bloat the tables
> and add maintenance work to the driver?
> It's all fine if your bigserials stay small enough.
> Access "Long Integer" are signed and therefore range from -2.147.483.648
> to 2.147.483.647.
> That matches "integer" in PG.
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

As I said I'm not an expert...
I wish you to solve your problem on the best possible way.
In my case adding of new colums with bigserial fields as primary keys solved
it. Andreas mentioned that it is not good to use bigserial but just serial,
so I will change it now to serial.
Good luck,

Zlatko

----- Original Message -----
From: "Christof Thalhofer" <devnull(at)deganius(dot)de>
To: <pgsql-odbc(at)postgresql(dot)org>
Sent: Sunday, July 24, 2005 11:28 PM
Subject: Re: [ODBC] psqlodbc-08_00_0101 does not work with Ms Access

> Hi Zlatko,
>
> thank you for your reply.
>
>> I'm not an expert, but I experienced the same troubles with MS
>> Access/Postgres in the past, so I think I can help you.
>> I also experienced the trouble with deinstalation of ODBC driver
>> 08_00_0101 and thought that previous versions of drivers are better...
>> Well, it is not true! You will experience the same "#Deleted" with
>> older versions of ODBC driver, just leave Access connected to Postgres
>> for two hours...
>
> we have a couple of Access-Applications running against a 8.03 Postgresql-
> DB. With the old ODBC driver they stay connected the whole day long
> without
> any problems. We also use in some tables varchar(x) primary keys and in
> some other tables primary keys combined of several columns. Without any
> problems for years and with Postgresql 8.xx for months now!
>
> So I stay with my opinion: there are problems with the newer 8.. ODBC
> drivers and Access, which do not occur in older versions, as I described
> in
> my former posting.
>
> Alles Gute,
>
> Christof
> (Für Mail "devnull" in meinen Vornamen ändern.)
>
> --
> oans zwoa drei fia
> #/&wrx§fs$%pff3örx4656tspf&äx=#%xkr§o&(w53x4bl=äh)$˛˛˛˛ł%
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

unsubscribe

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Zlatko Matiæ
Sent: Monday, July 25, 2005 11:42 AM
To: Christof Thalhofer; pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] psqlodbc-08_00_0101 does not work with Ms Access

As I said I'm not an expert...
I wish you to solve your problem on the best possible way.
In my case adding of new colums with bigserial fields as primary keys solved
it. Andreas mentioned that it is not good to use bigserial but just serial,
so I will change it now to serial.
Good luck,

Zlatko

----- Original Message -----
From: "Christof Thalhofer" <devnull(at)deganius(dot)de>
To: <pgsql-odbc(at)postgresql(dot)org>
Sent: Sunday, July 24, 2005 11:28 PM
Subject: Re: [ODBC] psqlodbc-08_00_0101 does not work with Ms Access

> Hi Zlatko,
>
> thank you for your reply.
>
>> I'm not an expert, but I experienced the same troubles with MS
>> Access/Postgres in the past, so I think I can help you.
>> I also experienced the trouble with deinstalation of ODBC driver
>> 08_00_0101 and thought that previous versions of drivers are better...
>> Well, it is not true! You will experience the same "#Deleted" with
>> older versions of ODBC driver, just leave Access connected to Postgres
>> for two hours...
>
> we have a couple of Access-Applications running against a 8.03 Postgresql-
> DB. With the old ODBC driver they stay connected the whole day long
> without
> any problems. We also use in some tables varchar(x) primary keys and in
> some other tables primary keys combined of several columns. Without any
> problems for years and with Postgresql 8.xx for months now!
>
> So I stay with my opinion: there are problems with the newer 8.. ODBC
> drivers and Access, which do not occur in older versions, as I described
> in
> my former posting.
>
> Alles Gute,
>
> Christof
> (Für Mail "devnull" in meinen Vornamen ändern.)
>
> --
> oans zwoa drei fia
> #/&wrx§fs$%pff3örx4656tspf&äx=#%xkr§o&(w53x4bl=äh)$˛˛˛˛ł%
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend