Send recordset through access

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@***co.uk", , , "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
LVL 6
CaptainGibletsAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

http://www.databasejournal.com/features/msaccess/article.php/3407531/How-to-Execute-SQL-Stored-Procedures-from-Microsoft-Access.htm

http://accessexperts.net/blog/2011/07/29/sql-server-stored-procedure-guide-for-microsoft-access-part-1/
0
 
IrogSintaCommented:
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    
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Open in new window

0
 
CaptainGibletsAuthor Commented:
stored procedure.
0
 
IrogSintaCommented:
Ah, sorry then.  I'm not familiar with that.  Hopefully someone else will be able to help you out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.