Thread: PostgreSQL ODBC driver: recordset not updatable
Hi! I have installed the postgresql ODBC driver (6.5) under W98 and I am trying to access the PostgreSQL 7.0.2 database on Linux but I cannot edit or modify the data from MS Access 2000. The Readonly check box is not marked, so ? Is this a bug ? Are the components incompatible? Any help will be appreciated. Evelio
On 4 Dec 2000, at 19:44, Evelio Martinez wrote: > I have installed the postgresql ODBC driver (6.5) under W98 and I am > trying to access the PostgreSQL 7.0.2 database on Linux but I cannot > edit or modify the data from MS Access 2000. > > The Readonly check box is not marked, so ? There are *two* read-only options, did you fix both, and did you re- link your tables after? Do you have a primary key for your table? There is a FAQ for PostgreSQL + Microsoft Access at http://www.scw.org/pgaccess. -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)
Evelio,
ODBC drivers on Micro$oft windows, typically do not support update, delete
or insert (db modification) through them when used against non-microsoft
databases. Part of the price you pay for ODBC. You will have to write server
side functions and execute them through the odbc connections like:
cnn_postgresql_ado_connection.Execute("SELECT function_name('" + arg1 + "',
'" + arg2 + "', '" + arg3 + "');")
using vba.
Try all three types of query before writing lots of functions. ODBC may
support some of them.
You simply write a parameterized function. In this function you put your
update, insert, or delete query. When you execute through ODBC it will work.
Details are in the PostGreSQL online manual. Get the searchable PDF that
Bruce Momjian is writing. It is excellent. It got me through 90% of the
platform specific stuff that I needed to learn.
L8,
Neil
----- Original Message -----
From: "Evelio Martinez" <evelio.martinez@testanet.com>
To: <pgsql-interfaces@postgresql.org>
Sent: Monday, December 04, 2000 1:44 PM
Subject: [INTERFACES] PostgreSQL ODBC driver: recordset not updatable
>
>
> Hi!
>
> I have installed the postgresql ODBC driver (6.5) under W98 and I am
> trying to access the PostgreSQL 7.0.2 database on Linux
> but I cannot edit or modify the data from MS Access 2000.
>
> The Readonly check box is not marked, so ?
>
> Is this a bug ?
> Are the components incompatible?
>
> Any help will be appreciated.
>
> Evelio
>
>
On 4 Dec 2000, at 15:18, Neil Davis wrote: > ODBC drivers on Micro$oft windows, typically do not support update, > delete or insert (db modification) through them when used against > non-microsoft databases. Part of the price you pay for ODBC. You will > have to write server side functions and execute them through the odbc > connections like: In MS Access, you can update, delete, insert, either by (a) direct linked tables, or (b) ODBCDirect. You can't when using "Pass- through queries". In VB, when I simply bind a data object to a PG recordset, that's updatable too. You might want to look into your problem; I don't know exactly what you've been trying w/ODBC + PG, but, in at least several areas, I've been able to update recordsets w/o problem and w/o parameterized queries. http://www.scw.org/pgaccess may have some helpful info. It's mostly Access-focused, but has general info about ODBC + PG. Good luck! -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)
Joel, I love it when I am proved wrong and another resource is dumped into my lap! That is how I learn. I admit I was generalizing. I did a little research into odbc on windows after I wrote that and found that the MySQL driver works really well. Open source ODBC on windows has come a long way since I last played with it. thanks! Neil >From: "Joel Burton" <jburton@scw.org> >To: "Neil Davis" <npdavis@hotmail.com> >CC: <pgsql-interfaces@postgresql.org> >Subject: Re: [INTERFACES] PostgreSQL ODBC driver: recordset not updatable >Date: Mon, 4 Dec 2000 17:00:30 -0500 > >On 4 Dec 2000, at 15:18, Neil Davis wrote: > > > ODBC drivers on Micro$oft windows, typically do not support update, > > delete or insert (db modification) through them when used against > > non-microsoft databases. Part of the price you pay for ODBC. You will > > have to write server side functions and execute them through the odbc > > connections like: > >In MS Access, you can update, delete, insert, either by (a) direct >linked tables, or (b) ODBCDirect. You can't when using "Pass- >through queries". > >In VB, when I simply bind a data object to a PG recordset, that's >updatable too. > >You might want to look into your problem; I don't know exactly >what you've been trying w/ODBC + PG, but, in at least several >areas, I've been able to update recordsets w/o problem and w/o >parameterized queries. > >http://www.scw.org/pgaccess may have some helpful info. It's >mostly Access-focused, but has general info about ODBC + PG. > >Good luck! > >-- >Joel Burton, Director of Information Systems -*- jburton@scw.org >Support Center of Washington (www.scw.org) _____________________________________________________________________________________ Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
On Mon, 4 Dec 2000, Neil Davis wrote: > Evelio, > ODBC drivers on Micro$oft windows, typically do not support update, delete > or insert (db modification) through them when used against non-microsoft > databases. Part of the price you pay for ODBC. You will have to write server > side functions and execute them through the odbc connections like: I don't know where you've been, but every ODBC driver I've used does that. Now, some go further and offer updatable resultsets too. The mysql ODBC driver does not support updatable resultsets, because the MySQL server does not support cursors. But again, ODBC supports this if the driver vendor chooses to code the proper support. The full ODBC API is published. BTW, The PostgreSQL ODBC driver supports updatable resultsets too. ... > > I have installed the postgresql ODBC driver (6.5) under W98 and I am > > trying to access the PostgreSQL 7.0.2 database on Linux > > but I cannot edit or modify the data from MS Access 2000. > > > > The Readonly check box is not marked, so ? There are two ReadOnly check boxes. Make sure both are off. By default both are enabled (that is a very silly default, btw). Also, you will need to have proper permissions to the database. See GRANT and REVOKE sections in manual. Tom
Joel Burton escribió: > On 4 Dec 2000, at 19:44, Evelio Martinez wrote: > > > I have installed the postgresql ODBC driver (6.5) under W98 and I am > > trying to access the PostgreSQL 7.0.2 database on Linux but I cannot > > edit or modify the data from MS Access 2000. > > > > The Readonly check box is not marked, so ? > > There are *two* read-only options, did you fix both, and did you re- > link your tables after? yes > > > Do you have a primary key for your table? is it important?. I have a unique key instead of a primary key. > > > There is a FAQ for PostgreSQL + Microsoft Access at > http://www.scw.org/pgaccess. > -- > Joel Burton, Director of Information Systems -*- jburton@scw.org > Support Center of Washington (www.scw.org)
On Tue, 5 Dec 2000, Evelio Martinez wrote: > > There are *two* read-only options, did you fix both, and did you re- > > link your tables after? > > yes > > > Do you have a primary key for your table? > > is it important?. I have a unique key instead of a primary key. Yes very important. Isn't a primary key the same as a unique key, or just a key in general? Access has to have a unique field to make sure it updates the correct record. If Access doesn't recognise a unique field then it won't let you update the recordset. What recordset are you trying to update? Is it a linked table or a query? Also, you must be sure that you relink your tables in Access if you change something in PG. If you don't, Access will not see the change. You may want to write some VB code to relink all your tables for you. There is an example in the Developer Solutions database supplied with Access (you will also need the Northwind db). -Cedar
I agree. Microsoft has no control over how someone makes their ODBC driver. Sometimes people blame them [Microsoft] for more than they do. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Joel Burton" <jburton@scw.org> To: "Neil Davis" <npdavis@hotmail.com> Cc: <pgsql-interfaces@postgresql.org> Sent: Monday, December 04, 2000 5:00 PM Subject: Re: [INTERFACES] PostgreSQL ODBC driver: recordset not updatable > On 4 Dec 2000, at 15:18, Neil Davis wrote: > > > ODBC drivers on Micro$oft windows, typically do not support update, > > delete or insert (db modification) through them when used against > > non-microsoft databases. Part of the price you pay for ODBC. You will > > have to write server side functions and execute them through the odbc > > connections like: > > In MS Access, you can update, delete, insert, either by (a) direct > linked tables, or (b) ODBCDirect. You can't when using "Pass- > through queries". > > In VB, when I simply bind a data object to a PG recordset, that's > updatable too. > > You might want to look into your problem; I don't know exactly > what you've been trying w/ODBC + PG, but, in at least several > areas, I've been able to update recordsets w/o problem and w/o > parameterized queries. > > http://www.scw.org/pgaccess may have some helpful info. It's > mostly Access-focused, but has general info about ODBC + PG. > > Good luck! > > -- > Joel Burton, Director of Information Systems -*- jburton@scw.org > Support Center of Washington (www.scw.org)
> Also, you must be sure that you relink your tables in Access if you > change something in PG. If you don't, Access will not see the change. > You may want to write some VB code to relink all your tables for you. > There is an example in the Developer Solutions database supplied with > Access (you will also need the Northwind db). There's also some code to relink tables in the PG + Access FAQ at http://www.scw.org/pgaccess -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)