Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xp_sendmail stored proceedure from VB (Access02 VBA)

    I have several Access databases connected to SQL-Server Back-ends and I want to start incorporating the xp_sendmail stored procedure for them (now I use Outlook at the front end and I need to move away from this). Can anyone help me get started with some VB code to use xp_sendmail from and Access .mdb? I have never ran a Stored Procedure from Access. I have the SQL Server all set to go (I can use xp_sendmail form query analyzer and it works). Any help would be great!

    Thanks, keith

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: xp_sendmail stored proceedure from VB (Access02 VBA)

    Did you want to use DAO or ADO?

    The simplest way is to use this construction:

    <connection object>.Execute "EXEC <sp name>"

    Here's a sample using DAO and ODBCDirect:

    'Execute SP From Access
    'Declare the variables
    Private wkTest As Workspace, conTest As Connection
    Private DSNName As String, CONNECT_STR As String

    'Populate the connection strings
    DSNName = DLookup("DatabaseLocation1", "Setup", "CurrentConnection = " & True)
    CONNECT_STR = "ODBC;DSN=" & DSNName & ";UID=" & sqluser & ";PWD=" & sqlpassword & ";DATABASE = " & DSNName

    'populate the object variables
    Set wkTest = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
    Set conTest = wkTest.OpenConnection("CONNECTION", dbDriverNoPrompt, True, CONNECT_STR)
    conTest.QueryTimeout = 0

    'Execute the stored procedure (SQL Server 7.0)
    DoEvents
    conTest.Execute "EXEC PurgeOldServiceData '" & Me.Text77.Value & "'"
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: xp_sendmail stored proceedure from VB (Access02 VBA)

    You don't indicate what method you are using to connect to to SQL Server - is you Access front-end an MDB or an ADP? If it's an ADP, it's fairly easy as you can invoke stored procedures directly from Access. If you are using an MDB with ODBC linked tables, the process is more complicated. The simplest way is to create a PassThrough query on the fly and then execute it

    I should note that our experience with xp_sendmail has not always been trouble-free. As I recall, it also requires that you have an email client installed on your server, and there seem to be glitches that ocurr from time to time. What is your reason for wanting to not use Outlook?
    Wendell

  4. #4
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xp_sendmail stored proceedure from VB (Access02 VBA)

    I don't want to use Outlook anymore for a few reasons:

    1. I can no longer use the Set MyOutlook = New Outlook.Application OR Set MyOutlook = CreateObject("Outlook.Application") methods using Access 2003 under Windows XP ServicePack2, and we just got 35 new machines with this loaded on them and I am stuck with it. I cannot figure out a fix to this, it seems XP SP2 has placed more security features blocking email access.
    2. Our IT department doesn't like me sending mail from the front-end computers - they want me to off-load it to the b.e. server.
    3. I am writing a new module for an existing database that is going to send out a batch of 100 or so emails at a time, and xp_sendmail seems like a more robust solution.

    I have our server configured now and I can call xp_sendmail dirrectly using Query Analizer- I hope it will be reliable. We are running SQL Server 2000 on a Win2003 Server, I am using Outlook 2000 as a client on the server going through Exchange.

    The database is an mdb using an ODBC connection.

    Thanks

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: xp_sendmail stored proceedure from VB (Access02 VBA)

    OK - I agree the stuff that's been done with SP2 and 2003 does make things difficult. Since you are using an MDB front-end, you will either need to use ADO as Charlotte suggested, or create Pass Through queries on the fly that invoke the xp_sendmail stored procedure. The latter requires that you understand DAO well enough to create and save a QueryDef that contains the SQL statement for the stored procedure. If you've not been through that before, a good resource is "Beginning Access 2000 VBA" by Smith and Sussman, published by WROX. The 2002 version or the 97 version would be OK too. The other thing to do is to look up the xp_sendmail stored procedure in SQL Server Books Online.

    The problems we had with it were related to things that were run by the scheduler - notifying staff when an operation failed, that sort of thing. Since you are doing it from Access, it will likely be more reliable.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xp_sendmail stored proceedure from VB (Access02 VBA)

    Thank you both, I will give this a go...

  7. #7
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xp_sendmail stored proceedure from VB (Access02 VBA)

    well I got something to work - I am using Pass Through method (I could not get charlotte's code to work). Thanks for your help, I will post my code in case anyone eles can benefit, and if anyone has some feedback on it (I love feedback), I will continue to refine it to fit my app, handle errors, and be more robust. One question though, using this method, is ther a way to recieve confermation form SQL Server (other than a copy of the email)?

    Thanks Again, Keith


    <font color=4682b4> Function sqls_mail()


    DoCmd.DeleteObject acQuery, "qry_SendSQLMail"

    DoEvents
    DoEvents
    DoEvents

    Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef
    Dim strEmailMessage As String, EmailCopyTo As String
    Set mydatabase = DBEngine.Workspaces(0).Databases(0)
    Set myquerydef = mydatabase.CreateQueryDef("qry_SendSQLMail")

    strEmailMessage = "Testing the email via access and SQL Server"
    EmailCopyTo = "clive.gillett@syron.com"
    myquerydef.Connect = "ODBC;DATABASE=master;UID=kfw;PWD=Manageme231;DSN= sy_master;"
    '"ODBC;DSN=odbc_db_master.dsn;Description=Master;U ID=kfw;PWD=kfw;DATABASE=master;Network=DBMSSOCN "
    myquerydef.SQL = "EXEC xp_sendmail @recipients = 'keith.walter@syron.com" & "', @message = '" & strEmailMessage & "', @copy_recipients = '" & EmailCopyTo & "', @subject = 'Hello From sqls_mail'"
    myquerydef.ReturnsRecords = False
    myquerydef.Close
    For Z = 1 To 100
    DoEvents
    Next Z

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



    End Function </font color=4682b4>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •