SQL Linked Tables: Pass Parameters to Stored Procedure & Retrieve Value

tkepongo · #1 (**permalink**) 07-26-11, 13:52

Can somebody please provide me some guidance on how to pass parameters to a SQL Server back-end stored procedure and then retrieve the results? I am using DSN linked tables to connect to the SQL Server.

My goal is to concatenate rows from SQL Server into a string and then passing it into a textbox. Below is what the stored procedure looks like. Thank you for your time!

Code:

DECLARE @ClauseList varchar(1000)
DECLARE @ValueFromForm
SELECT  @ClauseList = COALESCE(@ClauseList + ', ', '') + 
CAST(Clause AS varchar(1000))
FROM dbo.tbl_GroupClauses
Where Group_Name = @ValueFromForm
SELECT @ClauseList

Sinndho · #2 (**permalink**) 07-26-11, 15:40

Here's an example:

Code:

Function Call_SP()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strProcedureName As String
    Dim strQueryName As String
    Dim strConnection As String
    
    strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;TRUSTED_CONNECTION=Yes;"
    strProcedureName = "Some_StoredProcedure"
    strQueryName = "Qry_" & ProcedureName
    Set dbs = CurrentDb
    If DCount("*", "MSysObjects", "[Name]='" & strQueryName & "'") > 0 Then dbs.QueryDefs.Delete strQueryName
    Set qdf = dbs.CreateQueryDef(strQueryName)
    qdf.Connect = strConnection
    '
    ' Used for calling an existing procedure that takes
    ' 2 parameters: @param1 is numeric while @param2 is text.
    '
    qdf.SQL = strProcedureName & " @param1=<Value1>, @param2='<Value2>'"
    '
    ' For passing a SQL statement directly, use:
    ' qdf.SQL = "SELECT ... FROM... WHERE... ORDER BY..." , etc.
    '
    Set rst = qdf.OpenRecordset
    '
    ' Use the values returned into the recordset.
    '
    rst.Close
    qdf.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing

End Function

tkepongo · #3 (**permalink**) 07-27-11, 12:50

Quote:

Originally Posted by Sinndho

Code:

Function Call_SP()

    strQueryName = "Qry_" & ProcedureName
    Set dbs = CurrentDb
    If DCount("*", "MSysObjects", "[Name]='" & strQueryName & "'") > 0 Then dbs.QueryDefs.Delete strQueryName
    Set qdf = dbs.CreateQueryDef(strQueryName)
    qdf.Connect = strConnection
    '

Can you tell me what the strQueryName is supposed to be? I have a stored procedure name but I don't understand what the QueryName is.

Quote:

Originally Posted by Sinndho

Code:

    ' Used for calling an existing procedure that takes
    ' 2 parameters: @param1 is numeric while @param2 is text.
    '
    qdf.SQL = strProcedureName & " @param1=<Value1>, @param2='<Value2>'"

How would I pass a variable into the @param1 and @param2?

Thanks!!

healdem · #4 (**permalink**) 07-27-11, 13:11

Sinndho uses a variable strQueryName to build his queries
he prefixes his queries with qry_

say the values you wish to pass are 44596 and astring

qdf.SQL = strProcedureName & " @param1=44596, @param2='astring'"

Sinndho · #5 (**permalink**) 07-27-11, 15:03

That's it, thanks healdem!

tkepongo · #6 (**permalink**) 07-28-11, 17:21

Forgive me for my ignorance but I am still very confused. If I already have a Stored Procedure in SQL Server, why would I need to create a query?

Also with your code example below, what if 44596 and astring comes from a form control? How would I pass that into the SQL code?

Code:

qdf.SQL = strProcedureName & " @param1=44596, @param2='astring'"

Thank you for your patience

Sinndho · #7 (**permalink**) 07-29-11, 02:11

Note: What follows is an over-simplification of the Data Access Model.

If you want to retrieve values returned by the stored procedures you call from Access, you need an object that's capable of handling a data set returned by a SQL Server from which you can create a row set (i.e. values organized in rows and columns).

In access objects that can do that are: 1) a (linked) Table, 2) a Query, 3) a Connection. These objects act as connectors from which you can create a RecordSet which is the object that makes the data available to your application.

These objects can be obtained from 2 different COM objects: DAO (Data Access Object) or ADO (ActiveX Data Object) (let's forget about RDO).

- A (linked) Table is a passive container that can only be connected to a table or a view on the server, but not to a stored procedure.
- In one of the 2 libraries (DAO) the Connection object does not permit the OpenRecordSet method in every version of Access, so let's forget about it here.
-The only object that has an Execute method (for calling the stored procedure) and that has an OpenRecordSet method (for retrieving the data set) is the Query.

For several reasons I do not intend to explain here, I prefer working with DAO than with ADO when dealing with Access. This is the reason why I provided a solution based on a Query object from the DAO data access library.

tkepongo · #8 (**permalink**) 07-29-11, 11:33

So I'm going to attempt to dumb this down as much as I can

. If I have a very simple stored procedure such as:

Code:

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

And assuming I use code similar to your example:

Code:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strProcedureName As String
    Dim strQueryName As String
    Dim strConnection As String
    Dim location as string

   location = me.txtlocation
   strConnection = ODBC;DRIVER=SQL Server;SERVER=SA-PHAMT\SQLEXPRESS;APP=2007 Microsoft Office system;DATABASE=ProtoDB;Trusted_Connection=Yes;
   strProcedureName = "sp_GetInventory"
   strQueryName = "Qry_" & ProcedureName
    If DCount("*", "MSysObjects", "[Name]='" & strQueryName & "'") > 0 Then dbs.QueryDefs.Delete strQueryName
    Set qdf = dbs.CreateQueryDef(strQueryName)
    qdf.Connect = strConnection
qdf.SQL = strProcedureName & " ' & location & ' "

What would the query have to consist of to retrieve the Product and Quantity?

Sinndho · #9 (**permalink**) 07-29-11, 13:45

Just go on:

Code:

qdf.SQL = strProcedureName & " ' & location & ' "
Set rst = qdf.OpenRecordset
Product = rst!Product
Quantity = rst!Quantity

Once you have the recordset open, you just handle it like any other (local one). You can even open it in the Access database window. As its origin is a stored prodedure, it's read-only, though.

Internet Services

Web Development

Digital Marketing

Consumer Tech