Thread: ODBC and Access 2000

ODBC and Access 2000

From

Chris Gray

Date:

07 May 2001, 17:48:46

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?

Thanks,
Chris

Re: ODBC and Access 2000

From

Hiroshi Inoue

Date:

07 May 2001, 23:59:41

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

RE: ODBC and Access 2000

From

"Peter Wilkinson"

Date:

08 May 2001, 05:13:18

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

RE: ODBC and Access 2000

From

Chris Gray

Date:

08 May 2001, 13:42:48

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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

RE: ODBC and Access 2000

From

"Keith Todd"

Date:

09 May 2001, 03:33:32

I am getting the same problem.

Keith Todd

-----Original Message-----
From: Chris Gray [mailto:cpgray@library.uwaterloo.ca]
Sent: Tuesday, May 08, 2001 7:17 AM
To: Peter Wilkinson; Hiroshi Inoue; Jean-Michel POURE
Cc: pgsql-odbc@postgresql.org
Subject: RE: ODBC and Access 2000


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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: ODBC and Access 2000

From

Hiroshi Inoue

Date:

09 May 2001, 07:21:17

Keith Todd wrote:
>
> I am getting the same problem.
>

Does your table have float fields ?

regards,
Hiroshi Inoue

Re: Re: ODBC and Access 2000

From

Chris Gray

Date:

09 May 2001, 12:20:30

In the table I'm having trouble with all fields are varchar except for the
primary key which was defined as a serial type.

Chris

On Wed, 9 May 2001, Hiroshi Inoue wrote:

> Keith Todd wrote:
> >
> > I am getting the same problem.
> >
>
> Does your table have float fields ?
>
> regards,
> Hiroshi Inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

RE: ODBC and Access 2000

From

Chris Gray

Date:

09 May 2001, 13:36:45

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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

RE: ODBC and Access 2000

From

"Peter Wilkinson"

Date:

09 May 2001, 14:10:48

I did not see these in the earlier messages, but I may have missed so
forgive me and ignore this message if so...

Do you have "Recognise Unique Indexes" checked in the ODBC driver?
Do you have the MS Access key icon next to the primary key in the design
view of the table? You can look at the design view even though Access gives
message saying you can't change it (resonably enough)
When you link the table do you get a pop-up asking you to nominate a unique
column? If you do it means that Access has not found the key.

Also, by the way, if you make any changes to the table design on the
backend, you need to delete the table link in Access and relink it.  I don't
thing the link-refeshing add-in refeshes well enough. Not doing this can
give rise to confusing problem-solving sessions.


----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Chris Gray
Sent: 09 May 2001 15:07
To: Peter Wilkinson; Hiroshi Inoue; Jean-Michel POURE; Keith Todd
Cc: pgsql-odbc@postgresql.org
Subject: RE: [ODBC] 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: ODBC and Access 2000

From

Tom Lane

Date:

09 May 2001, 14:37:44

Chris Gray <cpgray@library.uwaterloo.ca> writes:
> Still no luck.  I tried making all fields non null and supplying a default
> value and still get the same error message.

I'm not an Access user, but I have a vague recollection that it has
problems with updating rows in tables that don't have a PRIMARY KEY
declared.  There is more about this in our mailing list archives.

            regards, tom lane

RE: ODBC and Access 2000

From

"Keith Todd"

Date:

10 May 2001, 00:19:00

I have a primary key which is int and the rest varchar

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, May 09, 2001 8:18 AM
To: Chris Gray
Cc: Peter Wilkinson; Hiroshi Inoue; Jean-Michel POURE; Keith Todd;
pgsql-odbc@postgresql.org
Subject: Re: [ODBC] ODBC and Access 2000


Chris Gray <cpgray@library.uwaterloo.ca> writes:
> Still no luck.  I tried making all fields non null and supplying a default
> value and still get the same error message.

