Thread: Re: Insight ODBC driver
Please note that postodbc-users@listserv.direct.net is no longer considered the *official* support channel for the ODBC driver... Reposting to pgsql-interfaces@postgresql.org On Thu, 16 Apr 1998, Olaf Mittelstaedt wrote: > Hello all, > > after installation of PostgreSQL 6.3.1 and the newest Insight ODBC > driver there's only one problem left: when using Access 2.0 (or MS > Query 1.0) on a Windows NT 4.0 system all tables are read only, even > if a unique index is present. > > Any hints appreciated. > > Regards, > Olaf > -- > Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de > Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm > Tel.: +49 (0)731-502-8220 Fax: -8270 > > Tertium non datur. >
Olaf, Did you check to see if the 'ReadOnly' checkbox was selected in the data source configuration. This is a new feature of the driver and it does default to readonly. This is available on the Data Source configuration dialog box and the File DSN configuration box. I'm not sure which one you are using. Byron The Hermit Hacker wrote: > Please note that postodbc-users@listserv.direct.net is no longer > considered the *official* support channel for the ODBC driver... > > Reposting to pgsql-interfaces@postgresql.org > > On Thu, 16 Apr 1998, Olaf Mittelstaedt wrote: > > > Hello all, > > > > after installation of PostgreSQL 6.3.1 and the newest Insight ODBC > > driver there's only one problem left: when using Access 2.0 (or MS > > Query 1.0) on a Windows NT 4.0 system all tables are read only, even > > if a unique index is present. > > > > Any hints appreciated. > > > > Regards, > > Olaf > > -- > > Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de > > Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm > > Tel.: +49 (0)731-502-8220 Fax: -8270 > > > > Tertium non datur. > >
The Hermit Hacker wrote: > Please note that postodbc-users@listserv.direct.net is no longer > considered the *official* support channel for the ODBC driver... > > Reposting to pgsql-interfaces@postgresql.org > > On Thu, 16 Apr 1998, Olaf Mittelstaedt wrote: > > > Hello all, > > > > after installation of PostgreSQL 6.3.1 and the newest Insight ODBC > > driver there's only one problem left: when using Access 2.0 (or MS > > Query 1.0) on a Windows NT 4.0 system all tables are read only, even > > if a unique index is present. > > 1. I am not as familiar with Access 2.0. But the 7.0 and 97 versions require a unique key to be specified at link time for an update to be allowed. I want to be sure that you have done that, and that you not referring to the "create unique index" statement which creates the index in the database table on the server. 2. There is a read-only check box on the data source setup dialog. This was put in to prevent unintentional key strokes from getting into the database. This is remarkably easy to do in MS Access. Just unchecked the box for your data source.
Attachment
The Hermit Hacker wrote: > Please note that postodbc-users@listserv.direct.net is no longer > considered the *official* support channel for the ODBC driver... > > Reposting to pgsql-interfaces@postgresql.org > > On Thu, 16 Apr 1998, Olaf Mittelstaedt wrote: > > > Hello all, > > > > after installation of PostgreSQL 6.3.1 and the newest Insight ODBC > > driver there's only one problem left: when using Access 2.0 (or MS > > Query 1.0) on a Windows NT 4.0 system all tables are read only, even > > if a unique index is present. > > 1. I am not as familiar with Access 2.0. But the 7.0 and 97 versions require a unique key to be specified at link time for an update to be allowed. I want to be sure that you have done that, and that you not referring to the "create unique index" statement which creates the index in the database table on the server. 2. There is a read-only check box on the data source setup dialog. This was put in to prevent unintentional key strokes from getting into the database. This is remarkably easy to do in MS Access. Just unchecked the box for your data source.
Attachment
Thank you Byron and David for your answers (and Marc for redirecting my message to pqsql-interfaces). Here a few more details on my problem. > 1. I am not as familiar with Access 2.0. But the 7.0 and 97 versions > require a unique key to be specified at link time for an update to be > allowed. I want to be sure that you have done that, and that you not > referring to the "create unique index" statement which creates the index > in the database table on the server. Access 2.0 should automatically use a unique index on the database server if it's present. Below the statements I've used to create, populate and test the table. create table test3(i1 int4,i2 int4, i3 int4); create unique index i_test3_i1 on test3(i1); insert into test3 values(1,2,3); insert into test3 values(2,3,4); insert into test3 values(3,4,5); \d Database = vorwahl +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | postgres | i_test3_i1 | index | | postgres | test3 | table | +------------------+----------------------------------+----------+ \d i_test3_i1 Table = i_test3_i1 +-----------------+----------------------------------+-------+ | ld | Type | Length| +-----------------+----------------------------------+-------+ | i1 | int4 | 4 | +-----------------+----------------------------------+-------+ select * from test3; i1|i2|i3 --+--+-- 1| 2| 3 2| 3| 4 3| 4| 5 (3 rows) insert into test3 values(3,3,3); ERROR: Cannot insert a duplicate key into a unique index After linking the table above to an Access 2.0 database I can see the index, but Access thinks it's not unique. > 2. There is a read-only check box on the data source setup dialog. This was > put in to prevent unintentional key strokes from getting into the database. > This is remarkably easy to do in MS Access. Just unchecked the box for your > data source. The read-only checkbox on the data source dialog is unchecked. It can't be an authentication problem because if I create a local index for the linked table I can add/delete rows. Regards, Olaf -- Olaf Mittelstaedt - IuK - mittelstaedt@fh-ulm.de Fachhochschule Ulm Prittwitzstr. 10 89075 Ulm Tel.: +49 (0)731-502-8220 Fax: -8270 Tertium non datur.
Olaf Mittelstaedt wrote: > Thank you Byron and David for your answers (and Marc for redirecting > my message to pqsql-interfaces). > > Here a few more details on my problem. > > > 1. I am not as familiar with Access 2.0. But the 7.0 and 97 versions > > require a unique key to be specified at link time for an update to be > > allowed. I want to be sure that you have done that, and that you not > > referring to the "create unique index" statement which creates the index > > in the database table on the server. > > Access 2.0 should automatically use a unique index on the database > server if it's present. Below the statements I've used to create, > populate and test the table. > Access 7.0 & 97 do not automatically use a unique index on the database server if it's present. They need to be told the key at link time. I do not possess a 2.0. version. I am including a portion of a drafted of the new FAQ. I hope this helps. PS. You are using a 32 bit Windows OS aren't you.
Attachment
Hello,
At 14.03 17/04/98 -0400, you wrote:
From the FAQ part:
...
>How do I get my application to recognize primary keys?
>SQLPrimaryKeys() is implemented in the driver. As of the driver's
release, >however, there was no way to query the PostgreSQL system tables
to discover a >table's primary key. Therefore the following convention was
used. The driver >queries the system tables in search of a unique index
named with the using >"{table}_key". Example:
>create table foo (id integer, name varchar(20));
>create unique index foo_key on foo using btree(id);
>Creating this index does not guarantee that your application is using the
>SQLPrimaryKeys() call. For example, MS Access 7.0 & 97 require the user
to >manually specify the key at link time. This key specification is
required to modify >a table from MA Access. See "Why does the PosgreSQL
backend crash every time I >browse some tables in MS Access?".
actually when you specify something like:
create table test06
(
Posizione int4 not null primary key,
Testo char(50),
Campo float8,
DataEOra datetime
);
postgres automagically creates an index named test06_pkey (note the 'p' in
fron of 'key'). I think changing the ODBC driver to look for such type of
index, instead of '{table}_key', would simplify things a lot.
Hope it helps.
P.S. My compliments to all the great development effort coming up on PostODBC.
Dr. Sbragion Denis
InfoTecna
Tel, Fax: +39 39 2324054
URL: http://space.tin.it/internet/dsbragio