RE: ODBC and Access 2000 - Mailing list pgsql-odbc

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@library.uwaterloo.ca]
> Sent:    Wednesday, May 09, 2001 3:07 PM
> To:    Peter Wilkinson; Hiroshi Inoue; Jean-Michel POURE; Keith Todd
> Cc:    pgsql-odbc@postgresql.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@postgresql.org
> > > [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Hiroshi Inoue
> > > Sent: 08 May 2001 01:19
> > > To: Chris Gray
> > > Cc: pgsql-odbc@postgresql.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
> > >
>