How Do I Return Multiple Recordsets from SQL Stored Procedure in Access 2010

I’ve created a pass-through query in Access which executes a stored procedure that searches for a string across all tables in my SQL database. The stored procedure on the SQL server runs as expected, returning multiple Recordsets that contain the value of my search string. However, when I double-click on the pass-through query in Access, in Datasheet View I see the results of only one Recordset.  Since it appears that Access is not designed to handle multiple result sets, then how do I use VBA in Access to accomplish this?
Darlyne PittTech & CRM ConsultantAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I don't think you'd see those results in the query window. You'd have to manage this through code, something like:

Dim rst As New ADODB.Recordset
rst.Connection = YourADOConnection
rst.OPen "EXEC YourSPName"

Dim rst1 As Variant
Dim rst2 As Variant

rst1 = rst.NextRecordset
rst2 = rst.NextRecordset
'/ and so on

This is aircode, from some stuff I did a loooong time ago ... hope it helps.
Darlyne PittTech & CRM ConsultantAuthor Commented:
I think this will work for me.  I also found this MS article that has a similar approach:
Gustav BrockCIOCommented:
I think that was for an ODBCdirect workspace - which is no longer supported in Access but still mentioned in the on-line help (look it up).

Thus, I believe, this is not possible with A2010+.

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.