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!

List Table Fields

I recently needed a way to list all the table fields from an Access database project, so I could export it into Excel and format it for the user's review (just to make sure I covered all the user's requirements). Unfortunately, all I found, looking around for a utility to do it, was a way to print a report showing all the fields. Unless I simply miissed it, I couldn't find one that exports it to Excel for further consumption. So, I decided to create this demo.

This demo is similar to my other "list" demos where you get to browse for the database file and clicking a button will populate a temporary table with all the information gathered from the selected database. You can then export the table's data into Excel, as desired. The only difference with this new version is that it should be able to handle a password-protected file. If the selected file is password protected, the user is presented with a prompt to enter the password.

To download this demo, please click on the image below. As usual, if you have any comments or suggestions for improvements or found any bugs with this demo, please do not hesitate to let me know. I would really appreciate it.

(click on the above image to download the demo file)

Thank you for reading!

Normalization

This month, I decided to go back to basics. I know there are already plenty of articles regarding this subject. But since I am about to give a lecture about this topic to a small group of people this week, I thought I might as well post it here, so I can use it as a quick reference in the future.

What is Normalization?

Normalization is the process of designing a database table structure that organizes the data to minimize data redundancy and facilitate data integrity. The main objective of "normalizing" the data is to isolate the information so modifications to the data can be performed in just one table and then propagate throughout the database by means of related foreign keys.
There are defined "rules" to follow when going through the normalization process. When a database is properly normalized, it is considered to be in "normal form." For example, a database complying with the first rule is said to be in "First Normal Form (1NF)." Although there are several levels or rules for normalizing a database, most developers will attempt to comply only up to the "Third Normal Form (3NF)." 3NF is considered to be the highest level necessary for most database applications in use today.

First Normal Form (1NF)

A database must comply with the following rules to be considered 1NF:

1. Eliminate repeating groups in individual tables
2. Create a separate table for each set of related data
3. Identify each set of related data with a primary key

Example: The following table violates 1NF.

Second Normal Form (2NF)

A database must comply with the following rules to be considered 2NF:

1. All the rules for 1NF
2. Create separate tables for sets of values that apply to multiple records (duplicates)
3. Relate these tables with a foreign key

Example: The following table is in 1NF but violates 2NF:

Third Normal Form (3NF)

A database must comply with the following rules to be considered 3NF:

1. All the rules for 1NF and 2NF
2. Eliminate fields that do not depend on the key

Example: The following table is in 2NF but violates 3NF:

How can I remember all that?

Here are the main ideas to help you remember each normal form:

1. 1NF - Eliminate repeating groups (e.g. Price1, Price2, Price3)
2. 2NF - Eliminate duplicate or redundant data (e.g. multiple records with same values)
3. 3NF - Ensure all fields in the table are dependent on the primary key (e.g. city is dependent on the venue and not on the artist)

How do I fix this table structure?

The following tables show a properly normalized structure:

If you can think of a better example, please let me know. Thank you.

Original post date: January 26, 2016

Temporary Database

In an earlier blog, I mentioned about writing an article on how to use a temporary database for manipulating data that does not cause the front end to increase in file size. Unfortunately, I got side-tracked and forgot about it until recently when someone reminded me about what I said and requested that I write the article now. I apologize for the long delay and hope that this article and the attached demo will help clarify what I meant back then.

To recap, it is sometimes unavoidable to use temporary tables as a working space to manipulate the data before it can be consumed to represent its final form. However, doing so results in the size of the database file to increase almost exponentially each time this happens. It then becomes necessary to perform a compact and repair at a regular interval to keep the file size to a minimum and prevent reaching the 2GB limit.

As an alternative, the working data can be moved to a separate and temporary database file, so the size of the front end file will not be affected. That is what this demo file will try to illustrate. Please click on the image below to download the file.

Click here to download the demo...



How it works.

The demo contains a table with the data that is required to be manipulated. Instead of creating a temporary table, the demo creates a temporary database in the same folder as the front end file. It then copies the data from the local table into the temporary database. A linked table is then created to establish a connection between the front end and the temporary database. As the linked table is created, a security warning may display. This can be avoided by creating the temporary database in a Trusted Location. When the user closes the database, the temporary database file can be manually deleted. The demo automatically deletes the temporary database file prior to creating a new one.

Please let me know if you have any questions. Thank you.

Original post date: July 31, 2015

Should I use Make-Table or Append Query?

I have been involved in a few discussions over what causes database bloat more when it comes to using temporary tables: using a Make-Table query or just deleting the records and appending new ones?

If you are highly concerned about database bloat (the database file size steadily increases with each use) caused by using temporary tables, then the best approach is to avoid using temporary tables altogether. However, the need to use temporary tables cannot be avoided sometimes. In those cases, there are two main approaches to creating and populating a temporary table.

Make-Table Query

The simplest approach is to use a Make-Table query to dynamically create the temporary table. Of course, you will have to delete the temporary table after you're done with it because Access will throw an error when it tries to create a table that already exists.

Delete and Append Query

The other approach is to create your temporary table's structure beforehand and just populate it at runtime. With this approach, you will first run a Delete query to empty out any remaining data in the temporary table before you execute an Append query to populate the table with the necessary data.

So which one causes more bloat?

I have always thought that creating and recreating database objects over and over would cause database bloat more than just deleting and adding new data. However, some experts swear that it's actually the opposite. So, to settle the score, I have decided to create a little experiment. To see the result of my little experiment, skip to the end. To try it out for yourself, click on the image below to download the demo file I used for this experiment. By the way, the demo version was created using Access 2007.

Click here to download the demo...



Is there a better approach?

Actually, there is... If you regularly use temporary tables in your database projects, you can avoid the issue of database bloat by using a temporary database instead of temporary tables. I will elaborate more on this approach in my next blog; but essentially, you can create a temporary database with all the temporary tables in it and just link to them from within your current database. Once you are done processing the data, you can simply delete the temporary database file since you will create a new one next time you need it.

My test results

Based on my little experiment, I was able to confirm that both methods (make-table and append query) result in database bloat. And what's interesting to me was that both methods produced about the same amount of bloat, as you will see in the results table if you run the experiment yourself. What I did find significant in my experiment was that using a make-table query ran so much faster than using an append query. So, based on this experience, I will probably change my tune and recommend using the make-table approach over the append query.

Additional info

Please note that I used the term "database bloat" very loosely in this article. Some experts do not consider creating records on purpose as database bloat because we all know that adding records is supposed to increase the file size of the Access database.
If you have any comments or questions, please let me know.

Original post date: June 24, 2013