Listed Tables with Columns That all Null

  • Thread starter Thread starter Dean P.
  • Start date Start date
D

Dean P.

Please help me obtain Access (2000) script that check each table in the
database that will display all of the table columns where every row is null.

Thanks,
 
Please help me obtain Access (2000) script that check each table in the
database that will display all of the table columns where every row is null.

Thanks,
Click to expand...

Ugggghhhh.... This will take a LONG while if the database is at all large, but
give it a try. Off the top of my head, needs error trapping but works in one
of my databases:

Public Sub FindUnusedFields()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As Field
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strFind As String
Set db = CurrentDb
For Each tdf In db.TableDefs
' skip system and temp tables
If Left(tdf.Name, 1) = "~" Or Left(tdf.Name, 4) = "MSys" Then
' do nothing
Else
strSQL = "SELECT * FROM [" & tdf.Name & "];"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
For Each fld In tdf.Fields
If fld.Required = False Then ' only check nullable fields
strFind = "[" & fld.Name & "] IS NOT NULL"
rs.FindFirst strFind
If rs.NoMatch Then ' no non-null values found
Debug.Print "Table "; tdf.Name & " field " _
& fld.Name & " is all NULL"
End If
End If
Next fld
End If
Next tdf
End Sub
 
Back
Top