Re: Some problems with Access and ODBC to PostgreSQL

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