Using SQL and Stored Procedures (1 Viewer)

[liddlem]

Hi All
I am trying to get my head around using stored procedures in SQL Server
On my form, I have a button that SHOULD add students from a specified formid(which is a combobox)

The following is the underlying code.

Private Sub Btn_Add_Studs_By_Form_Click()
Dim qdef As DAO.QueryDef
Dim err_msg As String
Dim err As DAO.Error
On Error GoTo err_lbl

'In the front end, I have a linked Pass Though Query called "PTQ" which is modified as follows. 

Set qdef = CurrentDb.QueryDefs("PTQ")
qdef.SQL = "EXEC SPI_ACT_ACTIVITYDTL " & Me.ID_ACT_ActivityHdr & ", '" & Trim(Me.Add_Form) & "'"

'Although the line above returns what seems to be the correct call with correct parameter info, 
'the following line returns a 3065 error (Cannot execute select query)
qdef.Execute dbFailOnError
end sub

My 'SPI_ACT_ACTIVITYDTL' Stored Procedure on the sql server is defined as :

USE [Demo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SPI_ACT_ACTIVITYDTL] @ID_ACT_ActivityHdr integer, @FMName nvarchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO dbo.ACT_ActivityDtl ( ID_Student, FmName, ID_ACT_ActivityHdr, ID_staff, ID_form )
SELECT ID_Student, FORM_Name, @ID_ACT_ActivityHdr, 0 AS Staff, ID_FORMID
FROM dbo.STU_Student
WHERE (((dbo.STU_Student.FORM_Name)=@FMName));
END
GO

As you can see, the SP is trying to INSERT data that is sourced from a SELECT query.
If I can only make calls to an ACTON query that does not contain a SELECT statement in it, then how can i make the SP work and then as a consequence of not being able to put SELECTS in my SP's, is there any point to using an sp?

Thanks

 

[Ranman256]

if you have to do large mass updates, then the SP works for you.
I dont think theres any gain on small sets

 

[pbaldy]

You might check the Returns Records property of the pass through query.

 

[liddlem]

Hi Pbaldy - Thanks for the suggestion, but I don't know how to return the records for an SP. can you point me in the right direction please.

 

[pbaldy]

I'm talking about a property of the saved query "PTQ".

 

[Galaxiom]

Does the procedure work when you run it on the server?

 

[Mark_]

Possible issue, is FMName unique within the database? Not sure where the error is coming form elsewhere, but if there is an ambiguous reference for which record your trying to select that can cause other issues.

 

[Pat Hartman]

Access is a Rapid Application Development Tool. The whole point is to use bound forms and let Access do the bulk of the work for you. When you use unbound forms, you loose the biggest benefit of Access and are only left with the baggage.

 

[liddlem]

Hi All - Thanks for the input.
Let me try answer each of your comments in order.

Ranman256 : For me, this exercise is about 2 things.
1. Learning how to use SP's (and functions) on a SQL server. (I presume that if/when I convert my front end to a web platform, that I will need this skill?)
2. Reduce network traffic where possible. (see my comments to Pat Harmon below).

JUST A THOUGHT HERE Could / should I be using a UDF instead? Can I perform an action query inside a UDF?


PBaldy : If I edit the 'PTQ', it has changed to read ... EXEC SPI_ACT_ACTIVITYDTL 396, 'MS78A'
Which is what I expected.

Galaxiom : If I 'simulate' the SP (IE run the following in a NEW QUERY on the SQL server) it works.

declare @ID_ACT_ActivityHdr int
declare @FMName varchar(15)
set @FMName = 'MS9A'
set @ID_ACT_AtivityHdr = 326
SELECT ID_Student, FORM_Name, @ID_ACT_ActivityHdr, 0 AS Staff, ID_FORMID
FROM dbo.STU_Student
WHERE (((dbo.STU_Student.FORM_Name)=@FMName));

Mark_ : I have tried changing the name of the variable to a sequence of random letter (so no duplicate name)...makes no difference.

Pat Harmon : Okaaay?...My understanding is that SP's perform some action on the server side, whereas a [front end] query 'downloads' ALL the underlying data from the server before performing some action.
The output of which is then returned back to the server and then updated accordingly.
Surely this implies that there is a lot of unnecessary traffic on the network?

 

[Mark_]

If your code works when you give it an explicit FMName, where is your stored procedure getting this value from? Same with ID_ACT_ActivityHdr.

