Need help using Multiple Select Statements

[jspurlin]

I need to reproduce an Sql Server 2000 stored procedure as a MS Access Query that uses multiple select statements and returns multiple results. Is this even possible?

Here is the first select statement:
PARAMETERS [TabId] Long, [PortalId] Long;

SELECT Portals.PortalId, Portals.PortalName, Tabs.TabId, Tabs.TabOrder, Tabs.TabName, Tabs.MobileTabName, Tabs.AuthorizedRoles, Tabs.ShowMobile
FROM Tabs INNER JOIN Portals ON Tabs.PortalId=Portals.PortalId
WHERE (TabId=[TabId] and PortalAlias='p_default')
ORDER BY TabOrder

Then I need the other result sets:

SELECT TabName, AuthRoles, TabId, TabOrder FROM Tabs
WHERE PortalId = [PortalId] ORDER BY TabOrder

SELECT MobileTabName, AuthRoles, TabId, TabOrder FROM Tabs
WHERE PortalId = [PortalId] ORDER BY TabOrder

SELECT * FROM Modules INNER JOIN
ModuleDefinitions ON Modules.ModuleDefId = ModuleDefinitions.ModuleDefId
WHERE TabId = [TabId] ORDER BY ModuleOrder

This is how it is done in SQL SERVER 2000. Can this be done with MS Access or will I need to execute separate queries for each?

 

[sfreeman@co.mer]

From the link below:

"When developing an Access database using the desktop Microsoft Access application, you are able to create and store queries. These are usually referred to as "Stored Queries", similar to SQL Server's "Stored Procedures", but less powerful. They are only SQL statements, no more, no less; they cannot do what Stored Procedures do on SQL Server"

http://www.15seconds.com/issue/020919.htm

Access queries, while pretty flexible and powerful, simply cannot match SQL Server stored procdures. Simply because the stored procedrues can run a virtually unlimited number of SQL statements. Access can do union queries, but that is really no comparison.

Going from stored procedures to Access queries kinda sounds like going backwards to me... just my $.02 [or is that pence?]

 

[jspurlin]

Yes, it is going backwards. But some people I build websites for are interested in small personal websites or small business websites on a budget and Sql Server is overkill. I know, I know: for a business and extra $120.00 a year is nothing. But for a small personal website (at least pending it's growing popularity) Access is all they really need and I can knock $120.00 of the setup fee. If they ever need Access, I just switch a setting in my web.config file from "MSAccess" to "SqlClient" and that is "literally" all that needs to be done. I build and test everything in SQL Server first. Then I add Access, if applicable.

I went ahead and just created three extra stored queries to get the Settings for the page and for a small website with a relatively small work load, the performance hit is not significantly perceptible.

But what you said "is" worth a wooden nickle!

P.S. I read the article you linked to. I use ASP.NET so it would be interesting to see a stored querie's performance compared to asp 2.0. Things run fast.

Can I Use the EXEC statement in an Access stored query itself? That would be a big help.

 

[sfreeman@co.mer]

No, EXEC is not functional within the query. The query must be 'called' or executed externally, ie from a command button, opening a form or report, or via VBA.