Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL stored procedure from Access

Posted on 2013-05-17
4
Medium Priority
?
368 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 66

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 1500 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

971 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