running SQL Stored Procedures from Access

[gojohnnygogogogo]

Hello,
has anybody had any experience with running sp's from access ?

how would I run this :

spfinancestatementlink
with parameter equal to :

[forms]![test]![txttest1]

I would like to attach this to a report, is that possible ?

thank you for any advice / links.

 

[cmmrfrds]

Is the Access program an ADP or MDB? If it is an Access Project then it is relatively easy to use stored procedures, it is more difficult in a MDB but doable.

If ADP, then the SP can be the recordsource
Me.RecordSource = dbo.spfinancestatementlink parm1

The SP can be the record source under the data tab, also has a place to put the parameters.

Also, there are ways to run SP using the ADO Command Object.

Post back if you need specific help but explain your environment and exactly the requirements.

 

[gojohnnygogogogo]

hello,
The access program is an MDB.
I am using MS Access 2000, and SQL 7 for the table / sp's.

I wish to pass parameters from a form to the sp, and that sp will have the data needed for a report.

thank you/

 

[cmmrfrds]

Read up on pass through queries.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp

The stored procedure will need to be a pass-through query in an Access MDB. An mdb is not as easy to work with as an ADP when executing stored procedures.

The pass through query needs to be saved in the Procedures collection in the mdb. The pass through code will look something like
exec dbo.spfinancestatementlink parm

The query name for example qryspfinance can be the recordsource on a report.

There may be other ways in an mdb to change parameters, but the only way I know is to set up vba code to read the procedures collection and change the query every time you need to change the parameter. These are reasons why I stick with an ADP for sql server backends.

 

[gojohnnygogogogo]

I've never even heard of ADP.

thanks for the info and link I'll check it out.

 

[cmmrfrds]

An adp is a Microsoft Access Project and is available from Access 2000 and above. The connection to the sql server database is at the project level so there is no need to link sql server tables and ODBC can be completely bypassed. Also, all the Forms and Reports deal directly with sql server data sources. In addition, there is a direct interface to sql server stored procedures and views. You can create and maintain the stored procedures and views directly from Access.

Advantage:
(1) A stored procedure can be a data source on a Form or Report just like you would make a query a data source in an mdb.
(2) Interface to the data source does not need Jet or ODBC
(3) There are many other advantages but the first two are enough to make a difference.

Some confusions (at first look):
(1) There is no direct interface to building queries. A query, if needed, can be built in vba code and stored in the procedures collection. You should NOT use Access Queries since they should be stored procedures on the back end sql server so they are available to all the clients - also more efficient, faster, etc..
(2) A View is an sql server view and resides in sql server.