[AccessD] Feeding parameters to SQL Server

Yes.

I will sometimes send a SQL SELECT string or INSERT statements using a
pass-thru, but when I can, I call a stored procedure like this because MS
SQL has already analyzed the stored procedure and knows the most optimal way
to run it.

The only gotcha is a pass-thru is read-only but for reporting it's nice.

Rusty

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com]
Sent: Wednesday, February 21, 2007 1:55 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Feeding parameters to SQL Server


Wow.  So you are saying that  

ap_GetMyInfo 6

Is valid SQL as long as the object at the other end of the connection string
knows what to do with it?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
rusty.hammond at cpiqpc.com
Sent: Wednesday, February 21, 2007 2:36 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Feeding parameters to SQL Server

John,

As long as you don't need to update the data in the view coming from SQL,
you can setup a stored procedure with parameters in SQL, then use a
pass-through query in your Access front end to call the stored procedure and
pass it the parameters.

For example, say you have a table in SQL called tblMyInfo with fields:
MyInfoID, SomeData1, SomeData2

Create a new stored procedure with parameters as in the following example:
CREATE PROCEDURE [ap_GetMyInfo] @GetMyInfoID integer
	 AS SELECT MyInfoID, SomeData1, SomeData2
		FROM tblMyInfo
		WHERE MyInfoID = @GetMyInfoID
GO

Then in your pass through query in Access, setup your connection string to
your SQL database(in the properties of the query) and call the stored
procedure in the SQL ie:

ap_GetMyInfo 6


When you run the pass-through query, you get a list of records that have a
MyInfoID value of 6

HTH

Rusty


**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************