SQL stored procedure from Access

I have this VBA: -

Dim objRs As New ADODB.Recordset
Const strCon = "Driver={SQL Server};Server=SERVER;Database=DB;Trusted_Connection=yes;"
Dim objCmd As New ADODB.Command
Option Compare Database

Private Sub Command0_Click()
        objCmd.CommandText = "Test"
        objCmd.ActiveConnection = strCon
        Set objRs = objCmd.Execute
End Sub

Open in new window


When I run it I get an error (See image below) Can anyone help?
Error
HKFueyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
The message says it all. The user does not have EXECUTE permissions on the system Stored Procedure.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, the VBA code above doesn't use xp_sendmail, unless that's in your stored proc named "Test".

Second, xp_sendmail may not be active (i.e. it has to be specifically turned on by a DBA) on the SQL Server that you are using.   Last I remember this is turned off by default.
0
 
HKFueyAuthor Commented:
Sendmail is active, the sp looks like this: -

            EXEC master.dbo.xp_sendmail
                  @recipients= "me@domain.com",
                  @message = "test message",
                  @subject = "test subject"
0
 
HKFueyAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.