Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: SQL Pass-through Query with Combo Box Paramater

    Hi,

    I am in need of a way to get a pass-through query to accept a parameter from a combo box. What I currently have is a stored procedure on the SQL server that runs with two parameters. However, I want it to be available in the frontend so that it does not need to be accessed by going to the server.

    Currently, it runs if I type
    Code:
    EXEC proc_incorrect_emis_fix '1', '2'
    However, I want it to run without having to change the query every time. Something like
    Code:
    EXEC proc_incorrect_emis_fix @old_emis, @new_emis
    and the @old_emis and @new_emis to be passed from a pop up box that the user can see.

    Is there any way to do this? I have looked online, but everything that is explained is in VBA using the WHERE clause. The WHERE is already taken care of in the stored procedure, and I just need the parameters to be passed.

    Thanks,
    Andrew

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    Dim dbs as DAO.Database
    Dim qdf as DAO.QueryDef
    Dim strSQL as String
    Const c_strSQL as String = "EXEC proc_incorrect_emis_fix @old_emis={P1}, @new_emis={P2}"
    
    Set dbs = CurrentDb     ' or any db where the query is stored.
    Set qdf = dbs.Querydefs("<QueryName>")
    strSQL = Replace(c_strSQL , "{P1}", "<First parameter>")
    strSQL = Replace(strSQL, "{P2}", "<Second parameter>")
    qdf.SQL = strSQL
    qdf.Execute
    Set qdf = Nothing
    Set dbs = Nothing
    Notice that if the query does not return any data, you could as well use the Execute method of a DAO.Connection object. Both techniques (or very similar ones) exist using ADO.
    Have a nice day!

  3. #3
    Join Date
    Jul 2009
    Posts
    47
    Thank you for the reply! It is almost working, however I get an error on the qdf.Execute. I think I edited your code correctly for mine, but I must be missing something

    Code:
    Private Sub Fix_Click()
    
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Const c_strSQL As String = "EXEC proc_incorrect_emis_fix @old_emis={P1}, @new_emis={P2}"
    
    Set dbs = CurrentDb     ' or any db where the query is stored.
    Set qdf = dbs.QueryDefs("emis_fix_in_progress") 'the query name with "in progress" to let others know it isn't complete
    strSQL = Replace(c_strSQL, "{P1}", "old_emis") 'old_emis is the name of the old emis text box
    strSQL = Replace(strSQL, "{P2}", "new_emis") 'new emis is the name of the new emis text box
    qdf.SQL = strSQL
    qdf.Execute
    Set qdf = Nothing
    Set dbs = Nothing
    
    End Sub
    When I click the button, I get "Runtime error '3146': ODBC--call failed." Any ideas what would cause this? I Googled it, but nothing seemed to solve my problem yet.

    Thanks

  4. #4
    Join Date
    Jul 2009
    Posts
    47
    I figured it out! Thank you for all your help.

    I ended up fully qualifying the text box's name to [Forms]![f_fix_emis]![old_emis] and [Forms]![f_fix_emis]![new_emis] then removed the quotes from around them. The pass through now works flawlessly.

    I had another question though. Is it possible to return the rows, or the number of rows affected? If I was to run this on the SQL server, I would get "x rows affected". Is there anyway to get that data returned Access?
    Last edited by apurgert; 11-04-10 at 14:01. Reason: More information

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it's possible: the Querydef has a RecordsAffected property, for instance:
    Code:
    qdf.Execute
    MsgBox qdf.RecordsAffected & " records affected.",vbInformation, "emis_fix_in_progress"
    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
  •