SPs vs. Views

[el zorro]

I want to learn more about the difference between a stored procedure and
a view. I have an adp front end and a SQL back end, which was converted from
an Access darabase. I've been doing most of my data selection-type work using
views because views seemed to be the closest thing to the queries I had been
working with in Acccess. But there seems to be limits to using Views.

Stored procedures and views *appear* to do about the same
thing, but maybe stored procedures allow more flexibility. In some cases I
have resorted to putting an entire SELECT statement in the VBA code for an
event to populate a list box on a form because I couldnt figure out how to
get the code to just grab a View and use it. If that makes any sense...

Can you give me some insight on this, or direct me to a good reference on
the topic of SPs vs. Views?

Thanks mucho!

 

[Sylvain Lafontaine]

Essentially, only a Stored Procedure (SP) can retrieve data. View cannot
retrieve data: first, you (or Access) must enclose them in a select query:
Select * from MyView; this is a big difference from the queries that you had
been working with in Access.

Second, even when you are using a Select statement to retrieve data from
SQL-Server; you are accessing a SP: the statement is passed as a parameter
to a SP procedure that parse it, run it and send you back the data via a
resultset.

A Resultset is what SQL-Server is sending you back over the wire. When this
resultset is grabbed by either DAO or ADO, it is transformed into a
DAO.Recordset or an ADO.Recordset so that you can access the data inside it.
A SP can also send back multiple resultsets from a single call.

When accessing the SQL-Server from Access, you should use SP as a first
choice because even when you are using a full SQL statement such as Select *
From MyView, you end up calling a SP on the server to run it.

This explanation is a simplification, I won't enter into the details.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)