Sample Stored Procedures (ADP)

[ChristinaB]

Does anyone know of a good place to get a couple of sample stored procedures for a .adp project in Access? Or maybe someone can just help with this question....

I know how to pull data out for single table:
ALTER PROCEDURE ArchiveLog
AS SELECT PROD_NAME, ARCHIVE_TS
ORDER BY PROD_NAME

but I need to make a query where, to simplify the problem, I've got the tables
DM_LABEL
L_DATA
V_DATA
PRODUCT
SG_DATA
and others
I need to get the VALUE column from V_DATA if the LABEL_ID = the LABEL_ID from L_DATA and if the NAME in L_DATA is = "Operator" and so on and so forth.

The database that I"m accessing is basically just a maze of these little tables that need to be accessed multiple times in order to get info like Lot number, Operator, Audit time, etc. The same thing needs to be repeated for values in other tables based on being linked to that particular audit.

The whole thing needs to be run based on a parameter provided by the user, so they could say that they only wanted products with part number 587 and lot number 32.

I'd love a shove in the right direction.

[Jim Doherty]

Does anyone know of a good place to get a couple of sample stored procedures for a .adp project in Access? Or maybe someone can just help with this question....

I know how to pull data out for single table:
ALTER PROCEDURE ArchiveLog
AS SELECT PROD_NAME, ARCHIVE_TS
ORDER BY PROD_NAME

but I need to make a query where, to simplify the problem, I've got the tables
DM_LABEL
L_DATA
V_DATA
PRODUCT
SG_DATA
and others
I need to get the VALUE column from V_DATA if the LABEL_ID = the LABEL_ID from L_DATA and if the NAME in L_DATA is = "Operator" and so on and so forth.

The database that I"m accessing is basically just a maze of these little tables that need to be accessed multiple times in order to get info like Lot number, Operator, Audit time, etc. The same thing needs to be repeated for values in other tables based on being linked to that particular audit.

The whole thing needs to be run based on a parameter provided by the user, so they could say that they only wanted products with part number 587 and lot number 32.

I'd love a shove in the right direction.

Nice to see you working on Stored procedures Christina....

What I am going to relate to you now concerns bound forms ok? that is to say where your strategy is to bind the form to a recordsource and let access do the work navigating records and so on rather than you controlling it via ADO recordsets in code. Its the easiest to start with, without having to understand the syntax for ADO coding as well......infor mation overload.... you can always do that later once you get the form interaction methodology of using an ADP form with a stored procedure.

Stored procedures accept parameters and the body of the procedure itself accepts the value of the parameter passed to it and responds accordingly a simple example of this is can be a basic select:

CREATE PROCEDURE dbo.usp_ GetMyLabel
(@label varchar(100))     -- <<<<<<this is the parameter passed in with datatype defined
AS   --<<<< this AS word is part of the procedure
-- and then the select statement using the parameter as part of the WHERE clause
SELECT [VALUE] FROM vdata where [VALUE]=@label

GO

This returns to you a dataset ie a one column one row ([Value])
containing the values '12345' pre-supposing of course the table has the data.

So how did you 'pass' the parameter to the stored procedure from within your access form?.

Well lets say your form was purpose built to 'always' require that value as a dataset. The value would be passed as part of the FORMS InputParameters property (you see this by selecting View properties for the form its at the bottom of the dialog list.)

When the form opens it looks for its record source which, in this case would be the name of the stored procedure itself. It in turn looks to the inputparameter box if you like for the parameter.

Tip: (a must have) always use the 'owner' prefix (dbo) when using stored procedures or any view from an Access ADP this is very relevant if in a multi users environment particularly where users can create their own objects on the server from the client side.

So then the recordsource for the form as I said would be dbo.usp_GetMyLa bel. The PARAMETERS for this stored procedure are typed into the InputParameters like this @label='12345'

All very well and good but not a good example, maybe so, but it is the barest in order to quickly understand the principle because from here you then build on that like so:

CREATE PROCEDURE dbo.usp_ GetMyLabel
 (@namebeingpassedin-- <<<<<<this time the parameter passed in is variable parameter seeking name as you required
AS   --<<<< this AS word is part of the procedure
-- and here is the SQL statement
SELECT  TOP 100 PERCENT [VALUE] as MyValue FROM dbo.vdata
INNER JOIN dbo.L_DATA on dbo.vdata.LABEL_ID=dbo.L_DATA.LABEL_ID
WHERE [NAME]=@namebeingpassedin
ORDER BY v.data.[VALUE]
GO

(Notice in the above syntax how I have changed the NAME value of the field to alias it as MyValue. Watch out for using reserved words and so on for field naming etc)

The above stored procedure (sproc as they become known) answers basically this bit of your question

I need to get the VALUE column from V_DATA if the LABEL_ID = the LABEL_ID from L_DATA and if the NAME in L_DATA is = "Operator"

Q) So how did the actual word 'Operator' get passed to the procedure?
A) It was passed in as a variable via the inputparameters section of the form properties

Q) How did we get the form to do this?
A) Create a continuous form with one textbox on it set the forms recordsource to dbo.usp_GetMyLa bel. In Form design view type into the InputParameters property for the form this

@namebeingpasse din=[Enter the name to pass in]

This will bring you up a dialog prompt box (When you reopen the form) where you can physically type 'Operator' in alternatively you could use this

@namebeingpasse din=Forms!MyFor mName!MyListbox

where the parameter value would then being sought on open from a listbox value mounted on your form if you wanted to do it that way, or it could be a textbox or option group value and so on.

So in short.... you do not need a mirad of stored procedures to achieve everything you want to achieve (my advice is to resist creating too many stored procedures where the only difference is a change in criteria) just identify the tables you wish to work with and create the SQL syntax to suit the circumstances passing into the stored procedure the WHERE clause argument value which on execution of the SQL will return the prescribed dataset to your form

I hope this helps you

Jim

[ChristinaB]

Ok. I think I've got a lot to chew on for a while. Thanks, Jim!

[Jim Doherty]

Ok. I think I've got a lot to chew on for a while. Thanks, Jim!

As a PS I am on vacation as of today (off to sunny cyprus) so if you don't get a resolution you can PM me and I'll continue on return next week?

Jim