Re: RE: ODBC and Access 2000

Hurrah! That got it. Matching your ODBC settings changed the error
message to one about a bad operator. I created the function and operator
in the backend and now it's working fine.

Would anyone care explain just what that function and operator are doing?

Thanks again,
Chris

On Thu, 10 May 2001, Henshall, Stuart - WCP wrote:

> Hello,
> I know Access 2000 can link OK - I'm doing it. I did have a similar
> problem a little time ago but can't remember what I did to solve it, so I'll
> quickly run down my settings:
> First things first the connection string:
> csp = "ODBC;DRIVER={PostgreSQL};UID=" & unm & ";PWD=" & pd & ";DATABASE=" &
> DBName & ";SERVER=" & DBSrvr &
> ";PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIO
> NING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS="
> unm is the user name, pd is the password, DBName is the database name,
> DBSrvr is the database server.
> Other ODBC Driver options:
> Unkniown Sizes=Maximum,KSQO, Recognize Unique Indexes,Text as LongVarChar
> on, all othes false
> cache Size=100, Max Varchar=254, Max LongVarChar=16382
>
> Since I use row versioning I have the following in the back end (I got this
> from somewhere, sometime, can't remeber who or when sorry):
> create function int4eq(xid,int4)
> returns bool
> as ''
> language 'internal';
>
> create operator = (
> leftarg=xid,
> rightarg=int4,
> procedure=int4eq,
> commutator='=',
> negator='<>',
> restrict=eqsel,
> join=eqjoinsel
> );
>
> Also when you insert a record on the datasheet view of a table
> Access will first try to read the record back using the primary key,which
> for serial types is no longer NULL (assuming no value was set), and if it
> can't it will attempt to read back using all the none null fields. This can
> lead to it picking up the wrong record if the rest of the data entered is
> none unique (view postmaster output with -d2 to see what I mean).
>
> Oh yea, as a quick FYI the bools as char is unchecked as I don't use
> PG bools but int2's instead (as I knew I'd be using M$ Access as a front
> end, which also leads to the interesting <>false is not necessary =true, so
> instead of testing for true I'm always testing for <>false).
> Hope something in this lot is helpfull
> - Stuart
>
> > -----Original Message-----
> > From: Chris Gray [SMTP:cpgray(at)library(dot)uwaterloo(dot)ca]
> > Sent: Wednesday, May 09, 2001 3:07 PM
> > To: Peter Wilkinson; Hiroshi Inoue; Jean-Michel POURE; Keith Todd
> > Cc: pgsql-odbc(at)postgresql(dot)org
> > Subject: RE: ODBC and Access 2000
> >
> > Still no luck. I tried making all fields non null and supplying a default
> > value and still get the same error message.
> >
> > Chris
> >
> >
> > On Tue, 8 May 2001, Chris Gray wrote:
> >
> > > Thanks to everyone who replied.
> > >
> > > To answer Hiroshi Inoue:
> > > I am running the 7.01.00.05 version of pgsqlodbc which I downloaded just
> > > last week.
> > >
> > > To answer Peter Wilkinson:
> > > The table that I can't update via Access has no boolean fields in it.
> > But
> > > I'll try ensuring that all fields have a default.
> > >
> > > To answer Jean-Michel POURE: I have MDAC 2.6 which I downloaded last
> > week
> > > when installing pgAdmin. The Jet upgrade you pointed me to was for
> > > Jet 4.0 SP 3. I ran this upgrade but it doesn't seem to change the
> > > behavior. And thanks for the FAQ tip.
> > >
> > > Chris
> > >
> > > On Tue, 8 May 2001, Peter Wilkinson wrote:
> > >
> > > > We had this problem and tracked it down to the differences between
> > Access's
> > > > and Posgres's handling of uninitialised booleans. The solution was to
> > add
> > > > "set default false" on any booleans. Access does not set a default on
> > > > booleans in new rows it creates, but expects them to be not-null.
> > > >
> > > > By the way, "write conflict" seems to be Access's message of last
> > resort:
> > > > read it as meaning "something has gone wrong and I don't know what".
> > > >
> > > > Peter Wilkinson
> > > > Innate Management Systems Ltd
> > > >
> > > > -----Original Message-----
> > > > From: pgsql-odbc-owner(at)postgresql(dot)org
> > > > [mailto:pgsql-odbc-owner(at)postgresql(dot)org]On Behalf Of Hiroshi Inoue
> > > > Sent: 08 May 2001 01:19
> > > > To: Chris Gray
> > > > Cc: pgsql-odbc(at)postgresql(dot)org
> > > > Subject: Re: [ODBC] ODBC and Access 2000
> > > >
> > > >
> > > > Chris Gray wrote:
> > > > >
> > > > > I'm running PostgreSQL on a Debian GNU/Linux box and have installed
> > > > > pgAdmin successfully on a Windows 98 machine. pgAdmin works fine
> > for
> > > > > updating tables. I can use Access 2000 to insert a new row in a
> > table.
> > > > > If I create an Access SQL query to update or delete a row, it works.
> > > > >
> > > > > If I try to delete or update a row using the data sheet view of a
> > linked
> > > > > table I get a "write conflict" error or a message from the Jet db
> > engine
> > > > > that another user is trying to modify the same data even though I'm
> > the
> > > > > only one connected to the database.
> > > > >
> > > > > Any idea what's going on and how to stop it?
> > > > >
> > > >
> > > > Which version of psqlodbc driver are you using ?
> > > > If it's older than 7.01.0003 please try the latest one.
> > > >
> > > > regards,
> > > > Hiroshi Inoue
> > > >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>