Run a SQL stored procedure from a command button

[Winston]

Hello everyone,

I am trying to execute a stored procedure from a command button in an Access .adp form.
The error I am receiving is "A RunSQL action requires an argument consisting of an SQL statement." The stored procedure does an update and then interests data into another table.

When I look at the ?strSqlUpd in the VBA immediate window it shows EXEC sp_gla '1/1/2009', '1/1/2011', 2. When I run that line from the query analyzer in SQL, the stored procedure executes properly.

I am probably missing something simple and any help or advice would be greatly appreciated.

Here is the code that I am using to execute the stored procedure.
Dim strSqlUpd As String
Dim strSD As String
Dim strED As String

strSD = "1/1/2009" 'dates are pre set for testing
strED = "1/1/2011"

'strSqlUpd = "EXEC sp_gla '" & strSD & "', '" & strED & "', 2"
DoCmd.RunSQL strSqlUpd

Thanks,
Winston

[Winston]

Solved -

One of my coworkers pointed out that the RunSQL requires it to be an action query. Even though the stored procedure is performing actions allowed by the RunSQL, I am doing an update and an insert statement. When executing more than one action type was, what he thought was causing the error message. I will have to test that theory out later. He said I should look and see how to execute against a data source.

This is what I worked out and it works as intended.

Dim cmd As ADODB.Command
Dim strSD As String
Dim strED As String

strSD = "1/1/2009" 'dates are pre set for testing purposes only
strED = "1/31/2011"

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.sp_gla"
.CommandType = adCmdStoredProc
.CommandTimeout = 120

.Parameters.Append .CreateParameter("@Enter_Start_Date", adVarChar, adParamInput, 12, strSD)
.Parameters.Append .CreateParameter("@Enter_End_Date", adVarChar, adParamInput, 12, strED)
.Parameters.Append .CreateParameter("@RunPro", adVarChar, adParamInput, 1, 2)


.Execute

End With

Winston

[HansV]

A belated welcome to Eileen's Lounge, and thanks for posting the solution.