Received: from u1.abs.net (root@u1.abs.net [207.114.0.131]) by hub.org
	(8.8.8/8.7.5) with ESMTP id OAA13858 for
	<pgsql-interfaces@postgreSQL.org>;
	Fri, 17 Apr 1998 14:39:00 -0400 (EDT)
Received: from insightdist.com (nobody@localhost)
	by u1.abs.net (8.8.5/8.8.5) with UUCP id OAA25988
	for pgsql-interfaces@postgreSQL.org;
	Fri, 17 Apr 1998 14:15:04 -0400 (EDT)
X-Authentication-Warning: u1.abs.net: nobody set sender to
	insightdist.com!daveh using -f
Received: by insightdist.com (AIX 3.2/UCB 5.64/4.03)
	id AA35184; Fri, 17 Apr 1998 14:10:28 -0400
Received: from daveh by ceodev (AIX 4.1/UCB 5.64/4.03)
	id AA78252; Fri, 17 Apr 1998 14:05:02 -0400
Message-Id: <35379956.9CF38CD0@insightdist.com>
Date: Fri, 17 Apr 1998 14:03:02 -0400
From: David Hartwig <daveh@insightdist.com>
Organization: Insight Distribution Systems
X-Mailer: Mozilla 4.05 [en] (Win95; I)
Mime-Version: 1.0
To: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>,
	pgsql-interfaces <pgsql-interfaces@postgreSQL.org>
Subject: Can't Update from MS Access  (Was - Insight ODBC driver)
References: <199804170615.IAA01926@gate.va.fh-ulm.de>
Content-Type: multipart/mixed; boundary="------------28FC7B415F8C27FE394B42D6"

This is a multi-part message in MIME format.
--------------28FC7B415F8C27FE394B42D6
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit



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.

--------------28FC7B415F8C27FE394B42D6
Content-Type: text/html; charset=us-ascii; name="PSQLODBC_FAQ.htm"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="PSQLODBC_FAQ.htm"
Content-Base: "file:///G|/pages/work/PSQLODBC_FAQ.htm"

<HTML>
<HEAD>
   <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
   <META NAME="Author" CONTENT="David Hartwig">
   <META NAME="GENERATOR" CONTENT="Mozilla/4.05 [en] (Win95; I) [Netscape]">
   <TITLE>PSQLODBC FAQ</TITLE>
</HEAD>
<BODY>

<CENTER><FONT SIZE=+2>PSQL ODBC - FAQ</FONT></CENTER>

<CENTER><FONT SIZE=+2></FONT></CENTER>
<FONT SIZE=+1><U>How do I get my application to recognize primary keys?</U>&nbsp;</FONT>
<BR>SQLPrimaryKeys() is implemented in the driver.&nbsp;&nbsp; As of the
driver's release, however, there was no way to query the PostgreSQL system
tables to discover a table's primary key.&nbsp; Therefore the following
convention was used.&nbsp;&nbsp; The driver queries the system tables in
search of a unique index named with the using "{table}_key".&nbsp;&nbsp;
Example:
<BLOCKQUOTE><B><FONT COLOR="#333333">create table foo&nbsp; (id integer,
name varchar(20));</FONT></B>
<BR><B><FONT COLOR="#333333">create unique index foo_key on foo using btree(id);</FONT></B></BLOCKQUOTE>
<FONT COLOR="#333333">Creating this index does not guarantee that your
application is using the </FONT>SQLPrimaryKeys() call.&nbsp;&nbsp; For
example, MS Access 7.0 &amp; 97 require the user to manually specify the
key at link time.&nbsp; This key specification is required to modify a
table from MA Access.&nbsp;&nbsp; See "Why does the PosgreSQL backend crash
every time I browse some tables in MS Access?".
<BR>&nbsp;

<P><U><FONT SIZE=+1>Why does the PostgreSQL backend crash every time I
browse some tables in MS Access?</FONT></U>
<BR>When an Access user specifies a key at link time, the key is used to
generate queries to browse through the rows of the table.&nbsp;&nbsp;&nbsp;
When the key is multipart&nbsp; the query looks something like:
<BLOCKQUOTE><B>--&nbsp;&nbsp; This is a 3 part key</B>
<BR><B>select ... from foo where</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?") OR&nbsp;&nbsp;&nbsp;&nbsp;
-- line 1</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?") OR&nbsp;&nbsp;&nbsp;&nbsp;
-- line 2</B>
<BR><B>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
...</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?") OR&nbsp;&nbsp;&nbsp;&nbsp;
--&nbsp; line 27</B>
<BR><B>&nbsp;&nbsp;&nbsp; (v1 = "?" AND v2 = "?" AND v3 ="?");&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
--&nbsp; line 28</B></BLOCKQUOTE>
Unforunatly as of the latest release of the driver, this kind of query
cause the optimizer in the PostgreSQL backend to devour&nbsp; exponential
amounts of memory and eventually crash.&nbsp;&nbsp;&nbsp; On some server
systems a two part key will do the job.&nbsp;&nbsp; Using a table with
couple rows the query may work in with Access because it builds the query
with less lines.
<BLOCKQUOTE>&nbsp;
<BR>&nbsp;</BLOCKQUOTE>
&nbsp;
</BODY>
</HTML>

--------------28FC7B415F8C27FE394B42D6
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for David Hartwig
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             David Hartwig
n:              Hartwig;David
email;internet: daveh@insightdist.com
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard


--------------28FC7B415F8C27FE394B42D6--