My First stored procedures

[Powderfinger]

I have a very simple "contact" database that I am about to Upsize to SQL
Server 2005 from Access 2003. I plan to use the Upsizing wizard that is
included in Access, rather than SSMA unless anyone here has any objections.
I am going to leave it in .MDB rather than ADE.

The database, which i named CNSQL, has virtually one table of any
importance - dbo.tblContact . It has a primary key CID, which is the contact
number and also general contact-type fields, I won't list them all but
here's four: FIRST_NAME, LAST_NAME, ADDRESS, PHONE. The table has a few
hundred thousand records in it.

I need three things:

1) a select query (or whatever) that will return the entire table or all
just one record if a CID is specified.

2) an update query (or whatever) that will update every field in one record
based upon the CID.

3) an update query (or whatever) that will update just two fields field in
one record based upon the CID.

4) a query (or whatever) that will backup the database to a file.

I am going to stick with the Access that

There is just one form (of any importance) in the database, frmCONTACT. The
record source of the form is tblContactLocal , this is a one-record local
table I set up .I created local table when the decision was made to upgrade
to SQL Server 2005. It's been working in Access the last few months quite
well(I know what you're thinking, "Why is he upsizing at all? , that's a
long story)

When the user opens this form it will call query 1, to return the one record
from the server and store it in tblContactLocal. The form then opens with
the data in tblContactLocal. The user edits this data then when he/she
closes/saves it calls either Query 2 or Query 1, depending on whether the
user is a queen or worker bee(I have a VBA routine in the form to determine
this).

I decided to implement my four queries with stored procedures, rather than
pass-thru queries. . I thought that this would be the most eddicient way. I
did a few sample upsizes and everything looks ok, although I've never done
this before and I am scared as heck to put it into operations. Here they are
:

1)Store Procedure1

USE [CNSQL]
GO
/****** Object: StoredProcedure [dbo].[procContactSelect]
returns a sigle record if dblCID is not null, or the entire table if it is.
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procContactSelect]
@dblCID float = NULL
AS
SET NOCOUNT ON
IF @dblCID IS NULL
SELECT CID, FIRST_NAME, LAST_NAME, ADDRESS, PHONE FROM tblContact
ORDER BY CID
ELSE
SELECT CID, FIRST_NAME, LAST_NAME, ADDRESS, PHONE FROM tblContact
ORDER BY CID
FROM tblContact
WHERE CID = @dblCID
RETURN

2)Stored Procedure 2
USE [CNSQL]
GO
/****** Object: StoredProcedure [dbo].[procContactUpdateAll]
updates every field in the contact table based upon dblCID
Script Date: 04/06/2008 09:29:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procContactUpdateAll]
@dblCID float = NULL,
@strFIRST_NAME NVARCHAR(50) = NULL,
@strLAST_NAME NVARCHAR(60) = NULL,
@strADDRESS NVARCHAR(100) = NULL,
@strPHONE NVARCHAR(12) = NULL,
AS
/* SET NOCOUNT ON */
BEGIN
IF @dblCID IS NOT NULL
UPDATE tblContact SET PHONE = @strPHONE, FIRST_NAME = @strFIRST_NAME
,LAST_NAME = @strLAST_NAME , ADDRESS = @strADDRESS
WHERE CID = @dblCID
END
RETURN

Do you have any suggestions that would improve my first attempts at this
code? Better error checking and exception handling? Perhaps a reference
guide? How would I write the 4th query, I have not attempted it yet?

Any help would be appreciated.

Thanks

Jack

[unknown]

Unless you are using ADP /ADE, you cannot bind directly a form to a SP from
an MDB file with ODBC linked tables. To call the SP, you must either use a
passthrough query or an DAO or an ADO object. If I remember correctly, you
can bind a form to the recordset returned by a passthrough queri or from an
DAO or ADO object but such a form will be read-only.

If your plan for the upsizing is to keep a MDB file with ODBC linked tables;
then you should forget about using SPs or at least, rethink it twice. Also,
with ODBC linked tables, your original MDB frontend should work about the
same; with only minimal adaptation to make:

1- Add the adSeeChanges optional parameter here and there when making an
EXECUTE call (and Access will tell you exactly where when you will try
running the code.)

2- Change the order of Update if you want to add a new record to a recordset
and retrieve the new identity value.

