[AccessD] Feeding parameters to SQL Server

JWColby jwcolby at colbyconsulting.com
Wed Feb 21 13:55:10 CST 2007


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

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


Well... I dunno.

I have a view in SQL Server returning 8 fields, 6K records (currently),
completely unfiltered, all records in the table.  I pull that linked table
into a query in order to do some preliminary filtering - check date between
a pair of dates and check number not null.

I want the view in sql server to do the "check number not null" for me, and
I know how to do that.

I want the SQL Server to take a pair of parameters - "From Date" and "To
Date" and return only those records.

If I could do this, then I could get rid of my "base query" in access which
applies these filters, reduce traffic across the wire etc.  However even if
I do this, the view is not being requested by a form in XP (for example)
where some magical thing happens in Access to pass the parameters off to SQL
Server.  The linked view will be used immediately in another query which
joins this view to other tables (on one field - BEID - in the view) in order
to build up a set of data for a report.

So the "parent" query that is using the view will have to "feed" the
parameters to the linked view.  Being a nubee to SQL Server I do not even
know if what I am attempting to do is possible.  I cannot wrap my mind
around how a query that is using a view can know that the view needs
parameters, and if it did, what to feed it.  If it is possible, I have never
done so obviously, so I don't even have the basics of what to set in Access
to do the parameter passing.

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 Mark A Matte
Sent: Wednesday, February 21, 2007 1:50 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Feeding parameters to SQL Server

Maybe I'm not understanding the question...but you have a linked table in
access...that is really a view in SQL...would a passthru to this view work
to add your criteria??

???

Mark A. Matte


>From: "JWColby" <jwcolby at colbyconsulting.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Feeding parameters to SQL Server
>Date: Wed, 21 Feb 2007 13:36:31 -0500
>
>Eric,
>
>What is responsible for feeding in the parameters?  Can the query that 
>uses the linked view do that?
>
>
>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 Eric Barro
>Sent: Wednesday, February 21, 2007 1:05 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] Feeding parameters to SQL Server
>
>John,
>
>Convert the SQL server view to a SQL server function that returns a table.
>Functions take parameters and return data sets based on the parameters 
>that were passed to it.
>
>
>The general syntax for such a function would be...
>
>
>CREATE FUNCTION dbo.MyFunction
>(
>	@Parameter1	SQLDataType.
>	@Parameter2	SQLDataType
>)
>RETURNS TABLE
>AS
>RETURN
>(
>SELECT
>	fieldName1,
>	fieldName2
>FROM MyTable a (nolock)
>WHERE a.whereField1 BETWEEN @Parameter1 AND @Parameter2
>  )
>
>
>
>Eric
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
>Sent: Wednesday, February 21, 2007 9:59 AM
>To: 'Access Developers discussion and problem solving'
>Subject: [AccessD] Feeding parameters to SQL Server
>
>Is it possible to feed a parameter to a SQL Server view from Access?  I 
>am linking (ODBC) to a pair of tables in SQL Server.  The views return 
>a set of data which will only grow larger over time.  I would like to 
>somehow tell sql server where... and feed in the value - where 
>checkdate >=X and <=Y for example.  These views are then used in 
>queries inside of Access, linked to my tables and stuff.
>
>John W. Colby
>Colby Consulting
>www.ColbyConsulting.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.441 / Virus Database: 268.18.3/694 - Release Date: 
>2/20/2007
>1:44 PM
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Want a degree but can't afford to quit? Top school degrees online - in as
fast as 1 year
http://forms.nextag.com/goto.jsp?url=/serv/main/buyer/education.jsp?doSearch
=n&tm=y&search=education_text_links_88_h288c&s=4079&p=5116

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

**********************************************************************
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.
**********************************************************************
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list