I'm not an Access user, but I have a vague recollection that it has
problems with updating rows in tables that don't have a PRIMARY KEY
declared.  There is more about this in our mailing list archives.

            regards, tom lane

RE: ODBC and Access 2000

From

"Henshall, Stuart - WCP"

Date:

10 May 2001, 08:02:21

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
> > >
>

Re: RE: ODBC and Access 2000

From

Chris Gray

Date:

10 May 2001, 13:28:03

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@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
> > > >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Re: RE: ODBC and Access 2000

From

Tom Lane

Date:

10 May 2001, 14:33:44

Chris Gray <cpgray@library.uwaterloo.ca> writes:
> Would anyone care explain just what that function and operator are doing?

Apparently they're meant to allow constructs like "WHERE xmin = 1234"
to be accepted.  What I'd like to know is why Access wants to do that...

            regards, tom lane

RE: RE: ODBC and Access 2000

From

"Henshall, Stuart - WCP"

Date:

10 May 2001, 16:59:32

Hello,
    I had assumed that Access was just treating it like another field
and going for over kill in identifying the record. However this doesn't seem
to be the case as in a quick test it doesn't appear to use any other fields,
not even non null, unique indexed ones. Just the primary key and xmin.
    Is xmin a standard for RowVersioning? Does SQL Server use it
perhaps, and allows you to reference it in such a way. (Could this be
related to the problems people had updating using ADO in Access, but they
could outside and also to SQL Server? Never used ADO myself and can't
remember the exact situation that was discussed).
    I wonder what other software out there would like to treat xmin as
an int4? Is it an Access thing, an MS thing, or is it more wide spread?
Hmm, better stop rambling now :)
- Stuart

> -----Original Message-----
> From:    Tom Lane [SMTP:tgl@sss.pgh.pa.us]
> Sent:    Thursday, May 10, 2001 4:13 PM
> To:    Chris Gray
> Cc:    Henshall, Stuart - WCP; pgsql-odbc@postgresql.org
> Subject:    Re: [ODBC] RE: ODBC and Access 2000
>
> Chris Gray <cpgray@library.uwaterloo.ca> writes:
> > Would anyone care explain just what that function and operator are
> doing?
>
> Apparently they're meant to allow constructs like "WHERE xmin = 1234"
> to be accepted.  What I'd like to know is why Access wants to do that...
>
>             regards, tom lane

Re: RE: ODBC and Access 2000

From

Tom Lane

Date:

10 May 2001, 20:36:22

"Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk> writes:
>     Is xmin a standard for RowVersioning?

Not that I know of --- I had always thought it was a Postgres-ism,
which is why I'm surprised that Access depends on it.

            regards, tom lane

Re: RE: ODBC and Access 2000

From

Hiroshi Inoue

Date:

10 May 2001, 22:19:19

Tom Lane wrote:
>
> "Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk> writes:
> >       Is xmin a standard for RowVersioning?
>
> Not that I know of --- I had always thought it was a Postgres-ism,
> which is why I'm surprised that Access depends on it.
>

???? Of cource Access could know that xmin is a field for
row versioning by sending inquiries to psqlodbc driver.

regards,
Hiroshi Inoue

RE: RE: ODBC and Access 2000

From

"Peter Wilkinson"

Date:

11 May 2001, 08:19:25

What is row versioning? I do not expect a full tutorial in this forum but
would appreciate a quick sketch and pointers as to where to look further.

Regards
Peter Wilkinson


-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Tom Lane
Sent: 10 May 2001 21:59
To: Henshall, Stuart - WCP
Cc: Chris Gray; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] RE: ODBC and Access 2000


"Henshall, Stuart - WCP" <SHenshall@westcountrypublications.co.uk> writes:
>     Is xmin a standard for RowVersioning?

Not that I know of --- I had always thought it was a Postgres-ism,
which is why I'm surprised that Access depends on it.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: RE: RE: ODBC and Access 2000

From

