Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2014
    Posts
    11

    Unanswered: Assigning form control value to Stored procedure via Pass Thru Query.

    Hi all,
    I am new to this forum and looks to me that this is the place I belong Please help me if possible on how to pass parameter from form control (List box, combo box etc.) to SQL Server the way that it will use that query in another query and so on. Not in the final query that will be used back in access. What I mean i that I have multiple layer query where in the middle I need to insert parameter from from control and after that continue building queries having this one in it and all via Pass Thru. I can not use VB for this to construct passthru because I need this passthru to be included in another query and so on. Here is sample example I am talking about:

    I have stored procedure Test1 for example with parameter @Param. I need to filter this SP with criteria from form Forms![Quoting]![Text4] (which is customer ID). I create passthru like this:

    SET NoCount ON

    DECLARE @VARTEST INT

    SET @VARTES = 10

    EXEC Test1 @Param = @VARTEST

    How can I replace 10 with Forms![Quoting]![Text4]? I can use View instead of stored procedure but still can not pass this value to view ether because view will need to be run at the sql server and sql server doesn't know what my form is all about. Please take into consideration that I already specified DNS connection in Passthru property. Is this doable at all??? Please help. Spent hours and hours to figure out and this is the last thing I need to start rewriting my program. Sorry for a long post. Thank you in advance
    Last edited by Timaxusa; 10-03-14 at 20:48.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Timaxusa: There are no replies to your post. If one had been posted (which is very unlikely without me being able to see it), it has been deleted.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    11
    Quote Originally Posted by Pat Phelan View Post
    Timaxusa: There are no replies to your post. If one had been posted (which is very unlikely without me being able to see it), it has been deleted.

    -PatP
    I understand that there are no reply's but I can not see post itself ether when I go inside of the post.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Depending on whether the stored procedure returns a data set or not, and on what you want to do with the returned data set if one is returned, there are several possibilities.

    1. The SP does not return any data set:
    Code:
    Sub ExecSP_NoReturn()
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL As String = "Test1 @Param = @V;"
        
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@V", Me.Text4.Value)
        '
        ' Note1: Outside the form containing the TextBox control Text4, use:
        ' -----  strSQL = Replace(c_SQL, "@V", Forms("Quoting").Controls("Text4").Value)
        '
        ' Note2: Ideally the value of @Param should be passed as a parameter to the sub:
        ' -----  ExecSP_NoReturn(Byval ParamValue As Variant)
        '        Then: ExecSP_NoReturn Me.Text4.Value
        '
        ' Note3: If Text4.Value is not numeric, you must format it to call the SP.
        ' -----
        '        a) For a Text value, change c_SQL to:
        '           Const c_SQL As String = "Test1 @Param = '@V';"
        '
        '        b) For a Date/Time value, use:
        '           Const c_SQL As String = "Test1 @Param = '@V';"
        '           strSQL = Replace(c_SQL, "@V", Format(Me.Text4.Value, "yyyy-mm-dd"))
        '
        Set qdf = CurrentDb.CreateQueryDef(QueryName)
        With qdf
            .Connect = c_Connect
            .SQL = strSQL
            .ReturnsRecords = False
            .Execute
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    2. You want to create a persistent query from the call to the SP. This is useful when you want to display the returned data set in a form, in a ComboBox control, etc.
    Code:
    Function ExecSP_PersistQuery(ByVal QueryName As String) As String
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL As String = "Test1 @Param = @V;"
        
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@V", Me.Text4.Value)
        If DCount("*", "MSysObjects", "name = '" & QueryName & "'") > 0 Then DoCmd.DeleteObject acQuery, QueryName
        Set qdf = CurrentDb.CreateQueryDef(QueryName)
        With qdf
            .Connect = c_Connect
            .SQL = strSQL
            .Close
        End With
        Set qdf = Nothing
        ExecSP_PersistQuery = QueryName
        
    End Function
    Exemple of use:
    Code:
    Me.Combo1.RowSource = ExecSP_PersistQuery("qry_Customers")
    3. You want to retrieve the values returned by the SP in an array:
    Code:
    Function ExecSP_Array(Optional ByVal QueryName As String) As Variant
    
        Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL As String = "Test1 @Param = @V;"
        
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
        Dim rst As DAO.Recordset
        Dim var As Variant
        Dim lngRowCount As Long
        
        strSQL = Replace(c_SQL, "@V", Me.Text4.Value)
        If Len(QueryName) > 0 Then
            If DCount("*", "MSysObjects", "name = '" & QueryName & "'") > 0 Then DoCmd.DeleteObject acQuery, QueryName
        End If
        Set qdf = CurrentDb.CreateQueryDef(QueryName)
        With qdf
            .Connect = c_Connect
            .SQL = strSQL
            Set rst = .OpenRecordset
            With rst
                If .EOF = False Then
                    .MoveLast
                    lngRowCount = .RecordCount
                    .MoveFirst
                    var = GetRows(lngRowCount)
                    .Close
                End If
            End With
            .Close
        End With
        Set rst = Nothing
        Set qdf = Nothing
        ExecSP_Array = var
        
    End Function
    There are many possible variations, but this should cover the basic cases.
    Have a nice day!

  5. #5
    Join Date
    Oct 2014
    Posts
    11
    Thank you for your help!!! My problem is that my SP will return records that will be used in another query. I can not use Function or any VB code for that because I will need to include results farther in other queries. Or I can? Not sure how to use VB function as part of SQL after that. Is there way to do it with SQL or T-SQL?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always build a pass-thru query by any mean you see fit (it can be a VBA procedure that will be used only once). In such a case, you will use the Forms!reference syntax inside the SQL expression of the query. Depending on the purpose of the whole process, you could possibly use a VBA procedure that would first call the function ExecSP_PersistQuery("QueryName"), then open the query associating the data set returned by the stored procedure to some other data. Normally, you do not allow users to directly open a table or a query : data is accessed through form and reports objects, so the VBA procedure could be executed when the form or the report that uses the combined query is openend.
    Have a nice day!

  7. #7
    Join Date
    Oct 2014
    Posts
    11
    You mentioned "you could possibly use a VBA procedure that would first call the function ExecSP_PersistQuery("QueryName"),then open the query associating the data set returned by the stored procedure to some other data", how can I use this in another query? ExecSP_PersistQuery("QueryName") is not ready yet to use in Form or report but will need to used in another query. If it possible maybe you can include simple example? Thank you again!

  8. #8
    Join Date
    Oct 2014
    Posts
    11
    As a test, I created function:
    Function ExecSP_PersistQuery(ByVal TimQ As String) As String

    Const c_Connect As String = "ODBC;DSN=ABCTracking;UID=Tim;Trusted_Connection=Y es;DATABASE=ABCTracking;"
    Const c_SQL As String = "Test1 @Param = @V"

    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    strSQL = Replace(c_SQL, "@V", Forms![Quoting]![Text4])
    If DCount("*", "MSysObjects", "name = '" & TimQ & "'") > 0 Then DoCmd.DeleteObject acQuery, TimQ
    Set qdf = CurrentDb.CreateQueryDef(TimQ)
    With qdf
    .Connect = c_Connect
    .SQL = strSQL
    .Close
    End With
    Set qdf = Nothing
    ExecSP_PersistQuery = TimQ

    End Function

    and in OnOpen procedure assigned:

    Me.List0.RowSource = ExecSP_PersistQuery("TimQ") to List0 ListBox but it's not working. Where is the mistake?

  9. #9
    Join Date
    Oct 2014
    Posts
    11
    As a test I created function:
    Function ExecSP_PersistQuery(ByVal TimQ As String) As String

    Const c_Connect As String = "ODBC;DSN=ABCTracking;UID=Tim;Trusted_Connection=Y es;DATABASE=ABCTracking;"
    Const c_SQL As String = "Test1 @Param = @V"

    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    strSQL = Replace(c_SQL, "@V", Forms![Quoting]![Text4])
    If DCount("*", "MSysObjects", "name = '" & TimQ & "'") > 0 Then DoCmd.DeleteObject acQuery, TimQ
    Set qdf = CurrentDb.CreateQueryDef(TimQ)
    With qdf
    .Connect = c_Connect
    .SQL = strSQL
    .Close
    End With
    Set qdf = Nothing
    ExecSP_PersistQuery = TimQ

    End Function

    ...and in form On Open trigger I assigned:

    Me.List0.RowSource = ExecSP_PersistQuery("TimQ")

    No Results. Where is mistake?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Open the query TimQ and see what you get. If there's an error, open it in SQL view then switch to Datasheet view, the offending part of the query should be highlighted and the error message should be more explicit.
    Have a nice day!

  11. #11
    Join Date
    Oct 2014
    Posts
    11

    Temp table creating in MS SQL

    Hi Sinndho,
    Thank you so much, this worked perfectly fine and I could get results on my forms from SP. Took me little time though Now, my question is how can I create temp table that I can use in further queries on SQL side instead of displaying results on the form control? Seems to me that the function #2 that you suggested doesn't change SP on SQL side and only does it on access front end of it. I can create different post for it if you require that. Please advice if possible.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not quite sure to understand your question. If you want to create something (a table, a view...) in the SQL database, you need to call a stored procedure from Access to do so. You can also build a query in Access based on a permanent query created by calling a strored procedure.

    Please provide more information or samples.
    Have a nice day!

  13. #13
    Join Date
    Oct 2014
    Posts
    11
    Looking for example of creating temp table with View or Stored Procedure. Can results of SP be recorded into temp table and check if temp table exists delete it prior creation?

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That could be done. Once the query calling the stored procedure has been called (Function ExecSP_PersistQuery()), you can use a second query to insert the returned data set into a table:
    Code:
    Const c_SQL As String = "SELECT * FROM @Q INTO @T;"
    
    Dim qdf As DAO.Querydef
    
    
    Set qdf = CurrentDb.CreateQuerydef("")
    qdf.SQL = Replace(Replace(c_SQL, "@Q", ExecSP_PersistQuery("TimQ")), "@T", "TempTable)
    qdf.Execute
    qdf.Close
    Set qdf = Nothing
    Have a nice day!

  15. #15
    Join Date
    Oct 2014
    Posts
    11
    This is nice. Thank you very much!!! Will try it today. Looks very simple and doable.

Posting Permissions

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