Обсуждение: 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?