View The Output of a SQL Server Stored Procedure in Access

I ll like to view the output of a SQL stored procedure in an access subform. Say the output of the SQL Stored proc was;
select * from TableA
I ll like my record source of the subform to be that output. How do i do this
call a sql stored procedure in Access
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Bind the subform to a pass-through query.  

PS - I think the sp must have an argument since Access won't sync subforms bound to pass-through queries.  Also, the recordset will not be updateable.
I'd like to view the output of a SQL stored procedure in an access subform.
The output is of little consequence.  It will be a recordset
The name of the SQL Server stored procedure is the important thing
I'd like my record source of the subform to be that output.
Sure, you can bind it just like a regular query -- but it is read-only

How do I do this,  call a sql stored procedure in Access?
You create a new query in design view
Right-click.  Choose Properties.  Leave the properties window open
Choose the 'query type' menu/ribbon option
Then 'SQL Specific' | Pass-through
In the properties window you can paste in a valid ODBC connection string OR
you may be prompted to supply one when first viewing your query.
Getting the connection string right and saved can be fun.
Once I got my first one working, I copy it and change the name and SQL to avoid the hassle :)

Write the valid T-SQL to call your stored procedure
Here are some examples from my production

exec qryFRClientID
exec spSaveChecks  90079
Exec spbreakdownbyinspector '01-Sep-14' ,'30-Sep-14 11:59 PM' ,3
Exec SpInspectorRevenueSummary '13-Aug-14 12:00 AM' ,'19-Aug-14 11:59 PM', 1

The format is
exec NameOfTheSproc [option comma-delimited list of parameters with valid T-SQL formatting -- % for wildcard, string-literal dates, strings enclosed in single parentheses if the sproc requires them]

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
peterslove53Author Commented:
thank you Nick67, what if i want to pass the parameter to the procedure dynamically at run time.
I do that all the time.
Build the valid T-SQL string in VBA
fire up a querydef of the passthrough
change the .SQL property
Go to town

Dim db As Database
Dim QDF As QueryDef
Dim rs As Recordset
Dim SQL As String
Dim myJobID as Long

Set db = CurrentDb
Set QDF = db.QueryDefs("pthrMyInspectors")
myJobID = Forms!frmJobID!JobID
SQL = "exec spMyInspectors  " & myJobID
Set rs = QDF.OpenRecordset(dbOpenDynaset, dbSeeChanges)

'go to town.
'At this point the passthrough has been permanently altered, so if it is bound to a form/object, requerying will refresh the data
Generally, I use DAO for things.
ADO in certain circumstances can have a clear performance benefit.
You can bind objects to ADO recordsets drawn from stored procedures too will syntax like this

Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
dbCon.ConnectionString = Forms!tblDummy!ADOConnString.Value 'I store the connection string in table. This control has that string
dbCon.CursorLocation = adUseClient
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "QryFREquipType"
    .Parameters.Append .CreateParameter("@GeneralEquipTypeID", adInteger, adParamInput, , 23)
    .ActiveConnection = dbCon
    .NamedParameters = True
    Set rst = .Execute
End With

'bind a combobox to it as a test
Set Me.Combo24.Recordset = rst
'done with ADO, clean up
Set dbCon = Nothing
Set cmd = Nothing
Set rst = Nothing

Open in new window

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 SQL Server 2008

From novice to tech pro — start learning today.