[AccessD] Harnessing SQL Server with runtime

It's slowly starting to work...

John W. Colby
www.ColbyConsulting.com

On 3/18/2011 12:00 PM, Tony Septav wrote:
> Hey John
> Correct as to what I found out/read so far. Combos, list boxes, reports and read only forms.
>
> jwcolby wrote:
>
>> Robert,
>>
>> From my reading, pass through queries are always returned in a snapshot which makes them
>> non-updateable? These would work great for combos and such but not for bound editable forms correct?
>>
>> 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
>>>
>