Regarding bandwidth, are you looking to add a student, several students, a dozen students, or tens of thousands of students with a single press? For the number of students normally found in a class you'd see no difference in server or bandwidth utilization if you did this from the front end.

Stored procedures are normally used to accomplish that which cannot be easily done from the front end. having the server update half a million records is one use. Having the server sift through several million to return a subset of 12 is a perfect use.

 

[Galaxiom]

Pat Harmon : Okaaay?...My understanding is that SP's perform some action on the server side, whereas a [front end] query 'downloads' ALL the underlying data from the server before performing some action.

That is a common misunderstanding. If the query can be translated via the ODBC interface it will run on the server.

Including local tables and user defined functions are the most common reasons why the records would need to be brought down.

If the UDF return value is required to select the records on the server then the overheads become hideous because all the records need to be processed on the front end.

 

[pbaldy]

Liddell: you still haven't addressed my question. Your pass through query has properties, one of which is Returns Records. Yours should be No, since it will not return records. I suspect it may be Yes, leading Access to believe it's a SELECT query and give you that error.

 

[Pat Hartman]

My understanding is that SP's perform some action on the server side, whereas a [front end] query 'downloads' ALL the underlying data from the server before performing some action.
The output of which is then returned back to the server and then updated accordingly.
Surely this implies that there is a lot of unnecessary traffic on the network?

Galaxiom already addressed this. It is false as a generality since Access makes every effort to "pass through" every query. You do however need to understand something about how this works so that you do not create a query that cannot be "passed through" . There are articles out there on creating Access apps using good client/server techniques. It isn't very hard. Access uses VBA and SQL server doesn't. That's a start. If your query includes a VBA function, it could interfere with Access' ability to pass through the query. Usually VBA functions in the select clause don't cause a problem since Access can send the query to the server and apply the VBA function on the resultset. But, using VBA functions in other clauses usually requires them to be executed by the query engine itself. In that case, Access sends what it can to the server and performs the rest of the query including potentially the joins locally.

The other important concept is how forms are used. Old Access apps always bound forms to tables (and many people still do that) and then allow the user to filter the recordset locally. Better technique is to bind forms to a query and the query should select only the necessary columns and include a WHERE clause to reduce the records retrieved ideally to one.

Converting an old style Access app to SQL server invariably results in slower rather than faster performance due to the above two problems. But if you design the app with an understanding of how client/server actually works, you will be fine using bound forms.

I've been creating Access apps since the early 90's and I adopted Access because I discovered I could link to DB2 tables on the mainframe and actually update them. So, the bulk of my apps have used an RDBMS BE. Usually SQL Server but also DB2, Oracle, Sybase, and several other lesser known RDBMS. I generally create the app linked to ACE and when the schema is firmed up, convert to whatever RDBMS I am going to use. I code for the RDBMS and so I even have a couple apps that are sold to the public that can hot-swap between ACE and SQL Server at the client's choice.

Over the years I have had to create a few views to facilitate complex queries, a couple of pass-through queries to do bulk deletes, and a couple of sproc's for very complex reports but all in all, linked tables and bound forms are perfectly serviceable and significantly more cost effective to create and maintain.

Keep in mind that the vast majority of the bad press directed at Access is actually directed at Jet and ACE. People don't seem to be able to distinguish between the two. The confusion stems from two facts.
1. Access requires Jet or ACE to store its own objects - forms/reports/queries/macros/code. Do not confuse this with DATA. Access is named "Access" because of its ability to access ANY data database that offers ODBC and it has since day 1.
2. Jet and ACE do not depend on Access for anything however, they in themselves do not offer a GUI so Access is used as the GUI to visually create Tables and other objects. However DAO and ADO are sufficient if you don't need a GUI

Access is a RAD tool used to develop client/server applications. It can link to ANY RDBMS that supports ODBC. It is infinitely scalable. The number of concurrent users and the amount of data stored are completely dependent on the RDBMS. So, if you have a thousand CALs for SQL Server, you can have a thousand concurrent users. This is where the limitation issues come into play. The people who don't know any better always say "Access" can only hold 2gig and "Access" can only support a small number of concurrent users. When in reality those limitations are for Jet and ACE - NOT Access.

Access is not viable as a front end to web data. It can be done, but it is not optimized for it and unless you have a seriously high end and well tuned Azure database, you will be very disappointed. This is a case where you might actually have to resort to unbound forms. But, then I would never use Access anyway. If you can't take advantage of it's RAD tools, there is no point to having to deal with the baggage.