Re: Some problems with Access and ODBC to PostgreSQL

On Wednesday, March 17, 2004 7:02 AM, Richard Huxton
[SMTP:dev@archonet.com] wrote:
> On Tuesday 16 March 2004 01:18, Steve Jorgensen wrote:
> >
> > First, Access is bugging out.  I have one form on which, depending
which
> > variation of the code I run, either rewrites the RecordSource property
of
> > my form and subform or requeries them to reflect changes in form
controls
> > the queries refer to.  In either case, this will work the first few
times,
> > then it will continue to work, but I suddenly get 20 instances of the
form
> > showing in the task bar.
>
> Never seen anything like this, but it sounds like changing the
RecordSource
> without closing the form might be creating a new form.

Well, in 10 years of Access programming, I've never seen anything like it
either.  Also, I'm not getting multiple instances of the form, and they're
not stacking up as I requery.  Everything's fine for the first several
requeries, then - boom.  I still have one form, but there are 20 icons for
it on the Windows task bar.  I think it's probably some kind of memory
corruption or something.

>
> > Second, I have some queries that run very fast when I create them
> > dynamically in code, but run dog slow when I make them parameterized
using
> > DAO.  I'm talking simple parameters here, just
<field>=Forms!<formname>!
> > <controlname>.  With an MS SQL Server back-end, I get faster
performance
> > this way because it uses a prepared statement and the server doesn't
need
> > to recompile the query.  here, it seems as if Access might be failing
to
> > even figure out it should have the server process the WHERE clause,
though
> > I can't see why that should be happening.
>
> I think it's sending the raw query and filtering client-side. You can
check
> this by turning query logging on in PostgreSQL (see the postgresql.conf
file)
> or in your ODBC driver settings.
>
> Must admit, I tend to just assemble my own SQL nowadays - the only pain
is
> Access' poor string handling.

But that's opposite of Access best practices.  I like to keep my
application mostly back-end agnostic, so having to to everything
differently because this one thing works wrong only with a PostgreSQL
back-end is not my first choice.

I'm wondering if this could be my problem - I saw an article that implied
that Access won't simply use the settings in the DSN for the PostgreSQL
connection settings, they must all be present in the connection string.  Is
that right?  If so, I'm doing it wrong because I'm programmatically
relinking my tables using only the DNS name parameter.  Could this be the
issue?