"kapil c. tilwani"

Date:

11 May 2001, 17:27:54

Hi,

My name is Kapil.

I am working on a small project in VB and currently using MS-Access as my db.  I wanted to upgrade to Postgres as
Accesswas getting slower and offered no triggers that were required.  However, I learnt that PG works only on Linux.
However,I have also learnt that it does work on NT after Cygwin et al...  I believe you are using VB and PG ...  If
possible,could u just tell me as to how has that been achieved...   

If possible, could u kindly be a bit descriptive as that could help...  both in terms of installation of Postgres and
usingPostgres in VB. 

Waiting for ur early reply,
Thanx in advance,
Kapil

PS : Found ur mail address thru PG community mail...

_____________________________________________________
Chat with your friends as soon as they come online. Get Rediff Bol at
http://bol.rediff.com

RE: RE: RE: ODBC and Access 2000

From

"Henshall, Stuart - WCP"

Date:

14 May 2001, 19:46:13

Hello,
    To run Cygwin on NT just go to www.cygwin.com and follow the
instructions. The latest version of cygwin includes postgresql 7.1.1 (I
don't know wether this has been patched to for the plpgsql bug). You could
also compile it from source. See the FAQ's, and for more on using postgresql
with cygwin look to pgsql-cygwin (I'll just say 98 will do for development,
but don't try to run it for production, it's far to unstable and I've never
got it to pass regression tests, NT seems far better, but I don't think its
as speedy as Linux for this (no facts, just impressions), and I have nio
idea for othe unices (unixes?)).
    The easiest way to get postrgesql to work with Access is to use
linked tables. To do this first put the ODBC driver on (
ftp://ftp-stud.fht-esslingen.de/pub/Mirrors/ftp.postgresql.org/pub/odbc/vers
ions/ and go to either full, msi, or dll to get 7.01.0005 (full is probably
the one you'll need)). See my post a short while ago (or any number of
others) for info on my settings. Then just in access select new table, link,
and then select the ODBC settings (you'll need to have setup a DSN, have a
look at http://techdocs.postgresql.org (definitely have a look here LOTS of
good info) for more info or the FAQs). I tend to not use DSN's and
CreateTable with all the settings in a string to dynamically create tables
(which is why I gave a connection string like I did).
    There's quite a bit of info in various FAQ's and previous mailing
list discussions on this type of issue (and the various problems),
particulary look at the ODBC settings (e.g. you'll need to turn of the read
only both the driver and the datasource options to get a writable version).
Hope this gives you somewhere to start at,
- Stuart

> -----Original Message-----
> From:    kapil c. tilwani [SMTP:tilwanis@rediffmail.com]
> Sent:    Friday, May 11, 2001 7:00 PM
> To:    peterw@innate.co.uk; pgsql-odbc@postgresql.org
> Subject:    Re: RE: RE: ODBC and Access 2000
>
>
> Hi,
>
> My name is Kapil.
>
> I am working on a small project in VB and currently using MS-Access as my
> db.  I wanted to upgrade to Postgres as Access was getting slower and
> offered no triggers that were required.  However, I learnt that PG works
> only on Linux.  However, I have also learnt that it does work on NT after
> Cygwin et al...  I believe you are using VB and PG ...  If possible, could
> u just tell me as to how has that been achieved...
>
> If possible, could u kindly be a bit descriptive as that could help...
> both in terms of installation of Postgres and using Postgres in VB.
>
> Waiting for ur early reply,
> Thanx in advance,
> Kapil
>
> PS : Found ur mail address thru PG community mail...
>
> _____________________________________________________
> Chat with your friends as soon as they come online. Get Rediff Bol at
> http://bol.rediff.com
>
>
>
>

AW: RE: RE: RE: ODBC and Access 2000

From

"Joerg Last"

Date:

15 May 2001, 05:39:30

Hello,
I don't know if somebody else told you,
but there is a site
http://odbc.postgres.org
with many hints, the newest Postgres-ODBC-Driver for Win
and a FAQ about ODBC and especially Access.

- Joerg.

Re: AW: RE: RE: RE: ODBC and Access 2000

From

Cedar Cox

Date:

21 May 2001, 10:05:18

Nope.  Thanks for the info.

Hey everyone, this should be advertised a bit more... ;)  BTW, is someone
maintaining this page?

