sql mail

G

Guest

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
 
S

Sylvain Lafontaine

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
 
M

Malcolm Cook

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]
"(e-mail address removed)" , "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/mdobjconnection.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 -
(e-mail address removed) Applications Manager -
BioinformaticsStowers Institute for Medical Research - Kansas City, MO USA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top