How to check if an Access database object exist

The Usual Approach

The idea for this function came to me while responding to a question in the forums on how to check if a query exist. The usual approach is to assign the query's name to a QueryDef object and trap the error, if the query does not exist. For example:

Public Function QueryExists(QueryName As String) As Boolean
'thedbguy@gmail.com
'11/5/2021
Dim qdf As DAO.QueryDef
On Error Resume Next
Set qdf = CurrentDb.QueryDefs(QueryName)
If Err = 0 Then
    QueryExists = True
Else
    QueryExists = False
End If
Set qdf = Nothing
End Function

A Better Approach

Now, the above approach of using On Error Resume Next and then checking for errors is a very common approach. However, someone in the forums mentioned that this is not a very "clean" approach, because it leaves an Error Object hanging in the Errors Collection. They mentioned that their preferred method is to loop through the QueryDefs collection and check for the name of the query. For instance:

Public Function QueryExists_1(QueryName As String) As Boolean
'thedbguy@gmail.com
'11/5/2021

Dim qdf As DAO.QueryDef
Dim blnReturn As Boolean

For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = QueryName Then
        blnReturn = True
        Exit For
    End If
Next

QueryExists_1 = blnReturn

End Function

An Even Better Approach

Now, that makes a lot of sense, doesn't it? However, another smart person said: "Why not just use the MSysObjects table to check if the query exists?" Well, isn't that genius? So, inspired by that comment, I decided to create the following function as another option for checking if an Access database object exist or not. It goes something like this:

Option Compare Database
Option Explicit

Public Enum dbgObjectType
    dbgTable = 1
    dbgQuery = 5
    dbgForm = -32768
    dbgReport = -32764
    dbgMacro = -32766
    dbgModule = -32761
    dbgODBCLinkedTable = 4
    dbgOtherLinkedTable = 6
    
End Enum

Public Function ObjectExists(ObjectName As String, ObjectType As dbgObjectType, _
                                Optional DatabasePath As String) As Boolean
'thedbguy@gmail.com
'11/5/2021

Dim blnReturn As Boolean

If DatabasePath = "" Then
    blnReturn = DCount("*", "MSysObjects", "[Name]='" _
        & ObjectName & "' AND [Type]=" & ObjectType)
    
ElseIf Dir(DatabasePath) = "" Then
    'MsgBox "Cannot find " & DatabasePath, vbInformation, "Info!"
    Debug.Print "Cannot find " & DatabasePath
    blnReturn = False
    
Else
    blnReturn = CurrentDb.OpenRecordset("SELECT Count(*) FROM [;Database=" _
        & DatabasePath & "].MSysObjects WHERE [Name]='" _
        & ObjectName & "' AND [Type]=" & ObjectType)(0)
    
End If

ObjectExists = blnReturn

End Function

As you can see, I have decided to use an enumeration and allow the user to specify which object they want to search. Also, the above function allows the user to search objects in an external database. I just thought these additional features might help make the function a lot more flexible and allow the developer to use it in multiple situations. So, now, the above approach does not rely on the Error object and also skips going through a loop to search for a single object.

Extension

Having the above function available, we can even extend the idea by creating smaller helper functions designed to search for a specific object. Here are some examples:

Public Function TableExists(TableName As String, Optional DatabasePath As String) As Boolean

TableExists = ObjectExists(TableName, dbgTable, DatabasePath)

End Function


Public Function FormExists(FormName As String, Optional DatabasePath As String) As Boolean

FormExists = ObjectExists(FormName, dbgForm, DatabasePath)

End Function

I hope the above information was useful, and I welcome any comments, recommendations, or criticisms.

As always, thanks for reading. Cheers!

SimpleCSV() v2.0

 A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of this function is it doesn't work with parameter queries.

I have always pointed people to Leigh's Generic Recordset function to deal with parameter queries and avoid the "3061. Too few parameters" error.

