The Kid Grows Up

Bob Phillips — Wed, 03 Mar 2010 12:41:00 GMT

In a previous post, I talked about building stored procedures (SPs)in an Access database, and calling the same from Excel using ADO.

As I mentioned in that post, I am not a fan of the Access GUI. Whilst GUIs can be okay for doing some simple testing, checking whether something works or, I find it far easier to build a script when I need to do similar things over and over (such as building all of the SPs for an application). I am an inveterate scripter (see Autogen’ed Ribbon Code and XML Is Such A Pain); rather than build the stored procedures using the Access GUI, I much prefer to build a script file that can be rerun at any time. This is very much in line with my preferences to autogen as much as possible, and also with me development methodology, where I prefer to allocate design time before ploughing into the functional code.

In the post mentioned above, I said that … you can remove all of the inline SQL from your applications, create a separate SP creator app that creates the SPs, have better structured code, and more maintainable. This post will cover such a creator app.

In this app, I have a script file that defines all of the SPs, and the application just reads that file and builds the SPs defined therein. I have used an INI file as my SP definition file; I like the flexibility of INI files, the format does not have to be too rigid, and they are easily segmented, and easily read (via code).

The format of my file is as follows

1[spb_App]
2
3   [spb_DB]
4
5       DBType=Access ;could be SQL Server or any other DB
6       DBPath=C:\MyDatabases\
7       DBName=MyDB.mdb
8
9   [spb_Stored_Procs]
10
11       TypeCount=7
12
13       I001=Get
14       I002=Check
15       I003=Insert
16       I004=List
17       I005=Query
18       I006=Table
19       I007=Update
20       I008=Delete
21
22;-----------------------
23; Get Stored Procedures
24;-----------------------
25       [spb_SP_Get]
26
27           SPCount=7
28
29;spGetCompanyGoals
30           [spb_SP_Get_001]
31
32               SPName=spGetCompanyGoals
33
34               [spb_SP_Get_001_Parameters]
35
36                   ParameterCount=1
37
38                  [spb_SP_Get_003_Parameters_001]
39
40                      ParamName=prmLoginID
41                      ParamDataType=VarChar (50)
42
43               [spb_SP_Get_001_SQL]
44
45                   SQLLineCount=4
46
47                   Line001=SELECT SUM(SalesGoal) AS 'Company Sales Goal',
48                   Line002=      SUM(BonusGoal) AS 'Company Bonus Goal'
49                   Line003=FROM refUsers
50                   Line004=WHERE LoginID = prmLoginId;
51etc.

Line 1 isn’t actually used, it is just for completeness.

Lines 3-7 define the database, the type and location. Note that the type is to allow for building SPs in different databases, although we will just discuss Access.

Lines 9-20 define the SP categories, I do this so as to break up the SPs and keep them grouped, for easier maintenance. The Type (Get, Check, etc.) is used as part of the section id for the SP details, as in lines 20, 30, 34, and 38.

Line 27 defines how many SPs are in that group, used as a loop index in the code.

Line 32 is the SP name, used in the code to Drop the SP then Create it anew.

Lines 34-41 defines the parameters. As you can see, there is a ParameterCount specifying how many parameters the SP uses. A definition for each parameter, if applicable, follows, with an incrementing suffix index so that the app can extract each in turn.

Lines 43-50 define the SP code, with a SQLLineCount defining how many lines of SQL are within the SP. In the example above, the SP is very simple, but of course SPs of any complexity can be built.

Lines 29-50 are repeated for each SP within that category.

Lines 22-50 are repeated for each category of SPs.

The category names are not relevant, it can be any name and any number, as long as the sections match up.

Care has to be taken that the definitions are consistent, the category id is correct, the SP index is carried through, the parameter name in the parameter definition is the same as the parameter in the SQL code., and so on.

The SP Builder addin can be downloaded from here. It is unprotected, so the code can be examined, updated as you see fit.

I use this technique for all of my databases, so I have a script file for each, and can easily recreate the database code. As I mentioned, by creating a script file it helps in better design, thinking about the code required rather than diving into the GUI and building as required. This technique could be extended to creating the database, building the tables etc. I have a separate app for this, but have not combined them as I find myself creating the SPs far more often than the database, I find it more convenient to keep as separate applications.

Excel Do, Dynamic Does : VBA, Excel 2007

The Kid Grows Up