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

Answer 1

E.g.

Currentproject.connection "EXECUTE yourProcName;"

Answer 2

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

Answer 3

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

Answer 4

>>>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