Today, I was trying to help someone in the forum and decided to combine the two functions together. The following updated version of the SimpleCSV() function should be able to handle certain parameter queries. Please let me know if you find any bug.

Public Function SimpleCSV(strSQL As String, _
            Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://www.accessmvp.com/thedbguy
'v1.0 - 8/20/2013
'v2.0 - 8/25/2021 Handles parameter queries

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Variant
Dim strCSV As String

Set db = CurrentDb()

Set qdf = db.CreateQueryDef("", "SELECT * FROM (" & strSQL & ")")

With qdf
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
End With

Set rs = qdf.OpenRecordset

'Concatenate the first (and should be the only one) field from the SQL statement
With rs
    Do While Not .EOF
        strCSV = strCSV & strDelim & .Fields(0)
        .MoveNext
    Loop
    .Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End Function

Access Error: "Query is Corrupt"

By now, most of you would have already known about this latest Access bug from Microsoft as explained on their website and discussed in many forums and blog posts. If so, you may be wondering why am I writing this blog.

That's actually a very good question. Well, the simple answer is I decided to do my part and offer a temporary solution I think is a lot simpler than what Microsoft recommended. This approach avoids the need to modify any saved queries or SQL statements in VBA code (up to an extent - I can't imagine all the possible scenarios out there).

This utility can be deployed to your users, and they can apply the fix themselves. And when the actual fix becomes available from Microsoft, the users can rerun the utility to remove the temporary fix.

To download the "Query is corrupt" bug fix utility, go to this post at UtterAccess. If you find any problems or have any recommendations, please do not hesitate to let me know.

