sql mail

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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
 
Back
Top