Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Red face Unanswered: Pass user input variable to SQL Stored Procedure

    I have a Stored Procedure on a MS 2005 SQL server - FindName.

    I created a passthrough query setup in MS Access 2007 that executes the SP in SQL perfectly.

    example: exec spFindName 'TEC42890'

    and it returns the record.

    Now, I want to have the user in MS Access open a form, it asks for the USERID and it passes the user imput variable to my Stored Procedure in SQL.

    I looked at some of the threads in the archive, but could not find anything that was doing what I am trying to do.




  2. #2
    Join Date
    Nov 2003
    Posts
    300
    Any thoughts?

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One way is to use a DAO QueryDef to alter the SQL of a saved passthrough query. You'd build a string:

    strSQL = "exec spFindName '" & Me.TextboxName & "'"

    then assign that SQL to the query. More info in help on the QueryDef object.
    Paul

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, that is the only method I know.
    Note however that any SQL Server DBA that saw that would have a fit.
    SQL Injection.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Okay, don't know much about VBA but is this something like what you are suggestion to do?

    Sub DAO_FindUID(UID As String)
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Set db = CurrentDb
    Set qdef = db.QueryDefs("qryUIDdetail")
    qd.SQL = "exec spFindName '" & UID & "'"
    qdef.Close
    db.Close
    End Sub

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That looks about right. Does it work?
    Paul

  7. #7
    Join Date
    Nov 2003
    Posts
    300
    Not real well.

    I have the text box setup on the form so the user can enter in the ID# to put into the SQL SP. But I am not sure which event to use to trigger once they enter in the ID#

    Private Sub UserUID_LostFocus()
    Sub DAO_FindUID(UID As String)
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Set db = CurrentDb
    Set qdef = db.QueryDefs("qryUIDdetail")
    qd.SQL = "spFindName'" & Me.UserUID & "'"
    qdef.Close
    db.Close
    End Sub
    End Sub

    Blew up on the 1st line of code.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It blew up because you have a sub within a sub. Where depends on what you're using it for. If it's for a report, I'd run the code from the button the user clicks on to run the report.
    Paul

  9. #9
    Join Date
    Nov 2003
    Posts
    300
    I was using a form and an unbound text box on the form, this is where I want the user to type in the Unique ID... "UID" and then click on a command button and using the ONCLICK, execute this

    Sub DAO_FindUID(UID As String)
    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Set db = CurrentDb
    Set qdef = db.QueryDefs("qryUIDdetail")
    qd.SQL = "spFindName'" & Me.UserUID & "'"
    qdef.Close
    db.Close
    End Sub

    where the Me.UserUID is the name of the text box..

    is this correct?

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You took the "Exec" part out. Does it work?
    Paul

  11. #11
    Join Date
    Nov 2003
    Posts
    300
    sorry, copied it wrong,

    qd.SQL = "exec spFindName'" & Me.UserUID & "'"

    is correct..

    I am getting a runtime error 424 on that line,
    Object required.

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Set qdef = db.QueryDefs("qryUIDdetail")
    qd.SQL = "spFindName'" & Me.UserUID & "'"

    You likely also want that space after the SP name, before the single quote that you took out.
    Paul

  13. #13
    Join Date
    Nov 2003
    Posts
    300
    sorry, took a few days off.

    well that part worked great!

    also, I am getting a value of the ID when I hoover over the ID in the VB code, using step by step.

    But how do i get the results to show?

    Do i use do.cmd and open the passthrough query? need help here as I am pretty close, I guess.

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm often using that pass through query as the source of a report, so I'd run that process to update the SQL and then open the report. Most developers only expose forms and reports to users, never queries or tables. It's a control thing.
    Paul

  15. #15
    Join Date
    Nov 2003
    Posts
    300
    No problem, I understand that~!

    Just one more thing, everything works great-- thanks for the help!

    Is there anyway to put in the cresentials that the SQL - ODBC driver keeps asking every single time I run this?

Posting Permissions

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