pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: PostgreSQL ODBC driver: recordset not updatable
10+ messages / 7 participants
[nested] [flat]
* Re: PostgreSQL ODBC driver: recordset not updatable
@ 1970-01-01 00:00 npdavis@hotmail.com
0 siblings, 0 replies; 10+ messages in thread
From: npdavis@hotmail.com @ 1970-01-01 00:00 UTC (permalink / raw)
To: jburton@scw.org; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* PostgreSQL ODBC driver: recordset not updatable
@ 2000-12-04 18:44 Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 19:52 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
2000-12-04 20:18 ` Re: PostgreSQL ODBC driver: recordset not updatable Neil Davis <npdavis@hotmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Evelio Martinez @ 2000-12-04 18:44 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
@ 2000-12-04 19:52 ` Joel Burton <jburton@scw.org>
2000-12-05 09:23 ` Re: PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
1 sibling, 1 reply; 10+ messages in thread
From: Joel Burton @ 2000-12-04 19:52 UTC (permalink / raw)
To: Evelio Martinez <evelio.martinez@testanet.com>; +Cc: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 19:52 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
@ 2000-12-05 09:23 ` Evelio Martinez <evelio.martinez@testanet.com>
2000-12-05 11:21 ` Re: PostgreSQL ODBC driver: recordset not updatable Cedar Cox <cedarc@visionforisrael.com>
2000-12-05 17:14 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
0 siblings, 2 replies; 10+ messages in thread
From: Evelio Martinez @ 2000-12-05 09:23 UTC (permalink / raw)
To: Joel Burton <jburton@scw.org>; pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 19:52 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
2000-12-05 09:23 ` Re: PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
@ 2000-12-05 11:21 ` Cedar Cox <cedarc@visionforisrael.com>
1 sibling, 0 replies; 10+ messages in thread
From: Cedar Cox @ 2000-12-05 11:21 UTC (permalink / raw)
To: Evelio Martinez <evelio.martinez@testanet.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 19:52 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
2000-12-05 09:23 ` Re: PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
@ 2000-12-05 17:14 ` Joel Burton <jburton@scw.org>
1 sibling, 0 replies; 10+ messages in thread
From: Joel Burton @ 2000-12-05 17:14 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; +Cc: pgsql-interfaces
> 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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
@ 2000-12-04 20:18 ` Neil Davis <npdavis@hotmail.com>
2000-12-04 22:00 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
2000-12-05 04:33 ` Re: PostgreSQL ODBC driver: recordset not updatable Tom Samplonius <tom@sdf.com>
1 sibling, 2 replies; 10+ messages in thread
From: Neil Davis @ 2000-12-04 20:18 UTC (permalink / raw)
To: Evelio Martinez <evelio.martinez@testanet.com>; +Cc: pgsql-interfaces
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 20:18 ` Re: PostgreSQL ODBC driver: recordset not updatable Neil Davis <npdavis@hotmail.com>
@ 2000-12-04 22:00 ` Joel Burton <jburton@scw.org>
2000-12-05 15:21 ` Re: PostgreSQL ODBC driver: recordset not updatable Adam Lang <aalang@rutgersinsurance.com>
1 sibling, 1 reply; 10+ messages in thread
From: Joel Burton @ 2000-12-04 22:00 UTC (permalink / raw)
To: Neil Davis <npdavis@hotmail.com>; +Cc: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 20:18 ` Re: PostgreSQL ODBC driver: recordset not updatable Neil Davis <npdavis@hotmail.com>
2000-12-04 22:00 ` Re: PostgreSQL ODBC driver: recordset not updatable Joel Burton <jburton@scw.org>
@ 2000-12-05 15:21 ` Adam Lang <aalang@rutgersinsurance.com>
0 siblings, 0 replies; 10+ messages in thread
From: Adam Lang @ 2000-12-05 15:21 UTC (permalink / raw)
To: ; +Cc: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: PostgreSQL ODBC driver: recordset not updatable
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 20:18 ` Re: PostgreSQL ODBC driver: recordset not updatable Neil Davis <npdavis@hotmail.com>
@ 2000-12-05 04:33 ` Tom Samplonius <tom@sdf.com>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Samplonius @ 2000-12-05 04:33 UTC (permalink / raw)
To: Neil Davis <npdavis@hotmail.com>; +Cc: Evelio Martinez <evelio.martinez@testanet.com>; pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2000-12-05 17:14 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Re: PostgreSQL ODBC driver: recordset not updatable npdavis@hotmail.com
2000-12-04 18:44 PostgreSQL ODBC driver: recordset not updatable Evelio Martinez <evelio.martinez@testanet.com>
2000-12-04 19:52 ` Joel Burton <jburton@scw.org>
2000-12-05 09:23 ` Evelio Martinez <evelio.martinez@testanet.com>
2000-12-05 11:21 ` Cedar Cox <cedarc@visionforisrael.com>
2000-12-05 17:14 ` Joel Burton <jburton@scw.org>
2000-12-04 20:18 ` Neil Davis <npdavis@hotmail.com>
2000-12-04 22:00 ` Joel Burton <jburton@scw.org>
2000-12-05 15:21 ` Adam Lang <aalang@rutgersinsurance.com>
2000-12-05 04:33 ` Tom Samplonius <tom@sdf.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox