Send recordset through access

Posted on 2012-09-03
Last Modified: 2012-09-21
I currently have this code

ssql = "EXEC SelectexportReport @Datefrom='" & Me.txt_datefrom & "', @dateto='" & Me.txt_dateto & "'"

Dim rs As New ADODB.Recordset
rs.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

And i want to be able to send RS through email like you can with a table using the code

DoCmd.SendObject acSendTable, , acFormatXLS, "dan.james@***", , , "Employee List", "For your review.",False

However i get an error when using the record set, i have also tried ACSendquery instead of sendtable with no success
Question by:CaptainGiblets
    LVL 29

    Expert Comment

    Is SelectExportReport a table or query in Access?  If so, you could do it this way:

    Dim rs as DAO.Recordset
    Dim strSQL as String
    strSQL = "Select * From SelectexportReport Where Datefrom=#" & Me.txt_datefrom & "# AND Dateto=#" & Me.txt_dateto & "#"
    Set rs = CurrentDB.OpenRecordset(strSQL)
    Do While Not rs.EOF
        DoCmd.SendObject acSendTable, , acFormatXLS, rs!EmailTo, , , "Employee List", "For your review.",False    
    Set rs = Nothing

    Open in new window

    LVL 6

    Author Comment

    stored procedure.
    LVL 29

    Expert Comment

    Ah, sorry then.  I'm not familiar with that.  Hopefully someone else will be able to help you out.
    LVL 84

    Accepted Solution

    You could try building a PassThrough query that uses your Stored Procedure as the base, and then sending that PassThrough query. A PassThrough is basically a query that Access simply "passes through" to the underlying datasource. You must specify the connection string for the query, and Access will simply handle the return data (which you could then use in your SendObject, just as you would any other query).

    The links below might be helpful in understanding PassThrough stuff:

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now