How to call a ms-sql stored procedure from MsAccess?
Here i use also the parameters (emailadres and some_text)
1. start a msaccess application.
2. create a button a a form and paste the next code to it.
Dim emailadres As String
emailadres = InputBox("Uw emailadres is:... ")
If len(emailadres) <> 0 Then
test (emailadres)
End If
3. next create a new module and past the next code to it.
Sub test(emailadres)
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
With param
.Type = adChar
.Size = 100
.Value = emailadres
cmd.Parameters.Append param
End With
Dim param2 As New ADODB.Parameter
With param2
.Type = adChar
.Size = 100
.Value = "some text"
cmd.Parameters.Append param2
End With
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=databasename;UID=loginname;PWD=password;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = emailadres
cmd.Execute
End Sub
4. goto sql server and add a stored procedure to it. Like this one:
CREATE PROCEDURE [dbo].[emailMe]
(
@emailadres as varchar(10) --param
,@onderwerp as varchar(50) --param2
)
AS
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = @emailadres
, @subject = @onderwerp
END
and voila! Sending a email initiated from msaccess.