Thread: Help : serious problems with ODBC & Postgresql 7.1 RC2 & Access 2000

Hello all,

I have serious problems updating an attached table in access 2000.
The problem is similar to
http://postgresql.rmplc.co.uk/mhonarc/pgsql-odbc/2001-03/msg00020.html
My configuration is Access 2000 SP1 / Windows NT SP5 / MDAC 2.6 /
PostgreSQL 7.1 RC2 /  7.104 odbc driver

1) Configuration
Basically, my tables are built with SERIAL indexes and timestamp values as
described in http://www.scw.org/pgaccess/faq.html.
ODBC configuration is : Disable generic Optimizer=Yes , Recognize unique
indexes = Yes, KSQO = Yes, Logging = Yes. Everything else = No.
The database has 70 tables. Only one table will not allow updating. All
queries run within forms.
The problematic table has 5000 rows, 50 fields. No trigger is running
behind this table.

2) Problem description

a) When running the query "Select * FROM tblProblematicTable;" inside a
form, the record cannot be updated.
On the contrary, if running "Select field1, field2, field3, etc .. FROM
tblProblematicTable;", the record can be updated.
It seems that the maximum number of fields is 8 to 10, no more.

b) When deleting 99% of records, updating works again, no matter the number
of fields.

c) Logs on the client side show nothing.
Shall I allow logging server-side ?

d) The other tables seem to work well as they have less fields and records

e) Passthrough queries work perfectly.
Good, but a lot of rewriting is needed client-side.
What to you recommend : migrate to Passthrough queries or wait until the
odbc driver is fixed ?

Any idea ? Well, it seems I need help from my friends ...

Greetings from Jean-Michel POURE, Paris

Jean-Michel POURE wrote:
>
> Hello all,
>
> I have serious problems updating an attached table in access 2000.
> The problem is similar to
> http://postgresql.rmplc.co.uk/mhonarc/pgsql-odbc/2001-03/msg00020.html
> My configuration is Access 2000 SP1 / Windows NT SP5 / MDAC 2.6 /
> PostgreSQL 7.1 RC2 /  7.104 odbc driver
>
> 2) Problem description
>
> a) When running the query "Select * FROM tblProblematicTable;" inside a
> form, the record cannot be updated.
> On the contrary, if running "Select field1, field2, field3, etc .. FROM
> tblProblematicTable;", the record can be updated.
> It seems that the maximum number of fields is 8 to 10, no more.
>
> b) When deleting 99% of records, updating works again, no matter the number
> of fields.
>
> c) Logs on the client side show nothing.
> Shall I allow logging server-side ?
>

Yes. Try running the postmaster with -d2 (and without -S)
and see what gets logged in its stderr output.

regards,
Hiroshi Inoue

Hello all,

Thank you for your quick answers and help.

Surprise : after a discussion with Bill Gates, CIA's most rewarded agent, I
updated to Jet 4 SP3 which solved all problems.
It seems like MDAC 2.6 and Jet4 SP3 should always be used in conjunction.

Furthermore, I also converted my application to pass-through queries.
Cedar : thanks for your code, I am waiting for it. I will also post mine
when fully tested.

Greetings from Jean-Michel POURE, Paris

At 08:51 09/04/01 +0900, you wrote:
>Jean-Michel POURE wrote:
> >
> > Hello all,
> >
> > I have serious problems updating an attached table in access 2000.
> > The problem is similar to
> > http://postgresql.rmplc.co.uk/mhonarc/pgsql-odbc/2001-03/msg00020.html
> > My configuration is Access 2000 SP1 / Windows NT SP5 / MDAC 2.6 /
> > PostgreSQL 7.1 RC2 /  7.104 odbc driver
> >
> > 2) Problem description
> >
> > a) When running the query "Select * FROM tblProblematicTable;" inside a
> > form, the record cannot be updated.
> > On the contrary, if running "Select field1, field2, field3, etc .. FROM
> > tblProblematicTable;", the record can be updated.
> > It seems that the maximum number of fields is 8 to 10, no more.
> >
> > b) When deleting 99% of records, updating works again, no matter the number
> > of fields.
> >
> > c) Logs on the client side show nothing.
> > Shall I allow logging server-side ?
> >
>
>Yes. Try running the postmaster with -d2 (and without -S)
>and see what gets logged in its stderr output.
>
>regards,
>Hiroshi Inoue