Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Call an SQL Stored Procedure (XP/03)

    Too many sleepless nights this last week. I have a stored procedure in SQL Server 2005 Express. It requires no parameters. I just need to call it from code behind a control on a form and I am not able able to find the syntax.

    Any help is GREATLY appreciated.

    kwvh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Call an SQL Stored Procedure (XP/03)


  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Call an SQL Stored Procedure (XP/03)

    Hans,

    As usual, THANKS! I was obviously not using the correct search criteria. Is there a way to refine the search to find how to execute a procedure in Access without parameters. My pickle is I have a stored procedure that does not involve parameters, nor views. The procedure populates a temporary table. From what I read on the referenced link I was able to come up with:
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim strUID As String
    Dim strPWD As String
    Dim strConnect As String

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs
    sSQL = "exec spFirstInvention"
    strUID = "UserID"
    strPWD = "1234"
    strConnect = "ODBC;DRIVER={SQL Server}" _
    & ";SERVER = LAPTOP SQLEXPRESS " _
    & ";DATABASE = Florida " _
    & ";UID =" & strUID _
    & ";PWD =" & strPWD & ";"

    qdf.Connect = strConnect
    qdf.SQL = sSQL
    qdf.Execute

    Unfortunately it is not executing the procedure. If you can either point me in the right direction to continue searching, or if you have any recommendations, I would really appreciate it.

    Thanks,
    Ken

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Call an SQL Stored Procedure (XP/03)

    The article I referred to contains an essential line:

    qdf.ReturnsRecords = False

    This tells VBA that the querydef represents an action query. You must insert this line before qdf.Execute.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Call an SQL Stored Procedure (XP/03)

    Hans,

    Thank you so much. I missed that and will go back and reread it.

    THANKS!

    Ken

Posting Permissions

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