sql mail

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pjscott - 16 Aug 2005 17:39 GMT

I'm using sql server 2000 and access 2002. I have a maintenance work order
form setup to send a report using the click event. I want to start using sql
mail to do this so I've setup sql mail and have it working. But now I need
some information on how to execute the xp_sendmail procedure to run within
the click event of my form and send the report. Does anyone have an example
on how to do this?

Thanks for the help,

Paul

Reply to this Message

Sylvain Lafontaine - 16 Aug 2005 19:30 GMT

Use something like « CurrentProject.Connection.Execute "xp_sendmail ..." ».

If you need to call a stored procedure with parameters, you can either add
them at the end of above string or create an ADO Command Object, using the
CurrentProject.Connection as the connection to the SQL-Server:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "MyStoredProcedure"

cmd.Parameters.Append cmd.CreateParameter("@IdValue", adInteger,
adParamInput, , IdValue)

cmd.Execute
Set cmd = Nothing

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> I'm using sql server 2000 and access 2002. I have a maintenance work order
> form setup to send a report using the click event. I want to start using
[quoted text clipped - 8 lines]
>
> Paul

Reply to this Message

Malcolm Cook - 19 Aug 2005 16:07 GMT

I've not seen much MSAccess coders use this approach, but you should be able
to 'Execute a stored procedure as a native method of a Connection object'

Some experimentation with syntax shows that for xps in master, you can
invoke xp_sendmail simply as:

Application.CurrentProject.AccessConnection.[master.dbo.xp_sendmail]
"you@somewhere.org" , "hi there" , "" , "" , "" , "some subject"

However, unfortunately... When I try this (just cut and paste above line
into access' immediate window), it turns out that ADOs 'best guess' of
parameter types is wrong, since I get:
Run-time error '-2147217900 (80040e14)':

xp_sendmail: Supplied datatype for @recipients is not allowed, expecting
'varchar'

oh well - I tried - (any workarounds out there)

In any case, the 'native method' syntax is good to know and works
elsewhere...

c.f. ADO 2.8 API Reference for details on this method
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdob
jconnection.asp),
from which I excerpt:
To execute a stored procedure, issue a statement where the stored procedure
name is used as if it were a method on the Connection object, followed by
any parameters. ADO will make a "best guess" of parameter types. For
example:

Dim cnn As New ADODB.Connection
...
'Your stored procedure name and any parameters.
cnn.sp_yourStoredProcedureName "parameter"Regards,-- Malcolm Cook -
mec@stowers-institute.orgDatabase Applications Manager -
BioinformaticsStowers Institute for Medical Research - Kansas City, MO USA

> Use something like « CurrentProject.Connection.Execute "xp_sendmail
> ..." ».
[quoted text clipped - 31 lines]
>>
>> Paul

Reply to this Message