[AccessD] combo box 101

I have gone both ways over my time in Access.  There are times when a single "state or color or 
Model query" works for a dozen combos.  OTOH having the SQL in the combo allows it to travel with 
the combo so to speak.  OTOH if you have a pass through query to SQL Server...

I guess I am saying each has its place.

John W. Colby
www.ColbyConsulting.com

On 8/9/2011 6:36 PM, Asger Blond wrote:
> 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
>