• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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
0
CaptainGiblets
Asked:
CaptainGiblets
  • 2
1 Solution
 
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
 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now