Re: [INTERFACES] weird Access problem

Louis Bertrand wrote:
>
> Hello all,
>
> I connect to a postgresql database from MS Access and I see some weird
> things in the postgresql log in C:\psqlodbc_XXX.log. I am using an Access
> form to enter data into a table, and the sql log has these insane long
> lines with repeated OR terms:
>
> conn=43911292, query='SELECT "SystemID","MailingListID","SystemType"
> FROM "Systems" WHERE "SystemID" = 'system5' OR "SystemID" = 'System34' OR
> "SystemID" = 'System34' OR "SystemID" = 'System34' OR "SystemID" =
> 'System34' OR "SystemID" = 'System34' OR "SystemID" = 'System34' OR
> "SystemID" = 'System34' OR "SystemID" = 'System34' OR "SystemID" =
> 'System34''
> [ fetched 2 rows ]
>
> Access freezes up (Task not responding, ctrl-alt-del > "End task" works)
> if I keep working after this starts happening.
>
> Using Access97 SR-1 on Win95 with iodbc 6.40.0007. The tables in Access
> are "linked" to those in pgsql via a system DSN. PostgreSQL is running on
> OpenBSD 2.6.
>
> Is this a problem with Access or with the way I'm using the connectivity?
> I'm not a guru; I'm just trying to set up a simple demo and I'd rather it
> didn't crash while I'm running it.
>

Actually, even though the above syntax seems odd, it is correct. Access
uses the JET database engine, which creates keyset queries like this.
It fetches 10 rows at a time (if you notice there are 10 terms in that
query.) The repeats are there because the query always has the same
number of terms, and if there are less than 10 rows in this particular
keyset, it repeats them.

It shouldn't crash however. Do you have a *unique* index on the term in
the keyset (SystemID)?

The other thing to try is changing the driver option "use
declare/fetch". Sometimes having this option enabled can cause things
to lock up.

Byron