[dba-SQLServer] Re: [AccessD] was SQL Server queries - appending strings

John W. Colby wrote:
> Thanks both of you.
> 
> I have a client that purchased SQL Server and so it's time to get off the
> dime and learn this stuff.
> 
> The client uses A2K.
> 
> Now that I am changing to SQL Server I have to get the server doing more of
> the work - the whole point is of course to speed things up.  For technical
> reasons (a whole SLEW of reasons) going to an ADP is NOT an option any time
> in the near future, if at all.

It's really about the right tool for the job, altho speculation would 
deem that an ADP would be better suited.

> I have a main tabbed form which uses a multi-table join to get live data
> from a client / claim .  The client wanted it that way so that the user
> could edit fields in either table.
> 
> Views appear to be similar to tables, i.e. I can link to them and they show
> up in the table window.  Is there a way to pass parameters to them?  "Where
> LastName like col*" etc?

No, Views do not take parameters, that's what Stored Procedures (aka 
sprocs) are for..

> ATM, I pull the whole recordset and then filter down to a single record.
> Not fast, but after the initial pull the filter proceeds at a reasonable
> rate.  I can then "filter" to a specific claim reasonably fast.  I would
> like to change this to actually ask SQL Server for a view of exactly one
> record each time they want to see a claim.  Thus avoiding the "pull the
> entire recordset across the net, now pull the index, etc.

That would be effective.. again... Sprocs, think Pass-Through Queries.. 
generally you'd just type into the SQL window (technically not the qbe 
window), EXEC stp_MyFavoriteSproc Param1, Param2

I'll take this quote from David McAfee's post over on Access-L on 
exactly this issue

/QUOTE/
I couldn't get it working that way after all. I cant remember how I used 
to do that.

but...

In one of my samples (A97 db connected to A97 BE with an ODBC link to 
SQL 2K)

I created a pass-through query called "_BrettsPassThru"

The SQL inside the query is:

         EXEC devREDe.dbo.BrettsPOQuery '1/1/2004', '1/22/2004'


So I created a command button and placed the following code behind it:

CurrentDb.QueryDefs("_BrettsPassThru").SQL = "EXEC 
devREDe.dbo.BrettsPOQuery '" & Forms![Monthlyreport]![StartDate] & "', 
'" & Forms![Monthlyreport]![EndDate] & "'" 'strSQL
DoCmd.OpenQuery "_BrettsPassThru"

Let me know how it works for you.

David
/END QUOTE/

> This must be editable so AFAIK I cannot use stored procedures which would
> allow passing parameters.  Because this is A2K AFAIK I cannot assign a
> recordset to the form's recordsource property.

you are mistaken.  you can assign the recordset to the recordsource, but 
  because the data is from sql server (in an mdb) it becomes read only.

> What are my options here?  Do I have any?

Pass Through, convert the mdb to an ADP, Stored procedures.  and Yes you 
do :)


-- 
-Francisco