ADP Best Practices

[Ade F]

I wondered if users here have a certain workflow when it comes to deploying and updating adp projects.

I am considering making a project using access 2010 and sql express r2 2008. I am used to using access with jet projects on a split front end / backend format.

I am not however familiar with how you go about maintaining an adp project.

Let's say for instance I have a production version running and a client is happy. The queries and stored procedures etc are all working well and time moves on. The client then requests additional features that require extra queries / forms / reports etc. My question would be what / how do you go about safely importing the new stored procedures / views into the already deployed sql back end.

Is it a case that people don't use stored procedures and stick only to using queries that only live in the access front end this way you can literally just push out new front ends to user pc's and everything is good in the world of the client.

Having not ever done this sort of thing I wondered if someone could offer words of wisdom for the do' and don nots etc when trying to maintain an access adp front / sql express / standard back end. I wondered if there were tried and tested housekeeping methods for making the updating procedure of the database smooth in terms of not upsetting the apple cart.

I appreciate that there has always been murmurs of deprecating the adp side of things in the office sweet. From reading around though it appears a lot people still ue adp's for a more robust solution.

Any feedback or questions would be appreciated.

 

[mdlueck]

The client/server application I have built for a client uses A2007 on the client side / SQL Server 2008 R2 on the server side. I am using the .accdb database file format.

When promoting up a new version of the application from the Dev environment to Prod, I perform the following:

1) Update all BE DB schema. I keep track of the DDL via separate DDL scripts developed in SQL Server Management Studio.

I have the naming standard that Stored Procedure scripts include two periods in the filenames and tables have only one period. Thus I may automate the re-drive of all Stored Procedure scripts simply by re-executing *.*.sql scripts I do a file system scan for. So I would have first updated the table schema as needed, then redrive all of the SP scripts. That has the server all set.

2) I run through the following each time I publish a new FE DB:

VBA to Cleanup A2007 DB Extra Objects
http://www.access-programmers.co.uk/forums/showthread.php?t=226466

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

3) Once the FE DB is all cleaned up, I copy the new FE DB into the directory Software Distribution (SD) checks for the master copy of the file.

4) Run the icon and test the new build. SD takes care of making sure the client computers have the correct version of all client-side files.

 

[Ade F]

Many thanks for the response Michael. There are keywords here that I am not familiar with. I'm wet behind the ears when it comes to sql daily maintenance and scripting. When you say re-drive I presume you mean moving the Dev Stored procedures from the Dev Back end to the Prod Back end?.

This is after all what I am trying to achieve. A consistent way of syncing or regimenting a workflow to get the newly modified or created SP / views or whatever from the Dev Fe/Be to the Prod FE/BE.

You also mention maintenance scripts; is this something you could point me in the right direction of google wise or offer any help towards giving me a hint?.

Creating at least some form of foundation in terms of versioning and deployment would be great. I feel I have a long way to go in terms getting there but hopefully perseverance will get me there. Thank you for your Christian attitude and taking the time to respond.

Best Regards

Adrian

 

[mdlueck]

By SQL Scripts, I merely mean saving each SP into its own SQL script which creates/recreates it. Template here:

/************************************************************************************/
/* FILENAME       :  aoe.clsObjAOETbl_Insert.sql                                    */
/* TYPE           :  SQL                                                            */
/* DESCRIPTION    :  Stored Procedure to insert a record into the aoe table         */
/*                                                                                  */
/* AUTHOR         :  Michael D Lueck                                                */
/*                   mlueck@lueckdatasystems.com                                    */
/*                                                                                  */
/* NEEDS          :                                                                 */
/*                                                                                  */
/* USEAGE         :  authid                                                         */
/*                   productid                                                      */
/*                   cpcmplstatusid                                                 */
/*                   dcscmplstatusid                                                */
/*                   cpnum                                                          */
/*                   cpnotes                                                        */
/*                   dcsnum                                                         */
/*                   dcsnotes                                                       */
/*                                                                                  */
/* RETURNS        :  id                                                             */
/*                                                                                  */
/* REVISION HISTORY                                                                 */
/*                                                                                  */
/* DATE       REVISED BY DESCRIPTION OF CHANGE                                      */
/* ---------- ---------- -------------------------------------------------------    */
/* 07/18/2012 MDL        Initial Creation                                           */
/************************************************************************************/

-- Define name of STORED PROCEDURE for this script
:setvar SPNAME clsObjAOETbl_Insert
USE [Fandango]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Check if the STORED PROCEDURE exists already, DROP if found
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [type_desc] = 'SQL_STORED_PROCEDURE' AND [name] = '$(SPNAME)')
  DROP PROCEDURE [dbo].[$(SPNAME)]
GO
CREATE PROCEDURE [dbo].[$(SPNAME)] (
  -- Add the parameters for the stored procedure here
  @authid AS smallint,
  @productid AS smallint,
  @cpcmplstatusid AS smallint,
  @dcscmplstatusid AS smallint,
  @cpnum AS varchar(50) = NULL,
  @cpnotes AS varchar(8000) = NULL,
  @dcsnum AS varchar(50) = NULL,
  @dcsnotes AS varchar(8000) = NULL
  )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @id smallint

BEGIN TRAN
INSERT INTO [dbo].[aoe] (authid,logtimestamp,productid,cpcmplstatusid,dcscmplstatusid,cpnum,cpnotes,dcsnum,dcsnotes)
OUTPUT INSERTED.id
VALUES (@authid,CURRENT_TIMESTAMP,@productid,@cpcmplstatusid,@dcscmplstatusid,@cpnum,@cpnotes,@dcsnum,@dcsnotes);
COMMIT TRAN

SELECT @id AS [id]

SET NOCOUNT OFF
END

So, some naming standards...
1) I name the table creation script [tablename].sql, so in this case aoe.sql
2) I name SP's related to that table [tablename].[SPname].sql, so in this case aoe.clsObjAOETbl_Insert.sql
3) Like I said in #2, the SP name is within the filename, so :setvar SPNAME clsObjAOETbl_Insert defines the name of the SP
4) The name of the SP is the VBA Class / Method so in this case: clsObjAOETbl_Insert is the clsObjAOETbl class / Insert method of that class.

I run these scripts from the command line, or within SSMS I turn on Query \ SQLCMD Mode so that the :setvar is supported.

I have developed an Open Object Rexx (ooRexx http://www.oorexx.org/) script to drive the individual SP scripts. Like I said, I use SQLCMD to run/rerun them, so from the command line I run my ooRexx script in the directory which has all of the scripts in it, ooRexx does a directory scan for *.*.sql and begins executing the scripts. The command line syntax to run SQL scripts is as follows:

sqlcmd.exe -S %DBhst% -d %DBdb% -U %DBuid% -P %DBpwd% -X -i script.sql

I may then go in SSMS and verify that all of the timestamps of the SP's have been updated, else perhaps a SP got orphaned / perhaps a script came up missing.

All of the scripts I check into a source code version control system. Thus I am able to keep track of what versions of a certain script are/were correct for specific builds of the application. I have two environments: Dev/Prod. So I get Dev all set/stable, then promote up the new version to Prod, test, and initiate a new Dev version once promotion is complete.

 

[Ade F]

Many thanks for your answer Michael. You have spent some quality time helping me. There are a lot of things for me to use / consider here.

Have a great day.

Best Regards

Adrian

 

[eltonlight]

It is nerve cracking to study about adp. But it is really interesting. However, the payroll services company ADP reported 136,000 jobs added in May. For June, it states that amount is 176,000, an average but very much pleasant increase.