how to execute stored procedure from access vba using DAO with trusted connection, ODBC, DSN = 'DTe' 3

[tnguyen315]

This is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ResendPayEmail]
(
@id int
)
AS

DECLARE @operatorname AS nvarchar(100),
@operatoremail AS nvarchar(100),
@title AS nvarchar(100),
@message AS nvarchar(max),
@webpermitid AS int

--CHECK IF Database Mail Configured
IF EXISTS (SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs' AND value_in_use = 1)
BEGIN
SELECT @id = Operational_ID, @operatorname = Operator_Name, @operatoremail = Operator_Email FROM TableA
WHERE Operational_ID = @id

IF @operatoremail IS NOT NULL
BEGIN
SELECT @webpermitid = WebPermitID FROM tblWebPermit WHERE PrelimApproval is null AND ApprovalID = @id AND PermitType = 'T'
--CHECK IF corresponding record is in WebPermits
IF @webpermitid IS NOT NULL
BEGIN
--SET Subject
SET @title = 'Permit'
--SET Body
SET @message = '<img alt="" src="

http://www.abc.com/Templates/bi.jpg"/><br/><br/>'

+ ''
SET @message += @operatorname + ',<br /><br /> approved</b>.<br />'+
'Your Operator ID number is ' + CAST(@id AS nvarchar(max)) + '.<br /><br />'
SET @message += '

http://test.aspx?id='

+ CAST(@id AS nvarchar(max))
SET @message += '<br /><br />If you have any questions, please call us at 800-800-8888 or email us at test@abc.com.<br /><br />' +
'Thank You,<br /><br />'


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Test Email',
@body_format = HTML,
@recipients = 'ts@abc.com',
@body = @message,
@subject = @title
END
END
END


On my form i have a button named "cmdResendEmail"
Thanks
TWEE

 

[lameid]

An answer...
faq705-2531


You could also use the ADO command object. That should also be well documented if you search on it.

 

[tnguyen315]

i tried to use this but got error when i clicked on the button
error : complile error:
User-definied type not defined

Private Sub cmdSendEmail_Click()
On Error GoTo Err_cmdSendEmail_Click
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strConn As String
strConn = "DRIVER={SQL Server};SERVER=server1;DATABASE=dba1;Trusted_connection=yes"
conn.ConnectionString = strConn
conn.Open
cmd.ActiveConnection = conn
cmd.CommandText = "sp_ResendPayEmail"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing
Set conn = Nothing

Exit_cmdSendEmail_Click:
Exit Sub
Err_cmdSendEmail_Click:
MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub

 

[lameid]

Two immediate thoughts...

It seems like you probably do not have a reference forADO set. In a VBA window, go to Tools and references and ensurs something that begins "Microsoft ActiveXData Objects" is checked. Generally I go for the highest 2.x version on the system.

Secondly your SQL Procedure takes takes a parameter and you are not passing one. It's been a while for me but there is a parameter collection of some sort for the command object.

 

[tnguyen315]

OK here's what i did,
i added the Microsoft ActiveXData Objects 2.5 as you said


i create a module named "ResendPaymentEmailAddress"

Option Compare Database
Option Explicit

Public rst As ADODB.Recordset
Public con As ADODB.Connection
Public strCon As String
Public strOperatorID As Integer
Public strOperatorName As String
Public strOperatorEmail As String

Public Function ResendPaymentEmailAddress(strOperatorID As Integer, strOperatorName As String, strOperatorEmail As String) As String

On Error GoTo ErrorFunction
Set con = New ADODB.Connection

strCon = "Provider=sqloledb;Data Source=svpphesdb02; Initial Catalog=FoodDev;Trusted_connection=yes;"
strCon = "DRIVER={SQL Server};SERVER=svpphesdb02;DATABASE=FoodDev;Trusted_connection=yes;"
con.Open strCon

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = con

rst.Source = "EXEC sp_ResendPayEmail " & strOperatorID & ", '" & strOperatorName & "', '" & strOperatorEmail & "'"
rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly
rst.Open

ResendPaymentEmailAddress = rst!ResendPaymentEmailAddress

rst.Close
con.Close

ExitFunction:
Set rst = Nothing
Set con = Nothing
Exit Function
ErrorFunction:

GoTo ExitFunction
End Function



Here's my form:

Private Sub cmdSendEmail_Click()
Dim strResendEmail As String

'If Not IsNull(Me.Operational_ID) And IsNull(Me.Operator_Name) And IsNull(Me.Operational_Email) Then

strOperatorID = Me.Operational_ID
strOperatorName = Me.Operator_Name
strOperatorEmail = Me.Operator_Email

If MsgBox("Do you want to re-send email?", _
vbYesNo + vbQuestion, "Send Email?") = vbNo Then
Exit Sub
Else

'call procedure
strResendEmail = ResendPaymentEmailAddress(strOperatorID, strOperatorName, strOperatorEmail)

MsgBox "Email Sent!", vbOKOnly, "Email Sent!"

End If
' End If
End Sub



I step thru the code and it stopped right here:

rst.Open


didn't go thru this
ResendPaymentEmailAddress = rst!ResendPaymentEmailAddress

what did i do wrong?
Thanks

 

[lameid]

Unless I am missing something, your stored procedure does not return anything and you simply should use a command object. If it returns a record, you could open a recordset from the command object...

Check out:

http://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters

 

[dhookom]

i create a module named "ResendPaymentEmailAddress"

Public Function ResendPaymentEmailAddress

Don't name a sub or function the same as the module. That is one of the reasons most of use a naming convention. Considering using a convention that prefixes your modules with "mod" or similar.

I don't know if this is your current problem but it has bitten many developers in the past.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Duane, that bit of advice is worth a star to me... I've always used a prefix for my modules so I've never hit it but good to know it is out there.

 

[tnguyen315]

thanks dhookom for your advice. However, sorry for the confusion.
my module name "modSendEmail" and function named "ResendPaymentEmailAddress"

Public Function ResendPaymentEmailAddress(strOperatorID As Integer, strOperatorName As String, strOperatorEmail As String) As String


i tested with no error but i don't send any email sent out.

actually i don't need this
ResendPaymentEmailAddress = rst!ResendPaymentEmailAddress

i don't need to pass this code to any field just want to execute the stored procedure if email is not null
again thanks

twee

 

[tnguyen315]

I got it worked, all i needed is OperatorID only

i changed these on function and code:

Public Function ResendPaymentEmailAddress(strOperatorID As Integer) As String
,....
....
rst.Source = "EXEC sp_ResendPayEmail " & strOperatorID

from my code changed to this:

strResendEmail = ResendPaymentEmailAddress1(strOperatorID)