Remove Nulls in all Fields in MS Access Database

Using the code I found in the newsgroups, I also had a need to remove all Nulls from every table in my databases.

For some reason, using ADO to pull records from Access into Excel was causing problems when Nulls were present. So I need to remove them from every field of every table in my database. Well, I don't need to, but it's easier to loop through every table than to run this code on select tables.

So here's the code I used (in MS Access 2003). It loops through every table, acting on only non- linked or system tables. For each field in each table, it checks the data type and sets the appropriate default value.

Sub GetRidOfNullsAllTables()
  Dim db As DAO.Database
  Dim tbls As DAO.TableDefs
  Dim tbl As DAO.TableDef
  Dim thisTable As DAO.TableDef
  Dim SQLString As String
  Dim flds As DAO.Fields
  Dim fld As DAO.Field

  Set db = CurrentDb
  Set tbls = db.TableDefs

  ' loop through each appropriate table (i.e. no linked or system tables)
  For Each tbl In tbls
    If tbl.Attributes = 0 Then
      Set thisTable = tbl
      ' grab all fields
      Set flds = thisTable.Fields

      For Each fld In flds

        SQLString = "UPDATE [" & tbl.Name & "] SET "

        ' check field type and set to zero or empty string as appropriate
        Select Case fld.Type
          Case dbText
            SQLString = SQLString & "[" & fld.Name & "] = """" WHERE [" & fld.Name & "] Is Null;"
          Case dbDouble, dbInteger, dbLong, dbBigInt, dbDecimal
            SQLString = SQLString & "[" & fld.Name & "] = 0 WHERE [" & fld.Name & "] Is Null;"
        End Select

        Debug.Print SQLString
        ' execute update statement on field
        db.Execute SQLString, dbFailOnError
      Next fld

    End If
  Next tbl

End Sub

I know what you're thinking: where are the other data types? I don't really use them. Everything in my database is either text, or one of the basic number types. I do have some Date fields, but I don't want to touch those because there's no value I can set them to that will make them appear as anything other than dates (I'd be glad to hear otherwise), which will get them confused with the legit data. So I'm just fine with them staying Null.

You might also be asking why I'm converting numbers to zeroes, after all, isn't zero a number? In my database, zero doesn't matter since it doesn't add to the sum of anything. Trust me, it doesn't matter to my database.

Doing this, along with 'Compact and Repair' and Performance Analyzer have really served to speed up my database.