From: David Hartwig <daveh@insightdist.com>
To: Olaf Mittelstaedt <mstaedt@va-sigi.va.fh-ulm.de>
To: pgsql-interfaces <pgsql-interfaces@postgreSQL.org>
Subject: Can't Update from MS Access (Was - Insight ODBC driver)
Date: Fri, 17 Apr 1998 14:03:02 -0400
Message-ID: <35379956.9CF38CD0@insightdist.com> (raw)
References: <199804170615.IAA01926@gate.va.fh-ulm.de>
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.
Attachments:PSQLODBC_FAQ.htm (text/html, 2.8K) download
<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> </FONT>
<BR>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:
<BLOCKQUOTE><B><FONT COLOR="#333333">create table foo (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. 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?".
<BR>
<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.
When the key is multipart the query looks something like:
<BLOCKQUOTE><B>-- This is a 3 part key</B>
<BR><B>select ... from foo where</B>
<BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?") OR
-- line 1</B>
<BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?") OR
-- line 2</B>
<BR><B>
...</B>
<BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?") OR
-- line 27</B>
<BR><B> (v1 = "?" AND v2 = "?" AND v3 ="?");
-- 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 exponential
amounts of memory and eventually crash. On some server
systems a two part key will do the job. Using a table with
couple rows the query may work in with Access because it builds the query
with less lines.
<BLOCKQUOTE>
<BR> </BLOCKQUOTE>
</BODY>
</HTML>
vcard.vcf (text/x-vcard, 202B) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: daveh@insightdist.com, mstaedt@va-sigi.va.fh-ulm.de
Subject: Re: Can't Update from MS Access (Was - Insight ODBC driver)
In-Reply-To: <35379956.9CF38CD0@insightdist.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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