Creating my first stored procedure (1 Viewer)

Hi Guys

Up until now I have be through the exersize of writing an Access database then moving on to an Access front end and SQL Server back end.

So I have now written all my queries as Views, well most of my queries as views. I have been advised that some of these would benefit as stored procedures.

This has left me in limbo, as I understand what a stored procedure is, but have no idea how to convert my routine into one.

At the moment to get the result I need I have a set of 10 views that run, the last one being exported to Excel and opened.

As I have probably 5 or 6 of these, would someone be willing to step me through what I need to do and help me understand the process I am looking at.

Martin

Assuming ODBC or SQL Server Native Client (an ODBC driver).
It is good to create views.
Even connecting to a table vs a single table as a View might be worthwhile.
So, you are on the right track.

There is no clear-cut answer for converting vba funcitons over to functions. I have been searching for something too. Came up with a big empty void.
I can't find the excellent post somone put on this forum.
If a SQL View exist, the Linked Table is used to run an Access Query - the ODBC layer will translate the Access SQL into very efficient T-SQL over the ODBC interface boundry. The execution plan verifies that.
There will probably be very little gain.

Here is the big IF
If the Access query (in the Form or SQL view) is pure SQL, it can be translated.
However, if the Query contains any Access or VBA functions - then it won't be translated efficiently.

Take the case of a View connected as a linked-table - MyCustomers
From an Access Linked Table - Slect * From MyCustomers Where ST in('OK', 'TN')
There won't be hardly any gain noticed.
But now, do the same using an Access or VBA string function, huge difference.

Translating a VBA Function into a T-SQL function is not straight-forward.
The old saying "it depends" rings true.

If the Access query using a linked table has filters, the old rumor that all of the data is pulled across the network, then filtered on the Access workstation is false.

The solution depends on your actual situation. In a few cases, I create a pure SQL statement, bring back a couple of hundred records to a temp table, then use Access functions or VBA on the temp table.


Read this first:
http://www.access-programmers.co.uk/forums/showthread.php?t=221831&highlight=procedure

http://www.access-programmers.co.uk/forums/showthread.php?t=248981&highlight=procedure

http://www.access-programmers.co.uk/forums/showthread.php?t=249221&highlight=procedure

http://www.access-programmers.co.uk/forums/showthread.php?t=248361&highlight=procedure

Here is a post on the NUBIE site for SQL Server.
http://www.sqlservercentral.com/Forums/Topic1589135-1292-1.aspx
With these guys, my experience indictes it is very important to be very specific.
There are so many ways to accomplish the same thing on T-SQL.

Stored procedures can accept parameters but Views can't.

Unlike Views, a Stored Procedure cannot be linked to Access for use as a Recordsource. However an SP can return an ADODB recordset which can be used as the Recordset property of a form.

Since you are putting your results into Excel this would be a good solution. Generate the recordset in an SP then use the CopyFromRecordset method in Excel to put the results into the spreadsheet. Access doesn't even need to be involved.