I am trying to run a stored procedure on a SQL-Server 2000 from an Access Database Front-End using a stored procedure. I am getting different errors, at different times and having trouble getting this thing to work. Sometimes it works, sometimes it doesn't. The stored procedure is a "Wrapper" for xp_sendmail:

CREATE proc dbo.sp_smtp_sendmail
@subject NVARCHAR(4000) = NULL,
@message NVARCHAR(4000) = NULL,
@attachments NVARCHAR(4000) = NULL,

declare @rc int

exec @rc = master.dbo.xp_sendmail
@recipients = @TO,
@message = @message,
@subject = @subject,
@attachments = @attachments

if (@@error <> 0 or @rc <> 0)
raiserror(N'Sending message using xp_smtp_sendmail failed', 16, 1)

return @rc

Here is how I call it:

Function sqls_mail(str_to As String, str_copy As String, str_subj As String, str_body As String, Optional str_attachment As String)
On Error GoTo OH_######

Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef

Set mydatabase = DBEngine.Workspaces(0).Databases(0)

DoCmd.DeleteObject acQuery, "qry_SendSQLMail"


Set myquerydef = mydatabase.CreateQueryDef("qry_SendSQLMail")

myquerydef.Connect = "ODBC;Description=Applications Database;DRIVER=SQL Server;SERVER=SYRON-AP-01SYRONSQL;DATABASE=db_applications;Trusted_Connec tion=Yes"

myquerydef.sql = "EXEC sp_smtp_sendmail @TO = '" & str_to _
& "', @message = '" & str_body _
& "', @CC = '" & str_copy _
& "', @subject = '" & str_subj & "'"

myquerydef.ReturnsRecords = False
For z = 1 To 100
Next z

DoCmd.OpenQuery "qry_SendSQLMail" 'this runs the new query which sends the email to SQL Server

Set mydatabase = Nothing
Set myquerydef = Nothing

Exit Function

If Err.Number = 7874 Then
Resume Next
MsgBox "Please try again." & vbCrLf & "Error Number: " & Err.Number & ". Error Desc: " & Err.Description, vbCritical, "email errorisom"
End If

End Function

I am getting these errors: 3167, 7874, but sometimes I get no errors and the code runs fine. I often get 7874 when it tries to delete the qrydef, but also get 7874 when it goes to open the query.

Any help would be great! Even If I should scrap this code and start from scratch.
Thanks, Keith