On Tue, 15 May 2001, Joerg Last wrote:

> Hello,
> I don't know if somebody else told you,
> but there is a site
> http://odbc.postgres.org
> with many hints, the newest Postgres-ODBC-Driver for Win
> and a FAQ about ODBC and especially Access.
>
> - Joerg.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: AW: RE: RE: RE: ODBC and Access 2000

From

DI Hasenöhrl

Date:

21 May 2001, 13:17:57

Hi Cedar,

thank you for your odbcRowSource function. In the meantime I had to work with some "normal" Access databases, but now I'm back at postgresql with Access and I will try to implement your function in my code.

Cedar, are you sure, that

http://odbc.postgres.org

is the right adress, because I get the following message:

"the requested URL could not be retrieved ....."

Unable to determine IP address from host name for odbc.postgres.org

The dnsserver returned:

DNS Domain 'odbc.postgres.org' is invalid: Host not found (authoritative).

Thanks,

Irina

----- Original Message -----
From: Cedar Cox
To: Joerg Last
Cc: pgsql-odbc@postgresql.org
Sent: Monday, May 21, 2001 1:05 PM
Subject: Re: AW: [ODBC] RE: RE: RE: ODBC and Access 2000

Nope. Thanks for the info.

Hey everyone, this should be advertised a bit more... ;) BTW, is someone
maintaining this page?

On Tue, 15 May 2001, Joerg Last wrote:

> Hello,
> I don't know if somebody else told you,
> but there is a site
> http://odbc.postgres.org
> with many hints, the newest Postgres-ODBC-Driver for Win
> and a FAQ about ODBC and especially Access.
>
> - Joerg.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: AW: RE: RE: RE: ODBC and Access 2000

From

Cedar Cox

Date:

21 May 2001, 13:57:17

No.  The original post was wrong.  I caught it but forgot to correct it.
It should be
 http://odbc.postgresql.org
                  ---^^

-Cedar

On Mon, 21 May 2001, DI Hasenöhrl wrote:

> Hi Cedar,
>
> thank you for your odbcRowSource function. In the meantime I had to work with some "normal" Access databases, but now
I'mback at postgresql with Access and I will try to implement your function in my code. 
>
> Cedar, are you sure, that
> http://odbc.postgres.org
> is the right adress, because I get the following message:
>
> "the requested URL could not be retrieved ....."
>   Unable to determine IP address from host name for odbc.postgres.org
> The dnsserver returned:
>
>   DNS Domain 'odbc.postgres.org' is invalid: Host not found (authoritative).

Re: AW: RE: RE: RE: ODBC and Access 2000

From

Vince Vielhaber

Date:

22 May 2001, 15:26:40

On Mon, 21 May 2001, Cedar Cox wrote:

>
> Nope.  Thanks for the info.
>
> Hey everyone, this should be advertised a bit more... ;)  BTW, is someone
> maintaining this page?

I am, sorta.  I just finished putting it together and am getting things
set up so that tips and stuff can be easily added.  With the upcoming
holidays, and a bunch of other stuff the changes will be sporadic.  For
those who don't know me, I'm the PostgreSQL webmaster.

Vince.


>
> On Tue, 15 May 2001, Joerg Last wrote:
>
> > Hello,
> > I don't know if somebody else told you,
> > but there is a site
> > http://odbc.postgres.org
> > with many hints, the newest Postgres-ODBC-Driver for Win
> > and a FAQ about ODBC and especially Access.
> >
> > - Joerg.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================