Stored Procedure "...did not return records" Error in Access 2007

[PaulB]

I have an adp project connected to a SQL Server 2005 database.
I have been trying to execute a very simple stored procedure that simply
clears two tables:
DELETE FROM tbl1
DELETE FROM tbl2
Executing this procedure from anywhere (Access 2007, SQL Server 2005)
generates a message: "The stored procedure executed successfully but did not
return records." When I run this using the DoCmd.OpenStoredProcedure in a VB
module, the stored procedure generates and error and returns the above
message. I have tried using SET NOCOUNT ON in the stored procedure and
"DoCmd.SetWarnings False" in the VB code, but they don't seem to have any
affect. How can I get around this???

VB Code snippet:
On Error GoTo ErrorExit
DoCmd.SetWarnings False
DoCmd.OpenStoredProcedure "dbo.myStoredProedure"
....
....
ErrorExit:

 

[Graham R Seach]

Paul,

OpenStoredProcedure is intended to literally "open" the results of a stored
procedure, which is why you get that message. Try doing it this way:

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.myStoredProedure"
.CommandType = adCmdStoredProc
.Execute
Set .ActiveConnection = Nothing
End With
Set cmd = Nothing

....or you could just do it this way:
CurrentProject.Connection.Execute "DELETE FROM tbl1"
CurrentProject.Connection.Execute "DELETE FROM tbl2"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[PaulB]

Yup, both ways do the trick. Thanks very much, Graham. This is a big help.