[AccessD] combo box 101

My personal approach is to used sql in the rowsource. I feel it is safer
than using saved queries in that 6 months later when I come back to the
project I don't screw it up by modifying the query for something else and
inadvertently break a form. I have never found a problem with this approach,
and it does make it easier to repurpose a form on the fly by injecting a
different rowsource from vba if required. I do the same with combo and list
boxes for the same reasons. I have a few legacy projects with a hundred or
more saved queries. A real mess, especially with poor naming. 

Doug

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
Sent: Tuesday, August 09, 2011 3:37 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] combo box 101

I'm definitely with Stuart.
Why is an embedded SQL more "scattered" than an externally saved query?
Having externally saved queries for each an every form/combo/list/report
really seems "scattering" to me.
As Jim pointed out an embedded SQL is compiled exactely like a saved query
(behind the scenes Access creates a compiled querydef for the embedded SQL).
So you have no performance reason for creating an external saved query.
I agree with Jim that it would be nice if Microsoft had made it possible for
us to choose whether or not the embedded query should be compiled. In
scenarios with volatile data the execution plan selected by Access when
compiling the SQL may not be optimal. In good old days the embedded query
was always recompiled on the fly when opening the form etc., which made the
query plan up to date but also made the opening slower. Now, no matter if
you are using an externally saved query or an embedded SQL you have to force
a refresh of the query plan yourself by editing the query (making a change,
i.e. a new sort, the save the query, then remove the change, then save
again).
It's lamenting that we don't have the choise between a compiled and a not
compiled query any more. But that being so I prefer the embedded SQL -
exactely because it is not so "scattering"...
Asger
----- Original meddelelse -----

> Fra: Mark Simms <marksimms at verizon.net>
> Til: 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com>
> Dato: Tir, 09. aug 2011 21:37
> Emne: Re: [AccessD] combo box 101
> 
> I'm definitely not with you on that.
> I don't know what's worse: Scattered SQL Strings Or Database 
> Connection Strings.
> 
> > I think it is a matter of taste. I prefer SQL to Querydefs as the 
> > source of table based comboboxes - I find it easier to maintain.
> >
> > --
> > Stuart
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com