I've Got Plenty of Nothing |
  
|
I've Got Plenty of Nothing |
|
Doug Steele Gold Collection
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 is found in the Gold Collection on this page