Answered by:
Executing a SQL stored procedure

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