Executing A Stored Procedure From An Access Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an Access 2003 Database (NOT a project - ADP). The tables are linked
to SQL Server using ODBC.

My question is this:

Is there a way to call a SQL Server Stored Procedure as the RecordSource for
a Form or Report by wrapping it into an Access Query?

If it is possible, can someone provide me with a step by step response on
how I could achieve this?

I do not want to know how to do it using ADO in code since I already know
how to do that. I want to know if there is a way to call it from a query that
can be used as the RecordSource for a Form or Report.
 
Andrew said:
Hi,

I have an Access 2003 Database (NOT a project - ADP). The tables are
linked to SQL Server using ODBC.

My question is this:

Is there a way to call a SQL Server Stored Procedure as the
RecordSource for a Form or Report by wrapping it into an Access Query?

If it is possible, can someone provide me with a step by step
response on how I could achieve this?

I do not want to know how to do it using ADO in code since I already
know how to do that. I want to know if there is a way to call it from
a query that can be used as the RecordSource for a Form or Report.
Click to expand...

Create a Pass-Through query and in the SQL of that query execute the Store
Procedure with the same statement that you would use in SQL Server's Query
Analyzer. To pass parameters this usually means modifying that SQL on the
fly.

Dim db as Database
Set db = CurrentDB
db.QueryDefs("QueryName").SQL = "EXEC MyProc @Param = SomeValue"
db.Execute "QueryName", dbFailOnError
 
If to follow Rick steps, after assigning a string to the Pass-Through query
that you created, you can use this query as the recordsource for a report or
form.
You can't edit the records in this form.

Dim db as Database
Set db = CurrentDB
db.QueryDefs("QueryName").SQL = "EXEC MyProc @Param = SomeValue"
me.recordsource = "Select * From QueryName"

Another way is, after assigning the string to the Pass-Through query you
created, then you can open a recordset based on this query, and then assign
the values to the form fields.
 
In the properties of the query you created, there is an ODBC connection,
press on the button on the left and select the SQL Server database.
It will save it, and it wont prompt you with the message to which Db you
want to run.
 
Back
Top