Обсуждение: Some problems with Access and ODBC to PostgreSQL

Hi all,

I'm very familliar with the use of Access as a front-end to MS-SQL Server,
but I'm having some strange issues with PostgreSQL that are getting me
frustrated.

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.  Access will let me keep using the form and close
it, but then it crashes when I try to close Access.  Obviously, there's
something about what the ODBC driver is doing or not doing that Access is
not handling well.

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.

Any ideas?

Thanks,

- Steve Jorgensen

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.

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

--
  Richard Huxton
  Archonet Ltd

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?