Re: Some problems with Access and ODBC to PostgreSQL

On Wednesday, March 17, 2004 7:02 AM, Richard Huxton 
[SMTP:dev@xxxxxxxxxxxx] 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?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)