[AccessD] Harnessing SQL Server with runtime

Hey John
In farting around learning SQl Server. At the moment very simply I use
For a pass-through query - QryMatrixGrp

Dim qdfPassMatrix as QueryDef

On form open
Set qdfPassMatrix=db.QueryDefs("QryMatrixGrp")
qdfPassMatrix.Connect = "Your Connect String"

Hope this helps

jwcolby wrote:

> 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
>>