[AccessD] Harnessing SQL Server with runtime

Great idea Robert!

John W. Colby
www.ColbyConsulting.com

On 3/17/2011 11:19 AM, Robert wrote:
> John,
>
> You can modify querydefs in a runtime environment. I am not sure where you got the idea you could
> not do that.
> You cannot modify forms, reports, macros, or modules. Tables and queries can be modified. I do both
> all the time
> through code.
>
> I use and teach a system of using 2 queries (_0 and _1).
> The _0 query holds the base SQL without a where clause. In the case of a passthrough query, without
> the parameters.
> In code, I grab the SQL from the _0, add any Where clause or parameters, then replace the SQL in the
> _1 query.
> The comboboxes, reports, forms, etc. are always based on the _1 query.
>
> By doing this, the code behind only needs to know the Where clause or parameters, it does not need
> to know any of the
> SQL behind the queries. So, the queries can change and not affect the code.
>
> I just finished a rather massive implementation of SQL 2008 R2 and Access 2010 with this methodology.
>
> Robert
>
>
> At 07:50 AM 3/17/2011, you wrote:
>> Date: Wed, 16 Mar 2011 06:49:29 -0400
>> From: jwcolby <jwcolby at colbyconsulting.com>
>> To: Access Developers discussion and problem solving
>> <accessd at databaseadvisors.com>
>> Subject: [AccessD] Harnessing SQL Server with runtime
>> Message-ID: <4D8095B9.4020103 at colbyconsulting.com>
>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>>
>> http://www.tek-tips.com/viewthread.cfm?qid=1296308&page=269
>>
>> Using runtime means that you cannot do the typical "open the qdf, modify and use" because you can't
>> modify objects in the runtime environment. For that reason I am going to have to become proficient
>> in stored procedures and passing parameters to them. I found the thread above which discusses this
>> for the form itself. Can the same kind of thing be done for the combos and reports.
>>
>> This project is has already taught me a ton of things that I never had to use before. Working with
>> parameterized stored procedures from Access is another such area that I have always wanted to learn.
>> Any tips and tricks are always appreciated.
>>
>> --
>> John W. Colby
>> www.ColbyConsulting.com