Thread: ODBC-Problem
HI I have just moved my old MS Access Database to a postgres database via odbc. all went okay, and i can access the database in MS Access via ODBC. Only a Client programm that i wrote can´t access the database anymore via ODBC. The Visual BAsic Database programm says that the database itself is updateable, but the tables in it aren´t updateable, and all tries to connect to a table in VB via the OpenRecordset method fail with the error message "parse error near [name of the table]" or VB tells that it´s a connection error. That´s weird. MS Access has no problems connecting and writing to the database. VB´s Visual Database Manager can connect to the database, but can only read tables. MY VB programm can´t even connect to the tables, but it worked quite good via ODBC to the Access Database. I don´t know what i can anymore, so please help if you know a solution. Christoph Nelles
Add a primary key index to the tables that you want to be able to update. Access relies on the primary key for a lot of things. I recommend you add a primary key to all postgres tables. -----Original Message----- From: Evil Azrael [SMTP:EvilAzrael@EvilAzrael.de] Sent: Sunday, December 31, 2000 8:50 PM To: pgsql-interfaces@postgresql.org Subject: ODBC-Problem HI I have just moved my old MS Access Database to a postgres database via odbc. all went okay, and i can access the database in MS Access via ODBC. Only a Client programm that i wrote can?t access the database anymore via ODBC. The Visual BAsic Database programm says that the database itself is updateable, but the tables in it aren?t updateable, and all tries to connect to a table in VB via the OpenRecordset method fail with the error message "parse error near [name of the table]" or VB tells that it?s a connection error. That?s weird. MS Access has no problems connecting and writing to the database. VB?s Visual Database Manager can connect to the database, but can only read tables. MY VB programm can?t even connect to the tables, but it worked quite good via ODBC to the Access Database. I don?t know what i can anymore, so please help if you know a solution. Christoph Nelles
Also, make sure the ODBC driver for postgres is set for updating. By default it is read only. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Michael Davis" <mdavis@sevainc.com> To: <pgsql-interfaces@postgresql.org> Sent: Tuesday, January 02, 2001 7:54 PM Subject: [INTERFACES] RE: ODBC-Problem > Add a primary key index to the tables that you want to be able to update. > Access relies on the primary key for a lot of things. I recommend you add > a primary key to all postgres tables. > > -----Original Message----- > From: Evil Azrael [SMTP:EvilAzrael@EvilAzrael.de] > Sent: Sunday, December 31, 2000 8:50 PM > To: pgsql-interfaces@postgresql.org > Subject: ODBC-Problem > > HI > > I have just moved my old MS Access Database to a postgres database via > odbc. all went okay, and i can access the database in MS Access via > ODBC. Only a Client programm that i wrote can?t access the database > anymore via ODBC. The Visual BAsic Database programm says that the > database itself is updateable, but the tables in it aren?t updateable, > and all tries to connect to a table in VB via the OpenRecordset method > fail with the error message "parse error near [name of the table]" or VB > tells that it?s a connection error. > That?s weird. > MS Access has no problems connecting and writing to the database. > VB?s Visual Database Manager can connect to the database, but can only > read tables. > MY VB programm can?t even connect to the tables, but it worked quite > good via ODBC to the Access Database. > > > I don?t know what i can anymore, so please help if you know a solution. > > > Christoph Nelles
This sounds strange. I can connect to many tables in less than a minute. I can open tables in seconds regardless of howmany rows are in the tables. Access usually only selects 20-30 rows at a time. My ODBC connections to PostgreSQL in Access are fast. Not as fast as opening local Access tables by much faster than 1 minute. You can try different options in your connect statement. Some options work with ODBC, others don't. If this does not help, you will have to email me your VBA code. -----Original Message----- From: Evil Azrael [SMTP:EvilAzrael@EvilAzrael.de] Sent: Wednesday, January 03, 2001 1:44 AM To: mdavis@sevainc.com Subject: Re: ODBC-Problem thanx for the answer. i have in every table a primary index called ID, but ODBC and VB don?t accept it as a primary keys. and the performance of the OBDC connection is rather bad. he needs almost 1 minute to connect to a table with 300k rows, for just appending a few lines. even sql calls fails. i postponed the whole transfer for a time and check it then again. but thanx for the reply Christoph Nelles Michael Davis wrote: > > Add a primary key index to the tables that you want to be able to update. > Access relies on the primary key for a lot of things. I recommend you add > a primary key to all postgres tables. > > -----Original Message----- > From: Evil Azrael [SMTP:EvilAzrael@EvilAzrael.de] > Sent: Sunday, December 31, 2000 8:50 PM > To: pgsql-interfaces@postgresql.org > Subject: ODBC-Problem > > HI > > I have just moved my old MS Access Database to a postgres database via > odbc. all went okay, and i can access the database in MS Access via > ODBC. Only a Client programm that i wrote can?t access the database > anymore via ODBC. The Visual BAsic Database programm says that the > database itself is updateable, but the tables in it aren?t updateable, > and all tries to connect to a table in VB via the OpenRecordset method > fail with the error message "parse error near [name of the table]" or VB > tells that it?s a connection error. > That?s weird. > MS Access has no problems connecting and writing to the database. > VB?s Visual Database Manager can connect to the database, but can only > read tables. > MY VB programm can?t even connect to the tables, but it worked quite > good via ODBC to the Access Database. > > I don?t know what i can anymore, so please help if you know a solution. > > Christoph Nelles