Building and Using a Table of QueriesPublished: 08-Oct-2014
One problem that can surface as your database grows more and more complicated is the queries that you need to get data into or out of it. In Microsoft Access®, more than about ten queries, and the user interface even has trouble keeping up with the assets it needs to manage on your behalf. I often find the query area in particular "blinking" on and off like a demented strobe as I'm scrolling into or past it in the database assets window. And then there's remembering what each query is for. Have I made one for this or that task yet? Can I copy one and augment it, or do I need to start back at the beginning?
I'm not sure if this is a problem in other database applications, but, in Microsoft Access®, there is a name-space issue whereby if you give a query the same name as an existing table name, that table will be over-written with just the query. And 'voila', there goes your data in a puff of smoke. And your table design. You get to keep the query though.
So, what's the solution? I'm so glad you asked!
Table of Queries
So here's a solution to a number of problems all at once. Consider building a table to hold all of your queries.
First, create a table, which we're going to call "tblOfQueries". You'll notice that I don't use special characters (like underscore, or dash). I have a three letter prefix to indicate that it is a table (I didn't make up this particular naming convention, but I'm an evangelist of the one I use - which you can find out about here: http://goo.gl/gRwFsq). There's also no spaces in the name, and I use capitalisation to help break up the words.
Within the table, we're going to add three fields. A fourth field will be automatically created in most database tools. If an ID field is not created automatically, create a field specifically called ID and give it the design characteristics of a unique ID.
|Field Name||Data Type||Description|
Indexed=Yes (No Duplicates)
|The name of the query (for referencing in code)|
|qryPurpose||TextSize=255||What is the query for?|
You'll notice again the naming convention of the field names - a prefix to indicate "Query", giving a longer name of "Query Name" in the case of "qryName" for example. Once the convention is known and understood, it becomes an effective short-hand. If I were to use the noun "Name" I would likely come across a "The Name you supplied is a reserved word" error at some point, or some other fail.
The idea of the index on the qryName field, is, first of all, to order the queries by the name you give them. This is just like the general user interface characteristics in relation to sorting and displaying asset objects like queries, tables, forms and the like.
Including the "No Duplicates" option means you can't accidentally create two queries with the same name. Because that would be confusing.
You'll notice that the qryString field has been assigned a Data Type of "Memo". This is because query strings can become quite large. I have kept the qryName and qryPurpose field lengths at more reasonable field lengths.
Now that we have our table created, let's populate it with a query, and then let's provide some example code to utilise it.
Creating a Query
Create your query in the usual manner. Once the query is created, check the SQL version of the query. In Microsoft Access®, you can get to the SQL by changing the view from Design to the SQL option.
This particular query is used to check if a Country name exists in a lookup table. Later code will then insert a new Country name (should it not already exist).
|String||SELECT Count(*) AS countCountries
FROM tblCountries, tblTEMP_NewMemberEntry
|Purpose||Check if a Country in the NewMember table applicant field exists in tblCountries.|
This particular query counts the amount of records that match a particular value from another table. For the purposes of the application I'm building, I'm creating a quick check to determine if a value from one table exists in another table. If it does not exist, I'll then insert the value I'm testing for into the destination table.
You may have noticed an underscore character in the query name. Since this value is a text string and not an actual Microsoft Access® asset name, I allow myself some additional flexibility in my naming convention. The first portion of the query name indicates what the main purpose of the query is - to Count. The next portion provides a description of that action.
You'll have to use some imagination for some of the concepts we're about to discuss. I have a form that an end-user will enter data into. The data is being entered into a holding area which houses 'scratch' (draft) information. Once this data is entered, and the data in the form is validated, the next step is to move that data from its holding area into its permanent locations into other data tables of the database.
There's a number of ways to skin this cat. I'm using an "OnClick" event of a button (not very imaginative, but it's effective).
My button is called "cmdSave". In the Click event, we have:
(The elipsis ("...") indicates that there is other code in this sub-routine, but, because it's not relevant to this lesson, I'm not including it).
Within the routine we're working with we have:
Within the code snippet, we're creating a recordset object, a variant data type (in case we need it to inform the end-user of something), a number with a long data type which we're using because we're expecting to return a number of rows from a table, and a string data type to hold our query string.
The code goes on to return a query string via the "ReturnStandardQuery" function, which I'll describe shortly. Once we have the query, we then open it.
Once the query is open, we then test what value was returned. Because the query is designed to return only one row - the count of records, the code is strict in what it expects. If it doesn't return 1, then an error is thrown.
If the RecordSet object returns a 1, we then check what the result of the Count is. If a 0 is returned, then we know that the value we're looking for does not exist in the table, giving us license to run our Insert query. If a number apart from 0 is returned, we don't do anything in this case. Our table logic for our tblCountry table includes a requirement that a country need only be defined once, and this rule is respected by this code.
We also have an ExecuteSQL function which I use to run our more demanding Insert (also known as Append) queries.
Returning Data with Functions
Functions that I know I'll be using over and over again are kept in a module that I name mdlUtilities. The functions used in the code above are located in my Utilities module, where I can share them around throughout my application. It's less work that way.
This is the ReturnStandardQuery function:
This function uses the built in DLookup (domain lookup) function of Microsoft Access® to return the qryString field data from the tblOfQueries table. The query we want returned is provided to the Function by the strQueryName data variable. If the function fails, or if the returned value is an empty string, we throw an error and abort all further actions.
The last code snippet I'll show you is the ExecuteSQL function. It's the remaining piece in this article anyways.
This function uses the "Execute" method to cause a query to, well, execute. I'm using this particular method because for times that I use this function I'm using action queries that Insert (Append) data to tables. To help the other portions of the application, the function returns whether or not the action occurred successfully by becoming set to "True" (or remaining "False" if something goes wrong). I purposefully set the function to "False" at the beginning of the Function so that the next developer has a grasp of my specific intention. It's a courtesy rather than a necessity.
So, what have we learned? We've learned a simple, yet effective way to manage our ever growing set of database queries by creating a dedicated tblOfQueries Table, and placing our query strings into that Table. And then a code sample was provided to discover how to effectively utilise the query strings that are inside the tblOfQueries Table.
Until next time!