I've Got Plenty of Nothing

<< Click to Display Table of Contents >>

Navigation:  Tables >

I've Got Plenty of Nothing

Doug Steele          

This month, Doug Steele starts by looking at a technique for finding unused fields in tables and then moves on to show how to calculate holidays.

 

I recently took over support for a database that's pretty poorly documented. I'm pretty sure that there are fields in some of the tables that aren't being used. I know that there are third-party tools that will help determine this, but is there anything I can do first before investing in such tools?

Isn't that always the way? My recommendation would be to look at a product that can do this for you, but one quick-and-dirty way would be to use a quick VBA routine that checks each field in each table to see whether it has a value for each row. If a field has Nulls in it for every row in the table, it's a good indication that the field isn't being used.

Recognize that when you define fields as part of designing a table, you have the option of specifying a default value. If you've defined a default value for a field, that field will have a value (as opposed to being Null), yet it may still indicate that the field isn't being used.

Fortunately, it's fairly straightforward to check the DefaultValue property for a field, so it's possible to check for both conditions (that is, for the case where every value for a field is Null or where every value for a field is the default value) and report on it.

In the following code, I'm strictly checking the tables in the current database, so the DCount aggregate function works well. In the first case, I'm retrieving the count of rows in the table where the specific field is not Null. In the second case, I'm retrieving the count of rows in the table where the specific field is not the default value. If the count is 0, then the field (at least potentially) isn't being used. You need to be careful assuming that a field isn't being used just because the field has the default value for all rows; that's often legitimate. For instance, the application could have been set up to handle multiple currencies, but all transactions to date may have been in a single currency.

Here's the code for those tests:

Dim dbCurr As DAO.Database

Dim fldCurr As DAO.Field

Dim tdfCurr As DAO.TableDef

Dim lngDefault As Long

Dim lngNotNull As Long

Dim strDefault As String

  Set dbCurr = CurrentDb()

  For Each tdfCurr In dbCurr.TableDefs

    If (tdfCurr.Attributes And dbSystemObject) _

        = 0 Then

      For Each fldCurr In tdfCurr.Fields

        lngNotNull = DCount("*", _

          "[" & tdfCurr.name & "]", _

          "[" & fldCurr.name & "] IS NOT NULL")

          strDefault = fldCurr.DefaultValue & ""

        If Len(strDefault) > 0 Then

          lngDefault = DCount("*", _

            "[" & tdfCurr.name & "]", _

            "[" & fldCurr.name & "] <> " &

            strDefault)

        Else

          lngDefault = -1

        End If

        If lngNotNull = 0 Then

          Debug.Print "Field " & _

            fldCurr.name & " in Table " & _

            tdfCurr.name & _

            " has no value in any rows."

        ElseIf lngDefault = 0 Then

          Debug.Print "Field " & _

            fldCurr.name & " in Table " & _

            tdfCurr.name & _

            " only has the default " & _

            "value (" & strDefault & _

            ") for all rows."

        End If

      Next fldCurr

    End If

  Next tdfCurr

If you check the database in this month's download, you'll see that I've used slightly different code than I've used here. The code in the download database can check other databases, rather than requiring you to copy this code into each database you want to check.
 

Second part of the Access Answers:  In your September 2004 column ("Working All Day"), you talked about requiring a table of holidays in order to be able to include them in addition to weekends when calculating working days. Is there a way to automate creating such a table, so that I don't need to repopulate it each year?   Did Someone Say Holiday Read this article

 

Your download file is called 506STEELE.ZIP in the file SA2005-06down.zip

This can be purchased with all the other downloads on this page