Solved

SQL stored procedure from Access

Posted on 2013-05-17
4
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

628 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