18. Create Tables Faster

Optimize table design by changing the design defaults to match your needs.

A Text field is 50 characters. A Number field is a long integer, and the default value is 0. Sound all too familiar? How often have you gone out of your way to alter these defaults? Well, with this hack, you no longer need to.

In the Options dialog box (Tools Options), on the Tables/Queries tab, you'll find settings for selecting the default size for text fields, the default number type (integer, long, single, etc.) for number fields, and even the overall default type. Figure 2-22 shows this dialog box and the settings.

Figure 2-22. Changing field defaults

In Figure 2-22, the default Text field size has been changed to 100. This means that as new text fields are added to the design of a table, they will default to a size of 100. Also, the default Number type has been set to Single. As new number fields are added, they default to the Single datatype. The overall default field type is set to Number; therefore, as new fields are entered into a table design, they default to a Number field typeand that type will be of the Single number type.

Altering these design defaults can be quite useful. If, for example, you are designing a table that predominantly contains dates, set the default field type to Date/Time, and save yourself a lot of field-type selection. As you enter new fields, they will default to Date/Time. You will need to adjust only the minority of fields that aren't of this type.

2.7.1. Setting Default Values

The settings in the Options dialog box control field type settings but offer nothing to indicate default values. In other words, you can select Single as the default number type, but you can't specify that the field defaults to a value of 1.25 (for example) as new records are added to the table.

However, a setting is available in which you can indicate a default value. The field in the third row of the table being designed in Figure 2-23 has been manually set to 1.25, and this becomes the default value for the field.

Figure 2-23. Setting a default field value

2.7.2. The Code

What if 100 other fields need to be set to such a default value? My fingers hurt just thinking about the manual entry that would be involved! Automating this task will be a lifesaverwell, at least a finger saver. A little code to the rescue!

	Sub change_field_defaults()
      Dim cat As ADOX.Catalog
      Set cat = New ADOX.Catalog
      Dim fld As ADOX.Column
      cat.ActiveConnection = CurrentProject.Connection
      For Each fld In cat.Tables("myNewTable").Columns
        If fld.Type = adSingle Then
          fld.Properties("Default").Value = 1.25
        End If
      Next
      Set cat = Nothing
    End Sub

This code uses the ADOX library [Hack #17] to work with the fields in the designated table. In this example, the table name is hardcoded, but it certainly can be passed as an argument. This code example cycles through the fields, and when a field type is designated as Single (indicated by the adSingle constant), its default value is set to 1.25.

You can expand this code routine to set default values for all possible field types. Even more, you can set default values for combinations of field types and field names. For example, a field named local_rate can be a single field type, which you can set it to a default value of .25; likewise, you cab set a field named national_rate, also a Single datatype, to have a default value of .5.