[AccessD] Harnessing SQL Server with runtime

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