locked
Executing a SQL stored procedure RRS feed

  • Question

  • Hi,

    I am running the following code to execute a stored procedure which isn't correct as I am not pulling back any dataset.  Can anyone advise what code to use for executing a stored procedure and not returning a dataset.

    Dim dst as ADODB.Recordset

    dst.open proedurename, Currentproject.connection,adopenkeyset, adLockReadonly

    dst.close

    Saturday, April 9, 2016 7:03 AM

Answers

  • E.g.

    Currentproject.connection "EXECUTE yourProcName;"

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:21 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:25 PM
    Saturday, April 9, 2016 5:58 PM
  • One really easy way is to save a pass-though query.

    You can then execute it like this:

    currentdb.QueryDefs("MyPass").Execute

    Given that the sproc does not return records, then make sure the query is set to not return records

    Eg:

    The above approach also eliminates connection strings in your code.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:22 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:25 PM
    Sunday, April 10, 2016 12:58 AM
  • >>>Can anyone advise what code to use for executing a stored procedure and not returning a dataset.

    According to your description, you could refer to below code:
    Dim conn As ADODB.Connection 
    Dim cmd As ADODB.Command 
     
    Set conn = New ADODB.Connection 
    conn.ConnectionString = “your connection String here” 
    conn.Open 
     
    Set cmd = New ADODB.Command 
    cmd.ActiveConnection = conn 
    cmd.CommandType = adCmdStoredProc 
    cmd.CommandText = "put stored procedure name here" 
     
    cmd.Execute 
    conn.Close 
     
    Set conn = Nothing 
    Set cmd = Nothing 

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:21 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:24 PM
    Monday, April 11, 2016 2:36 AM

All replies

  • E.g.

    Currentproject.connection "EXECUTE yourProcName;"

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:21 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:25 PM
    Saturday, April 9, 2016 5:58 PM
  • One really easy way is to save a pass-though query.

    You can then execute it like this:

    currentdb.QueryDefs("MyPass").Execute

    Given that the sproc does not return records, then make sure the query is set to not return records

    Eg:

    The above approach also eliminates connection strings in your code.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:22 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:25 PM
    Sunday, April 10, 2016 12:58 AM
  • Unfortunately, the above “only” works if you using an ADP project in Access (they are deprecated).

    So, either my simple pass-through idea, or some “typical” ADO solution can be used.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:22 PM
    • Unproposed as answer by David_JunFeng Friday, April 15, 2016 2:22 PM
    Sunday, April 10, 2016 1:00 AM
  • >>>Can anyone advise what code to use for executing a stored procedure and not returning a dataset.

    According to your description, you could refer to below code:
    Dim conn As ADODB.Connection 
    Dim cmd As ADODB.Command 
     
    Set conn = New ADODB.Connection 
    conn.ConnectionString = “your connection String here” 
    conn.Open 
     
    Set cmd = New ADODB.Command 
    cmd.ActiveConnection = conn 
    cmd.CommandType = adCmdStoredProc 
    cmd.CommandText = "put stored procedure name here" 
     
    cmd.Execute 
    conn.Close 
     
    Set conn = Nothing 
    Set cmd = Nothing 

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:21 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:24 PM
    Monday, April 11, 2016 2:36 AM