August 15, 2005 at 10:42 am
I have a stored procedure with multiple select statements in it which executes fine in SQL Query Analyzer, but when I cut/paste it into an Access Stored Procedure, access will only show results (datasheet view) for the first select statement. Wondering if anyone knows if this is possible.
Cheers,
Rich
August 15, 2005 at 2:15 pm
are you working with DAO or ADO in Access?
I'm almost certain that DAO only supports one set of data returned
August 15, 2005 at 2:19 pm
I working with ADO, I'm using an Access project with a SQL server database.
August 15, 2005 at 2:38 pm
Hello, I take it that the selects represent similar information extracted by the various select statements. If so, you might bypass your 'problem' by merging the selects using UNION.
Regards,
August 15, 2005 at 2:49 pm
pereke, thanks for the suggestion, unfortunately it wont solve the problem as the resultsets returned are of different lengths thus the UNION operator will not work. Have a feeling I will have to split the SP up and place each select statement in its own query. This will be a pain as the original SP is the record source for a report which means I will have to create individual subreports for each query as well - this means 1 report and 1 SP will be split up into 4 querys and 4 subreports. There must be a better way of doing it.
August 15, 2005 at 3:45 pm
I just tried a few tests to mirror your SP, and it appears that Access always generates the recordset from the first statement that creates a recordset.
You're out of luck with this approach.
You should put your last SELECT statement in a separate SP, and then call it after you have done any necessary processing in the first SP. Alternatively, you will have to rewrite the SP as a single complex SELECT statement that returns your recordset.
HTH,
Rich
August 15, 2005 at 10:25 pm
In ADO you can access multiple recordsets using the NextRecordset method, re:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthnextrec.asp
Andy
August 16, 2005 at 1:11 am
Access can only handle one SQL statement at a time in queries and Access stored procedures. That is just how it is. Also, if you are trying to set your report's recordset to an ADO recordset, you will be out of luck there too. Only Access forms can have their recordset property set to an ADO recordset. I don't know why the Access team never finished this off as it is a real pain and a real pity. If this is what you are trying to do and manage find a work round, do let me know as I've spent ages trying to find one. At present my thoughts are that it is best to have the SQL Server tables linked into the Access front end as read-only and work off those for reports.
Alan
August 16, 2005 at 2:25 am
Hello again, I see you have a challenging task. Just a quick re-reply on UNION in general. You may in principle UNION 'whatever you like' as long as you know what you are doing, that is you may substitute missing columns in a unit of the UNION with dummy columns, convert data types etc. to create the final result set the way you want it as long as each unit has similar number of columns and data types are similar (which you point out). My point is that you are free to make them 'identical', a unit of the union may even be a select with one record information that the rows 'below' comes from another set etc. It may not be the best approach, but while you are looking for the better, it should work at least.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply