Solved

SQL stored procedure from Access

Posted on 2013-05-17
4
320 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now