On 27 Sep 2004 at 16:42, Merlin Moncure wrote:
> > For manipulating the data -- Access works pretty good as long as
> Access
> > can know which record(s) to update (primary keys or OIDs -- that sort
> of
> > thing).
>
>
> Looking deeper it seems all my problems are really boiling down to one
> problem: the where clause used to update the edited record back to the
> server. Both access and vb generate a where clause that includes every
> field in the table instead of the p-key. For various sundry reasons
> such as improper handling of null values, etc. this where clause gets
> busted. This problem is aggravated by the fact that all my domains get
> treated as text types, although this is correctable by using a view
> which I was planning to use anyways (aside: I make heavy use of
> domains). This busted where clause trips up vb and access in different
> ways. This may be a case where I may want to explore hacking the odbc
> driver to get it to do what I want, depending on who is building the
> clause. My guess is the ado driver is building it so I'm basically
> hosed. It's really queer to me that they use the p-key to pull the data
> out and the entire record to write the data back...what's the point of
> the p-key anyways?
>
> The odbc driver is supposed to have a property exposed to the ado
> wrapper that allows you to constrain the write-back where clause to
> different things, but apparently this does not work in pgsql-odbc.
>
> I tried the ole db driver and got nowhere, couldn't even get a basic
> connection. Hopefully I can get this corrected and work with this
> driver a little bit. I have about two weeks to figure out the best way
> to proceed. So far, I'm really unimpressed with the ado architecture,
> this being my first real experience with it (as compared to ado.net,
> which is a dream to work with).
>
The only reason the ADO driver would build an update statement using
all columns is that it can't identify a unique key (primary key). It's
possible that the ODBC driver does not correctly supply this info.
Can you do something artificial like put an additional unique index on
the primary key?
As a last resort you might try a commercial ODBC driver for postgres
,openlink I think for one, to see if this helps. At least it may give pointers
to the real problem. ( you can get a trial copy for a while I think)
Cheers,
Gary.