I hope this helps at least those using Access 2010 or 2013, where a fix from Microsoft is still pending. (If you're using Access 2016 or 2019, the fix is already available to you.)

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with parameter queries, you can review my article on Parameter Query Basics.

The following SQL statement is an example of  a parameter query.

SELECT Title, FirstName, LastName, Position
FROM Employees
WHERE Department=[Enter Department]

The above query will prompt the user to enter a department and the result will show all the employees working for that department. So, users are free to enter any department they are interested to see, each time they run the query. But what if the user wants to see all the employees for more than one department?

Problem

Typically, in a non-parameterized query, the SQL statement might look something like this.

SELECT Department, Title, FirstName, LastName, Position
FROM Employees
INNER JOIN Detpartments
ON Employees.DeptID=Departments.DeptID
WHERE Employees.DeptID In(1,3,5)

In the above example,  we switched to using the foreign key (DeptID), which would be a more likely scenario anyway and also to make demonstrating the technique being presented a bit simpler to explain. Also, please note the In() clause in the above example is a shorthand for the following criteria (meaning, it's functionally the same).

WHERE Employees.DeptID=1 OR Employees.DeptID=3 OR Employees.DeptID=5

So, our goal is to prompt the user to enter multiple DeptID using a parameter. One might be tempted to try the following as a query criteria.

WHERE DeptID In([Enter DeptID])

and expecting the user to simply enter 1,3,5 when prompted. Unfortunately, this seemingly straightforward approach will not work because Access will interpret the entire input as a String or Text. So, entering 1,3,5 does not mean 1 or 3 or 5; but rather, a string value of "1,3,5" - and we know there won't be any DeptID of "1,3,5" because DeptID is a numeric field. So, the query will result in an empty set because the intended criteria fails. Of course, the obvious solution is to use the following criteria.

WHERE DeptID=[Enter Dept1] OR DeptID=[Enter Dept2] OR DeptID=[Enter Dept3]

The above approach will work, but the user will be prompted to enter a single DeptID three times, which would be fine but what if the user wants to know more (or less) than three DeptID? As you can see, this approach is not very flexible.

Solution

Instead of using the above approach, we could try either of the following approaches.

WHERE InStr(";" & [Enter DeptID (separated by a semicolon ';')] & ";", ";" & DeptID & ";")>0

or

WHERE ";" & [Enter DeptID (separated by a semicolon ';')] & ";" Like "*;" & DeptID & ";*"

When the user runs the query and gets the prompt, we are expecting the user to enter something like 1;3;5, which in our criteria will be converted into ;1;3;5; (I prefer to use semicolons but using commas would work the same way).

The first approach uses the InStr() function to search the current record's DeptID, encapsulated within a pair of semicolons, within the user's input string from the parameter prompt. If a match is found, the result of the function should be a number greater than zero (0), which would make the criteria evaluate as True and include the current record in the query's result set.

The second approach simply uses the Like operator with a pair of wildcard characters to accomplish the same effect as the first approach. I prefer to use the second approach because it is simpler and avoids a function call, but either approach will work as intended.

Caveat

Unfortunately, this solution is not perfect. The first drawback is you'll have to "train" your users to enter their input using the exact format your query is expecting (e.g. 1,3,5 or 1;3;5 or 1 OR 3 OR 5). Otherwise, the query will not work if the user enters their input using any other format (e.g. user enters 1,3,5 but you expect 1;3;5). Adding the expected format in the parameter prompt, as in the example above, should help a little bit in reminding the user what format is expected.

The other drawback I would like to point out affects any parameter query, in general. As I mentioned in my article on Parameter Query Basics, there is really no way to validate the user's input when using a parameter query. Your query might be asking users to enter a DeptID in the parameter prompt but nothing will stop them from making a mistake and end up entering a DeptName instead. For this reason, it is considered "best practice" to use an unbound form to accept user input for your query parameters. Using a form will allow you more control over the user's input. You can easily validate their input when you use a form.

The last point I'll mention, if you do end up using parameters in your query, is to make sure you declare the data type for each parameter. This is more applicable when each parameter is a single value. To declare the parameter's data type, you can click on the Parameters button on the Design Ribbon to open the Query Parameters window.

To declare the parameter data types manually, you can add the PARAMETER clause at the beginning of your query's SQL statement. For example:

PARAMETERS [Enter DeptID] Long, [Enter Department] Text ( 255 );
SELECT ...
FROM ...
etc...

Please note, it is very important to end the PARAMETER clause with a semicolon, or you will get a syntax error. Also, the PARAMETER clause is typically required in Crosstab queries.

Conclusion

Parameter queries allow us to create semi-dynamic queries, which could help reduce the number of queries we need to create if all we need to change is the criteria condition of the query, based on user input. It is even possible to create a parameter query that accepts multiple values from the user. However, parameter queries have some limitations that it is highly recommended to use input forms whenever possible. Still, parameter queries are good as quick solutions for those times when the requirement is simple enough with very low chance of user input errors.

Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to explain my reasoning, I have decided to write an article about it.

What is a "placehoder" record?

First, we'll need to define what I consider as placeholder records to help define why I oppose them. To do that, I will try to use some concrete examples of what I consider as placeholder records (other developers may have a different definition of what is a placeholder record).

My definition of placeholder records are records created in advance and stored in the table to represent future records. In other words, these records are created in anticipation of possible records that may or may not be needed in the future. For example, let's say you have a database to track class attendance. If a class will have a total of ten sessions, you might be tempted to create ten records, in advance (at the time of enrollment, for example), per student to make it easier to mark if a student attended each session. They are considered placeholder records because you are creating records to represent future class sessions.

Other examples of potential placeholder records may be found  in booking databases (room and equipment rentals or theater shows to track daily occupancy or usage) or property management databases (apartment or house rentals to track monthly payments).

So, why are they bad?

Well, I probably wouldn't say they are bad but more like unnecessary. Records should only be created when they are needed and not before. For example, if we take the example of the class attendance database, each attendance record should only be created when that particular class session occur. And even then, I would also recommend that you only create records for those in attendance. There's really no need to create records for the absent students, because we can always determine who they are by comparing the list of students from those who attended the class. You can also create a query to show both attendees and absent students as the record source for your form or report.

If you need to produce sequential records for use in a crosstab query or a daily status report, you have the option of using a cartesian product query as presented in an earlier blog article: A practical use of a Cartesian query. That article demonstrates the use of a Tally table, which I also prefer over creating a table of sequential dates.

If you like the convenience of having placeholder records in the table for your data entry forms, that can also be addressed by other methods such as using a temporary table or a multi-select listbox. The user can select all the records to be created from the temporary table or listbox, and then VBA code can be used to actually add those records into the table.

Conclusion

So, to summarize, placeholder records could take up space in the table when it is not actually needed. They don't necessarily violate "normalization" rules, but it's considered best practice to avoid using placeholder records. Hopefully, I have described how the common benefits of creating placeholder records can be easily addressed without actually creating placeholder records.
The next time you hear me tell someone to avoid creating placeholder records, you now know my reasoning behind it. I would like to hear other perspectives on this topic, so please leave a comment to share your thoughts.

Thanks for reading!

Original post date: October 27, 2014

Query Join Basics

Recently, I have noticed a few developers, new to Access, showing up in the forums and were confused by the different JOINs that can be used in a query when retrieving basic information from their tables. This blog is my attempt of clarifying some of the basics regarding the different choices when joining or linking tables together in a query.

As I previously mentioned in my blog on Cartesian Query, there are basically three types of joins we can use when using multiple tables in a query:

1. INNER JOIN - returns all matching records between the joined tables
2. LEFT OUTER JOIN - returns all records from the "left-side" table and only the matching records from the "right-side" table (opposite of "right outer join")
3. RIGHT OUTER JOIN - returns all records from the "right-side" table and only the matching records from the "left-side" table (opposite of "left outer join")

INNER JOIN

When two tables are linked using an INNER JOIN in a query, they might look like this:



In this example, the line between Field1 in Table1 and Field1 in Table2 will instruct Access to return all records from Table1 and Table2 where the values in Field1 between the two tables are a match. If Table1 had 15 records in it, and Table2 had 20 records in it but only 12 records match between the two, then the query will only show those 12 records that were a match.

LEFT OUTER JOIN

If we had wanted the query to return all the records from Table1 with all the matching records from Table2, we could use a LEFT OUTER JOIN. To create a LEFT OUTER JOIN, we would start with an INNER JOIN and then right-click on the "join line" between the two tables and select "Join Properties."



The following "Join Properties" dialog window will show up:



As you can see, the initial join was set to include only the records that match between the two tables (Join Type #1). To do a LEFT OUTER JOIN, we just need to select Join Type #2 and click the OK button. Once the Join Properties dialog window closes, the query designer should update the relationship line between the two tables with an arrow head pointing to Table2.



At this point, running the query will return all 15 records from Table1 and show 12 of them with matching values from Table2 and show Null values for the three records with no matching  values from Table2.

RIGHT OUTER JOIN

Creating a RIGHT OUTER JOIN is the same as the above process; but this time, we will select Join Type #3. This join type will return all records from Table2 and display any matching records from Table1. The arrow will now also point to Table1.



Running this query will return all 20 records from Table2 and show 12 of them with matching values from Table1 and show Null values for the eight records with no matching values from Table1.
Please note that if you view the SQL statement of the query when using either LEFT or RIGHT outer joins, the SQL statement does not actually use the keyword "OUTER." Instead, you will just see either "LEFT JOIN" or "RIGHT JOIN."

I hope that helps explain the basics between the different join types in an Access query. They should not be confused with the table joins used in the Relationship Window because, although they may look similar, that is actually somewhat a separate concept.

One final note regarding Access Query Joins, Jet SQL (the SQL language used by Access) does not support a FULL OUTER JOIN that full RDBMS like SQL Server does. To return all the records from both tables in Access, matching or not, you may have to use a UNION QUERY.

Original post date: February 28, 2014

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will both update existing data in the table and add new records at the same time.

The Scenario

The member stated that he has a master table of data in Access and receives updated information every month in an Excel file. The Excel file contains the same information as the master table but with user changes to the data and possibly new information as well. The challenge is to update the Access table with the user changes and append the additional information as new records - at the same time.

The Usual Solution

The first step, usually, is to link to the Excel spreadsheet so we can access the data from within Access. We can then create an UPDATE query that joins the master table with the linked table to apply the latest changes to the data. However, this doesn't include the new information from the linked table.
As a second step, we might also create an APPEND query to grab all the new information from the linked table and add them to the master table. We would then need to execute both queries, one at a time, to complete the task of updating the master table.

The Combined Solution

But, since the challenge was to perform both the update and append actions in one query, we had to use a different technique. I can't say that this is a new technique because I'm sure it's been around for a while, and most of you probably already know it. I just thought of writing it down here as a possible reference for me in the future.
So, how do we execute an UPDATE and APPEND query at the same time? Well, we also start out by linking to the Excel spreadsheet to access the data. We then create one query that joins the master table to the linked table. However, in this case, we will use an OUTER JOIN to return all the records from the linked table.
As a result, the database engine will update existing data and append new records at the same time. Here's an example of how the query's SQL statement might look like:

UPDATE tblMaster
RIGHT JOIN tblExcel
ON tblMaster.FieldName=tblExcel.FieldName
SET tblMaster.FieldToUpdateOrAdd=tblExcel.FieldToUpdateOrAdd

Original post date: November 30, 2013

SimpleCSV()

I know there are already plenty of examples out there on how to concatenate records or child records into a delimited string, but I also see this request often enough in the forums that I wanted to have one place to point them to for a possible solution.

The following is just a simple function most users and developers can add to their database to quickly produce a delimited string of records they would like to combine. I purposely made it simple because all the bells and whistles, such as sorting the list or accepting listboxes as arguments, are already available on other websites, so there's really no need for me to reinvent the wheel for those.

To use this function, just pass a "SELECT" SQL statement and an optional delimiter to it. The power of this simple function is really in the creativity of your SQL statement. Please note there are no error handlers, so you'll have to add your own.

As always, I appreciate any comments you may have. Thank you.

Public Function SimpleCSV(strSQL As String, _
   Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records
' in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Concatenate the first (and should be the only one)
' field from the SQL statement
With rs
 Do While Not .EOF
  strCSV = strCSV & strDelim & .Fields(0)
  .MoveNext
 Loop
 .Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

Original post date: August 21, 2013

What's the opposite of DSum()?

A member recently posted a request in the forums for a function that does the opposite of DSum(). The intent was to have a query with a running total but using subtraction instead of addition. I never heard of anyone needing to do something like this before, so it was no surprise that I couldn't find a built-in function that does the opposite of DSum() or Sum() to offer the member.

I really don't know if anyone would ever need to do this again, but I just thought to share what I came up with in case it may be useful to someone else. The following function is very basic and does not include any error handling. Hopefully, you can modify it to suit your needs. I would happily accept any comments or recommendations to improve this function to better help those who may want to use it.

Public Function DDiff(strField As String, strDomain As String, _
    Optional strCriteria As String) As Variant

'accessmvp.com/thedbguy
'7/26/2013

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim varDiff As Variant

strSQL = "SELECT [" & strField & "] FROM [" & strDomain & "] "

If strCriteria > "" Then
    strSQL = strSQL & " WHERE " & strCriteria
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
    If Not (.BOF And .EOF) Then
        varDiff = .Fields(strField)
        .MoveNext
    End If
    Do While Not .EOF
        varDiff = varDiff - .Fields(strField)
        .MoveNext
    Loop
   .Close
End With

DDiff = varDiff

Set rst = Nothing
Set dbs = Nothing

End Function

To see the discussion that started it all, including a pure SQL solution, click here.

Original post date: July 27, 2013

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query, I will now try to describe in detail one example of a practical use of a Cartesian Query.

As luck would have it, while I was preparing the materials for this blog, an UtterAccess member (Kamulegeya) posted a question in the forum that's a perfect fit for this topic. So, we will use his situation as the example for this blog.

In a nutshell, he wanted to create a report that displays the number of days a room was reserved or "booked" for a given month.

For instance, let's say we have the following data:

Room Reservations Table



And we want to have a query that looks like the following:

Dates rooms were occupied



Basically, we want to produce a record for each day that the room was reserved or occupied so we can easily count them. So, how do we do that? Enter, the Cartesian Query.

As we have learned before, a Cartesian Query involves more than one table. So, we'll need another table to join with "tblBookings" to produce the in-between dates for each room reservation.

For this technique, we'll create a table with the following structure:

Table of numbers



Note: Some developers refer to this table as a "Tally" table.

And we just need to populate it with sequential numbers starting with zero up to the largest number required for the result of the query, which in this case is the longest time a room could be reserved, as shown below:

Populate tblNumbers with sequential numbers



Now that we have our two tables, we just need to construct the Cartesian Query. The SQL statement we need looks like this:

SELECT tblBookings.BookingID, tblBookings.RoomID,
          DateAdd("d",[TheNumber],[CheckInDate]) AS DateOccupied
FROM tblBookings, tblNumbers
WHERE (((DateAdd("d",[TheNumber],[CheckInDate]))<[CheckOutDate]))
ORDER BY DateAdd("d",[TheNumber],[CheckInDate]);

If you run that query, you will get the result shown in the second image above. You can now use this query to produce the number of days/nights that the room was reserved for each  month either by using a Totals query or a Crosstab query.

Note: We can also use a table of dates and a Non-Equi Join to produce the same result but it's not nearly as flexible as using a Cartesian Query. In fact, you can re-use tblNumbers in other Cartesian Queries to suit various situations.

To see the actual discussion at UtterAccess on this topic, click here.





Original post date: February 3, 2013

Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query. Usually, we want the query to return only the matching records between the tables, in which case an “inner join” would be used.

Sometimes, the desired result is for the query to return all the records from one table with the matching records from the related table. In these cases, an “outer join” (left or right, Access does not support “full outer join”) would be the join of choice.
However, there will be times when none of these join types will return the desired result. In which case, the only thing left to do is to not use a join at all. That’s when you get a ”Cartesian Query.”

Basically, a Cartesian Query returns a set of data that include all the records from all the tables in the query with a slight difference, the number of records returned will be the cartesian product between the number of records from all the tables.
What exactly does that mean? To better illustrate, take a look at the following sample data.

Table 1

Table 2

Cartesian Query

As you can see, the query returned as many records in Table2 for each record in Table1 (3 x 3 = 9). Here’s the SQL statement used to create that query:

SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
FROM Table1, Table2;

You will notice that there was no relationship join used between Table1 and Table2.

So, why would anyone want to use a Cartesian Query? Believe it or not, there are many valid reasons for using a Cartesian Query. For example, you can use a Cartesian Query to print a shipping label to indicate the box or pallet number within the number of boxes or pallets per shipment, or to display all possible network addresses within an IP range, or to calculate the fractional cost of a product over its life expectancy, or to generate a daily roster for student attendance or employee training or meeting.

One last note to remember when using a Cartesian Query: the resulting data set will not be updateable. So, the result of Cartesian Queries is usually used only as a data source for reports or other queries.

Hopefully, this post helped you become a little bit familiar with Cartesian Queries so that maybe next time when you need to generate records that are not normally stored in your table, you won’t be afraid to try it. Who knows, using a Cartesian Query could provide the solution you were looking for.

In my next post, I will show you one practical use of a Cartesian Query by generating all the calendar dates within a user defined range. See you next time…

Original post date: January 20, 2013