Solved

SQL stored procedure from Access

Posted on 2013-05-17
4
339 Views
Last Modified: 2013-05-20
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
0
Comment
Question by:HKFuey
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39174769
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
 

Author Comment

by:HKFuey
ID: 39175083
Sendmail is active, the sp looks like this: -

            EXEC master.dbo.xp_sendmail
                  @recipients= "me@domain.com",
                  @message = "test message",
                  @subject = "test subject"
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39176515
The message says it all. The user does not have EXECUTE permissions on the system Stored Procedure.
0
 

Author Closing Comment

by:HKFuey
ID: 39181627
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question