MS Access/SQL Server -- execute Stored Proc and view selection

I want to find a nice solution to execute a stored proc from MS access and then view the results.  

I have a nice function to execute the proc with input variables.  What I'm not sure of is how to display the results, which will be a selection set returned.

I have done a LOT of this using Excel, where i have a whole system the pulls the selection set into an ADO Recordset and pulls it into a data sheet and formats it, etc.

In MS Access i would LOVE to have something like an openQuery statement to open up a sheet of the results.

Has anybody done this?

Here is my function to run the Proc.  It includes a function to open the connection... but the rest is in this function

    Dim iNumParams As Integer
    Dim sSQL As String
    Dim oCmd1 As New ADODB.Command
    Dim oParam1 As ADODB.Parameter
    Dim oParam2 As ADODB.Parameter
    Dim oParam3 As ADODB.Parameter
    Dim oParam4 As ADODB.Parameter
    Dim oParam5 As ADODB.Parameter
    runProc = False
    If sInput1 = "" Then
        iNumParams = 0
    ElseIf sInput2 = "" Then
        iNumParams = 1
    ElseIf sInput3 = "" Then
        iNumParams = 2
    ElseIf sInput4 = "" Then
        iNumParams = 3
    ElseIf sInput5 = "" Then
        iNumParams = 4
        iNumParams = 5
    End If
    Debug.Print Str(iNumParams) & "param1: " & sInput1 & "| param2: " & sInput2 & "| param3: " & sInput3
    sSP = sProc
    If sMetric = "" Then sMetric = "NO-COMPS"
    If Not OpenSQLConn() Then GoTo ExitMe
     With oCmd1
            .ActiveConnection = goConn ' Reference to a Connection object.
            .CommandType = adCmdStoredProc
            .CommandText = sProc
            .CommandTimeout = 0
            If iNumParams > 0 Then
                If iNumParams >= 1 Then
                    Set oParam1 = .CreateParameter("@input1", adVarChar, adParamInput, 20)
                    .Parameters.Append oParam1
                    oParam1 = sInput1
                End If
                If iNumParams >= 2 Then
                    Set oParam2 = .CreateParameter("@input2", adVarChar, adParamInput, 20)
                    .Parameters.Append oParam2
                    oParam2 = sInput2
                End If
                If iNumParams >= 3 Then
                    Set oParam3 = .CreateParameter("@input3", adVarChar, adParamInput, 20)
                    .Parameters.Append oParam3
                    oParam3 = sInput3
                End If
                If iNumParams >= 4 Then
                    Set oParam4 = .CreateParameter("@input4", adVarChar, adParamInput, 20)
                    .Parameters.Append oParam4
                    oParam4 = sInput4
                End If
                If iNumParams >= 5 Then
                    Set oParam5 = .CreateParameter("@input5", adVarChar, adParamInput, 20)
                    .Parameters.Append oParam5
                    oParam5 = sInput5
                End If
            End If
        End With
    runProc = True
    Call CloseSQLConn
Set oCmd1 = Nothing
Set oParam1 = Nothing
Set oParam2 = Nothing
Set oParam3 = Nothing
Set oParam4 = Nothing
Set oParam5 = Nothing

End Function

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I think you will find this 3 part series of interest as it discusses this exact topic:
SQL Server Stored Procedure Guide for Microsoft Access

"ADODB and Stored Procedures – The preferred Way" is in part2
Dale BurrellDirectorCommented:
Why have you marked the question as SQL Server?
dougfosterNYCAuthor Commented:
thanks Portlet Paul.

I read through it and am still confused.  I don't have a problem with triggering the proc.  the article may be telling me, but I still don't see how to take a selection from the proc and displaying it like a table.  

The article used a dashboard with a table variable.  I want to display large tables so a variable is not desirable, and I still don't see the ACCESS side of how to display the results.

Do you have something that shows how to take the selection and show it in a tab like a table or query?

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dougfosterNYCAuthor Commented:
i marked it as MS Access primary and SQL Server secondary.

you're right, it is primarily an Access question but it does involve heavy use of SQL (stored procs) so i thought it was appropriate since the solution involves expertise in both.
Afraid not, my particular (and not necessarily shared by many) viewpoint is that stored procs aren't necessarily the best way - but I don't know what your proc is doing.

For many of the things I need to do Views or table functions serve me better than procs (when wanting a resultset) - obviously procs are best practice for DML.

The other salient fact here is I haven't personally used Access in a long while so I'm rusty on it. I quickly ran through that reference and thought it may assist - but at least we know it doesn't - sorry if it wasted your time though.

Hopefully the more Access oriented experts will join in with much more than I can offer.
dougfosterNYCAuthor Commented:

I'm a heavy proc developer, so perhaps I'm biased... but this is an app that i am only using Access as the front end temporarily, and knowing it will be migrated to another interface.

Procs have many advantages, but all the logic is server side so we can build the web UI later and all the heavy lifting will be the same, done by the procs.  So this development is independent of the UI.

Plus, there is a lot of data there and SQL Server procs are super fast/optimized.   Access is a DOG trying to do the same thing.  

those are my two cents....thanks...
Oh, on the topic point, I'm not sure SQL Server really is relevant for an answer - it attracted my abortive attempt as an example of that. I could remove that topic - but we don't quite know how this will pan out so I'd suggest leaving it 'as is' for now.
Dale BurrellDirectorCommented:
SQL Server and Access are actually quite different unfortunately, and tagging it as SQL Server means a number of us look then realise we can't help. Tagging SQL Syntax would be slightly more accurate, although MS Access isn't ANSI SQL either... so really its an MS Access only question.

I fully endorse the use of SP's though... in the right circumstances.

Do you really need to keep using MS Access? As you say its a dog... SQL Server Express is free and a *proper* database :)
dougfosterNYCAuthor Commented:
ok, i'll take sql server off.   Funny, i'm primarily a sql server developer, but i'm using Access as a front end in this situation, a "cheap" way to provide an interface to my client while I'm building out all the heavy lifting server side using SPs.

The end state is to have the front end by a web interface, but for now, i am providing a quick-and-dirty UI.

Thanks for your input.
dougfosterNYCAuthor Commented:
argh.. it looks like i can't edit the question now.. I hope this gets resolved quickly by somebody who knows Access.

thanks for your patience.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could return the results of your SP into an ADO Recordset, then create a form based on that Recordset:

Dim rst As New ADODB.Recordset
Set rst = YourConnection.Execute "EXEC YourSP 111, 'Bob', '01-01-2013'"

Then set the Form's Recordset property:

Forms("YourForm").Recordset = rst

Of course you'd have to create the form with controls bound to the returned values of the recordset.

Another method is to use a pass-through query, and then bind a form to that query. Pass-through queries are just like regular queries, expect you set the Connection property to hook up with your SQL database, and you can't use Access-specific syntax (like calling a local function in the query, or using Nz).

I personally think the pass through is easier, but some prefer the ADO Recordset method. If you do this, note that you must set the Cursor for the Recordset to a ClientSide cursor.

See this article for more information on creating Pass Through queries:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougfosterNYCAuthor Commented:
Excellent help
dougfosterNYCAuthor Commented:
I ended up doing a separate solution to save to a table, but it looks like these are my only options in Access.

Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.