Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    63

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

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by Sinndho View Post
    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 View Post
    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!!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'"
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's it, thanks healdem!
    Have a nice day!

  6. #6
    Join Date
    Jul 2011
    Posts
    63
    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

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  8. #8
    Join Date
    Jul 2011
    Posts
    63
    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?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Last edited by Sinndho; 07-29-11 at 18:01.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •