Thread: 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)$�����%
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@deganius.de> To: <pgsql-odbc@postgresql.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@deganius.de> To: <pgsql-odbc@postgresql.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
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.on@gmx.net> To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr> Cc: <pgsql-odbc@postgresql.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
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)$�����%
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@deganius.de> To: <pgsql-odbc@postgresql.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@postgresql.org so that your > message can get through to the mailing list cleanly
unsubscribe -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Zlatko Matiæ Sent: Monday, July 25, 2005 11:42 AM To: Christof Thalhofer; pgsql-odbc@postgresql.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@deganius.de> To: <pgsql-odbc@postgresql.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@postgresql.org so that your > message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend