[AccessD] Harnessing SQL Server with runtime

Robert,

What specific things have to be done to use the pass-through query?

I took a regular query using a table linked to SQL Server and made it pass-through by changing the 
sql specific to pass through.  It immediately asked me for a dsn, and when I gave it one it asked me 
for the user name / password.  But it keeps asking me for the dsn / user name / password any time I 
do anything with that query or the form that is bound to that query.

Am I missing something?

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