Image of Navigational Map linked to Home / Contents / Search The Executioner Is In

by Ross Mack - GUI Computing
Image of Line Break

Some Access Tools IV: A Prologue in which our hero faces the frumious Bandersnatch and his vorpal code goes snicker-snack.

I like generic tools, anyone who regularly reads my articles will probably realise this. The best thing about generic tools is that they make our work not only quicker, but they allow for more creativity. Personally I am well past the point where opening a whole new dynaset thrills me with excitement and a feeling of adventure. Yet, what I do enjoy is coming up with elegant, functional solutions to whatever problems and needs someone is asking for today. One day I might actually get this right!

With a good library of not only code, but tools as well, you can start to approach problems from a creative rather than a purely technical point of view. Instead of attempting to figure out how to achieve each minor feature of a functional requirement, you can instead think about how to best combine your tools to provide the most elegant and efficient solutions. After all, that's the reason why we use VBXs, isn't it? Also, I'm lazy.

The story continues: I have recently been working on a project that shipped in at least three distinct versions. Each version had a different database - it was basically the same, except each successive version had more querydefs, different columns, or different metadata. Of course we had to provide an upgrade path from one version to the next, and from the first version to the last, and so on. To further complicate matters, the application used multiple copies of the database in different directories (not my idea, folks).

Now, what I could have done was write a pile of Data Access code to handle each change, and try to make sure that all the changes for all the versions were in place. Too hard, and a mess to maintain. I already had some code that converted a SQL script of Table, Index and Querydef creation statements into an Access database. That's OK, but I needed to do more than that. Having a look at the existing code I realised it actually required no change to add the execution of any other SQL statements (within certain limitations) to the script. So long as I could express everything I wanted to do in SQL, I was set.

I took the existing code, wrapped it up in a function which located all the databases we were interested in, and all of a sudden we were on the way to having a useful upgrade application. Better yet, the upgrade could be controlled and customised simply by changing the SQL script it would execute. The simplest way to handle this, I found, was to dump the Data Definition SQL script for each database version. Then I used Textpad (an excellent shareware text editor) to tell me what differences there were between the SQL dumps for each version.

The differences that Textpad pointed out became the SQL scripts to upgrade from one version to another. For example, let's say that two databases are identical except that one of them has an additional Table. The file comparison of the two SQL dumps would indicate that one file had an extra 'CREATE TABLE …' SQL statement. I take that statement, write it into a text file, and that becomes the upgrade script for that version from the version it was compared to. Simple, yes?

The installation disks (created with WISE - refer to John Mina's Product Review) simply determine which version of the application we are upgrading from, and installs the EXE to run the SQL script (I called SQL Executioner, because it has a sinister feel) and the correct script. Then it launches SQL Executioner passing the filename of the script as the command line parameter. It works. Better yet, it will work for any Access database modification within certain bounds - essentially the limitations of the SQL that JET supports.

Since then I have further enhanced that simple EXE (it is now bulging to around the 20k mark!). Now, here is where I make my point about reusable tools and components. I have added support for a snazzy Progress Indicator simply by plugging in my standard Progress Indicator component (see Standing in the Way of Progress, last AVDF issue) which can be turned on and off via an INI file setting.

Options for logging errors to a file are also configurable via INI file settings.

We now also support variables within the SQL script. This is done through my token library (as explained in my Expression Parser Library article, this AVDF issue). Use of tokens is turned on by setting a flag in the SQL Executioner INI file, which then uses the token library to load any defined tokens from the specified section of the INI file. For example, what we could do is define a token that specifies the path and filename of the database we want to operate on in one application, and use the library again to save it to SQL Executioner's INI file. Then any references to that token in the SQL script would be replaced by the actual path and filename of the database, before any script item is executed.

If you define the token '{DBPath}' as having the value 'C:\VB\BIBLIO.MDB', then when the SQL Executioner executes the statement 'USE DATABASE {DBPath}' it first replaces the token with the value, resulting in 'USE DATABASE C:\VB\BIBLIO.MDB'.

You will notice that the SQL above ('USE DATABASE …') is not standard Access SQL. To make the SQL Executioner more flexible I have added to the normal SQL Syntax. I simply check the SQL statement for a number of special cases before executing it. It boils down to a few If…IfElse…Else statements (I'd like to make it Select Case, but it becomes too indirect).

Image of How-To Icon The code looks like this:

The code that actually reads the SQL statements from the script files is also quite simple. In general, it adds each incoming line to the SQL statement being read until each reaches a semicolon (;) terminator. If a line is blank or begins with '//' (signalling a comment) it is ignored. Once a whole statement has been read, it is checked for tokens and added to an array which is then read by the executing code, as in the above sample. The other special case it checks for is a line beginning with '#include'. Call me old fashioned, but it seemed like a good idea to support include files. When it encounters an include statement, the script reading routine simply calls itself to read the included file.

The support for include files means that we can have one standard set of SQL to be executed, and can build a custom file to be included for different databases. Also, tokens can be used to specify the name of that file. Together they make the app very flexible.

Image of How-To Icon The essential file reading code is shown here:

The zip file available for download (22kb), includes an updated version of the SQL script, generating code from the previous article to support this new syntax. It also includes all the VB code for the SQL Executioner - remember to test this thoroughly with your SQL script before relying on it. I hope you find this code to be useful, I certainly have - time and time again.

Written by: Ross Mack
April 96

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article

Image of Line Break