Building and Using a Table of Queries

table of queriesIntroduction

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
ID AutoNumber
qryName TextSize=100
Indexed=Yes (No Duplicates)
The name of the query (for referencing in code)
qryString Memo The query
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).

Name Count_ExistingApplicantCountryInCountries
String SELECT Count(*) AS countCountries
FROM tblCountries, tblTEMP_NewMemberEntry
WHERE
(((LCase([tblCountries.[CountryName]))=LCase([tblTEMP_NewMemberEntry]![applicantCountry])));
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:

Private Sub cmdSave_Click()

...

'// Update all lookup tables first...
Call DoWork_LookupTables

...

End Sub

(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:

Sub DoWork_LookupTables()
Dim rsCheck As DAO.Recordset
Dim varMsg As Variant
Dim lngResult As Long
Dim strSQL As String

On Error GoTo handle_Error

'// Check Applicant Country
Set rsCheck = Nothing
strSQL$ = ReturnStandardQuery$("Count_ExistingApplicantCountryInCountries")
Set rsCheck = CurrentDb.OpenRecordset(strSQL$)

If rsCheck.Fields.Count = 1 Then
lngResult& = rsCheck.Fields(0)
If lngResult& = 0 Then
strSQL$ = ReturnStandardQuery$("Insert_ApplicantCountryInCountries")
If ExecuteSQL(strSQL$) = False Then
varMsg = MsgBox("It was not possible to insert the Applicant's Country Name into the tblCountries table. You will need to seek support.", vbOKOnly, "ERROR: Insert Applicant Country...")
GoTo exit_Gracefully
End If ' ExecuteSQL(strSQL$) = False
End If ' lngResult& = 0
Else
varMsg = MsgBox("The wrong number of records was returned from the 'Count_ExistingApplicantCountryInCountries' query. Seek Support.", vbOKOnly, "ERROR: Returned Data Invalid...")
GoTo exit_Gracefully
End If ' rsCheck.Fields.Count = 1

...

End Sub

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:

Function ReturnStandardQuery(strQueryName As String) As String
Dim varMsg As Variant

On Error GoTo handle_Error

ReturnStandardQuery$ = CStr(DLookup("[qryString]", "tblOfQueries", "[qryName]='" & strQueryName$ & "'"))

If ReturnStandardQuery$ = "" Then GoTo handle_Error

exit_Gracefully:
On Error Resume Next
Exit Function

handle_Error:
MsgBox "Error " & Err.Number & " " & Err.Description & " in ReturnStandardQuery$ function."
End

End 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.

Function ExecuteSQL(strSQL As String) As Boolean
Dim adb As DAO.Database

On Error GoTo handle_Error

ExecuteSQL = False

Set adb = CurrentDb adb.Execute strSQL$, dbFailOnError

ExecuteSQL = True

exit_Gracefully:
On Error Resume Next
Set adb = Nothing
Exit Function

handle_Error:
MsgBox "Error " & Err.Number & " " & Err.Description & " in ExecuteSQL function."
Resume exit_Gracefully

End Function

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.

 

Conclusion

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!

Advertisements