3- Take a look to the following article if you want to increase performance
by using Views and have them updatable (useful if you want to bind them to a
form) : http://support.microsoft.com/kb/209123/en-us
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Powderfinger
I have a very simple "contact" database that I am about to Upsize to SQL
Server 2005 from Access 2003. I plan to use the Upsizing wizard that is
included in Access, rather than SSMA unless anyone here has any objections.
I am going to leave it in .MDB rather than ADE.
The database, which i named CNSQL, has virtually one table of any
importance - dbo.tblContact . It has a primary key CID, which is the contact
number and also general contact-type fields, I won't list them all but
here's four: FIRST_NAME, LAST_NAME, ADDRESS, PHONE. The table has a few
hundred thousand records in it.
1) a select query (or whatever) that will return the entire table or all
just one record if a CID is specified.
2) an update query (or whatever) that will update every field in one record
based upon the CID.
3) an update query (or whatever) that will update just two fields field in
one record based upon the CID.
4) a query (or whatever) that will backup the database to a file.
I am going to stick with the Access that
There is just one form (of any importance) in the database, frmCONTACT. The
record source of the form is tblContactLocal , this is a one-record local
table I set up .I created local table when the decision was made to upgrade
to SQL Server 2005. It's been working in Access the last few months quite
well(I know what you're thinking, "Why is he upsizing at all? , that's a
long story)
When the user opens this form it will call query 1, to return the one record
from the server and store it in tblContactLocal. The form then opens with
the data in tblContactLocal. The user edits this data then when he/she
closes/saves it calls either Query 2 or Query 1, depending on whether the
user is a queen or worker bee(I have a VBA routine in the form to determine
this).
I decided to implement my four queries with stored procedures, rather than
pass-thru queries. . I thought that this would be the most eddicient way.
I
did a few sample upsizes and everything looks ok, although I've never done
this before and I am scared as heck to put it into operations. Here they are
1)Store Procedure1
USE [CNSQL]
GO
/****** Object: StoredProcedure [dbo].[procContactSelect]
returns a sigle record if dblCID is not null, or the entire table if it is.
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procContactSelect]
@dblCID float = NULL
AS
SET NOCOUNT ON
SELECT CID, FIRST_NAME, LAST_NAME, ADDRESS, PHONE FROM tblContact
ORDER BY CID
ELSE
SELECT CID, FIRST_NAME, LAST_NAME, ADDRESS, PHONE FROM tblContact
ORDER BY CID
FROM tblContact
RETURN
2)Stored Procedure 2
USE [CNSQL]
GO
/****** Object: StoredProcedure [dbo].[procContactUpdateAll]
updates every field in the contact table based upon dblCID
Script Date: 04/06/2008 09:29:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procContactUpdateAll]
@dblCID float = NULL,
@strFIRST_NAME NVARCHAR(50) = NULL,
@strLAST_NAME NVARCHAR(60) = NULL,
@strADDRESS NVARCHAR(100) = NULL,
@strPHONE NVARCHAR(12) = NULL,
AS
/* SET NOCOUNT ON */
BEGIN
END
RETURN
Do you have any suggestions that would improve my first attempts at this
code? Better error checking and exception handling? Perhaps a reference
guide? How would I write the 4th query, I have not attempted it yet?
Any help would be appreciated.
Thanks
Jack

[Stefan Hoffmann]

hi Sylvain,

Post by unknown
If I remember correctly, you
can bind a form to the recordset returned by a passthrough queri or from an
DAO or ADO object but such a form will be read-only.

Passthroughs are read-only. DAO/ADO objects can be updateable depending
on the data source. SPs cannot be updateable.

This limitation can be solved using a view:
Use a UDF returning a table or a normal query to select the data and use
INSTEAD OF trigger to call SPs.

Post by unknown
If your plan for the upsizing is to keep a MDB file with ODBC linked tables;
then you should forget about using SPs or at least, rethink it twice.

You may use it as replacement for action queries (DELETE, INSERT,
UPDATE) as calling them as passthrough is not a big deal.
You may use it move data logic from Subs to SPs.

You

mfG
--> stefan <--

[unknown]

Post by Stefan Hoffmann
hi Sylvain,

If I remember correctly, you can bind a form to the recordset returned by
a passthrough queri or from an DAO or ADO object but such a form will be
read-only.

Passthroughs are read-only. DAO/ADO objects can be updateable depending on
the data source. SPs cannot be updateable.

I know that a DAO/ADO recordset can be updateable but can a form bound to a
recordset created by DAO or ADO and based on a SP be updateable? Also, like
you have said, there is also the possibility of using views and Instead of
trigger. There are also other possibilities like using unbound forms and
make all the updates with VBA code.

However, the real question here is if the OP don't want to deal with ADP/ADE
and instead keep the simplicity of using a MDB file with ODBC linked tables,
then it doesn't really make sense to try using SP for forms that should be
updateable. Like you have said, there are solutions but before going into
one of these ways, he should rethink twice about his strategy.

Post by Stefan Hoffmann
Use a UDF returning a table or a normal query to select the data and use
INSTEAD OF trigger to call SPs.

If your plan for the upsizing is to keep a MDB file with ODBC linked
tables; then you should forget about using SPs or at least, rethink it
twice.

You may use it as replacement for action queries (DELETE, INSERT, UPDATE)
as calling them as passthrough is not a big deal.
You may use it move data logic from Subs to SPs.
You
mfG
--> stefan <--

[Stefan Hoffmann]

hi Sylvain,

Post by unknown

Post by Stefan Hoffmann
Passthroughs are read-only. DAO/ADO objects can be updateable depending on
the data source. SPs cannot be updateable.

I know that a DAO/ADO recordset can be updateable but can a form bound to a
recordset created by DAO or ADO and based on a SP be updateable?

A form is only updateable, if the RecordSource is updateable. This does
not depend on the way the form is bound to data. So binding a form to a
DAO result set from a SP is _not_ updateable.

Post by unknown
Also, like
you have said, there is also the possibility of using views and Instead of
trigger.

I'm using views with INSTEAD OF triggers under Oracle quite a lot. This
makes it possible to use "simple" linked tables. Only larger updates or
long running pieces of code are stored as procedures on the server and
called with passthrough queries, which is the only way to run everything
in one session.

Post by unknown
There are also other possibilities like using unbound forms and
make all the updates with VBA code.

This is true. Its only drawback, I think, is that you then need to
handle some elementary logic in the frontend.

Post by unknown
Like you have said, there are solutions but before going into
one of these ways, he should rethink twice about his strategy.

This is quite correct.


mfG
--> stefan <--

[Brent Spaulding (datAdrenaline)]

Post by unknown
If I remember correctly, you

can bind a form to the recordset returned by a passthrough queri or from an
DAO or ADO object but such a form will be read-only. <<

If the DAO/ADO recordset is updatable, then binding the recordset to a form
doesn't change that ... well in A2000, it may .... I vaguely remember having
to change the connect string to included some more info ... something about
AccesConnection ... (CurrentProject.AccessConnection... )