Template Script Editor for SQL Server with Disconnected Recordsets (ADO)

Whenever you have the need to create a database bigger than something to manage your local stamp collection you will find out that the best frontend/backend solution to work with in Access is to use a Microsoft SQL Server (or other real database servers) as backend, simply because it's an active server in opposite to a passive Access backend file which can't do anything without a frontend working with this file (of course there are a lot more reasons to work with a database server but that's not the theme of this article).

Things which we all must do to work with a database are of course creating objects to work with, in the simplest form: creating some tables. When you create a small database this can be done quickly and so the SQL Server Management Studio is one good way to do that.

But when you create bigger databases of course the number of tables grows quickly and so you must always do the same things again and again: Open the table designer, create a long integer field, set a primary key on it, set identity insert on, create a timestamp field (in most cases necessary for Access as frontend), often create i.e. a name field for lookup tables and a comment field, set indexes as needed - and so on.
Can be very boring and time consuming the more tables you need to create. You can of course create template scripts in SQL Server Management Studio and insert the needed values, but I thought there must be a better way to enter some data quickly without directly working with create scripts to create tables. In my current database project I am now at 170 tables and it will be around 500 in the end so this little tool should allow to quickly select a create table template, enter the needed values and then create the basic table skeleton so I only need to adjust the result slightly with the table designer. It saved me a lot of time and so I thought maybe it is useful for other developers - so here it is.

The second thing which is part of this article is how we can use an ADO disconnected recordset to be able to edit a varying number of settings (variables) for each template script. This is needed in this little tool because we have a varying number of variables in each template script without knowing which one is used in which template and what it should display.

Because this tool was developed using SQL Server you need of course a running instance of SQL Server (at least 2005), which can also be SQL Server Express. The scripts on the server can be adapted easily to any other database server, I think.

The frontend used here was developed using Access 2007 but it should also run in other Access versions as there are no special Access features except using ADO.

Needed SQL Server Objects

(All scripts are included in the attachment.)

What we need are two tables, one which holds the template scripts and one which defines the variables to be inserted into these templates. The template script table contains a simple nvarchar(MAX) field for the script and all variables in a script can be defined using curly brackets and a unique variable name like "{name}" (without the double quotes). The variable table must have an entry with this variable name which defines the variable and it's behaviour in the frontend. There are two fields which are used to automatically convert the value the user entered into lower or upper case, moreover a ValueSELECT column where you can define a SELECT command (T-SQL syntax) which can contain any number of columns from any table or view, this will be used in a combobox later in the frontend. To do that you also need to specify the number of columns to be used in this combobox in the field "ValueSELECTColumns" and the column widths in the next field (in my demo entries German settings are used so it is i.e. "4cm;3cm", you need to change that to your regional settings). A further field can be used to define a default value to be displayed in the frontend when the user should enter values for the variables and the last field is a comment which is displayed in the frontend to explain the variable.

What is not in the table is a value column, because in a multi-user environment this would not work. You would need another table which contains a value column and a user ID or some temporary tables to be able to let any user enter a value in his own environment - but this template editor works without any additional table to enter the values.

Next we need to add one function and two stored procedures.

The function "fnCCFindVariables" searches through a given text for any variable enclosed in curly brackets, groups them to avoid duplicates and then returns a table of variable names which are used in the template script:

The stored procedure "procCCCreateObjectHelper" is used to edit the variables the function above has found. It uses the function to find the variables contained in the template script with the ID supplied in the parameter of the stored procedures and then it returns a table to be used in the frontend, where the table does not exist on the server and so it will only be edited in the frontend with a disconnected ADO recordset. The result will be used to create the table (in this case, it can of course be anything else depending on the used template scripts), we see that later.

The second stored procedure "procCCCreateObjectFromTemplate" is used when the user has completed editing the values in the frontend and wants to create the object now. It takes the variables and their values and needs the ID of the template script and then inserts all the values into the variables and executes the template script. In the end a message is returned (as error for easier handling in the frontend, but doesn't matter as this is for developers only and not for an end user):

The installation SQL script also contains some entries for the variables and three template scripts which will be inserted into the two tables. Moreover two views will be created for demonstration which will be used in the variables, one which shows all schemas of your database (except the standard ones but with "dbo") and one which shows "areas". This is from my current database project where I use my own kind of "schema" by naming the table objects with an area prefix like this: "dbo.tblCOMMON_Addresses" or "dbo.tblMASTER_Companies" and so on. The areas are here "COMMON" and "MASTER". The view extracts all these area names from the table list.

The Access Frontend

The Access frontend contains two main forms: A template script editor which you can use to enter new template scripts or update the existing and which contains a variable list combobox to insert the known variables from the variable table into the template script. As I edit the variable list with SQL Server Management Studio directly into the table I do not have a variable editor form, feel free to do that if you want. The template script editor is needed as this can't be done in a comfortable way with SSMS.

The other form is the editor which loads all variables contained in a chosen template script and offers them to edit their values as rows in a continous form. This is be done with a trick: First the stored procedure "dbo.procCCCreateObjectHelper" will be executed which uses a table variable to create a virtual table from the variable table. The recordset contains only the information about each variable but what we need is to enter also the variable value now. To do this you normally would need another table now to allow editing the values with a recordset. So the trick here to avoid an additional table is to create a disconnected recordset which is done in the "LoadVariables" method of the subform. It simply creates a recordset defining it's own fields where all fields are the same as within the returned recordset from the stored procedure with an additional Value field. This recordset has no connection to any database or table and exists only in memory. But fortunately Access can use ADO recordsets also in forms and so you can assign that to the form Recordset property - and now we have a variable list with an additional value field to be entered without a backend table.
To make it more comfortable the value field is displayed as a combobox and if the variable table contains a SELECT command then this will be executed using a pass-through query which has no SQL code, that is filled here with the SQL command of the variable table. Each row now has it's own combobox content and shows the chosen value without disappearing when you go to another row - something which is difficult with normal continous forms and bound or unbound comboboxes.

In the end you can now create SQL objects with this little tool and you can also use the disconnected ADO recordset trick for own purposes, for example if you want to manage user settings where each user has a different number and type of settings for your application.

To make it run you need to adjust the server name in the installation script in the attachment and also in the three pass-through queries (normally "ODBC;DSN=YourDSNName") and last but not least in the modCommon module in the connection string variable where you also need to enter the database name and so on. After that it should be usable in any SQL Server database.

Have fun in experimenting with disconnected ADO recordsets,

Christian
CCTemplateScripts.zip