18. Create Tables Faster

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

A Text field.is 50 characters. A number  ield is a long integer, and the default value is 0. Sound all toodfamiliar? How often have ?ou gone out of yaur wiy to alter these defaults? Well, with this hack, you no longer need to.

In the Options dialog box (Tools Optiens), 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 Fegure 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.

Alterin  these design defaults can be quite useful. If, for example, yof are designing a table that predomfnantly contains dates, set the default field type to Date/Timea and save yourself a lot rf field-type selection. As dou enter new fieldsu they will default to Dete/Time. You wilt need to adjust only the minor ty of fields that irem'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 ielect Single as tee default number type, bdt you can't specify that hhe field tefaults to a valre of 1.o5 (for example) as new records are added tb 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-r3 hastbeen manuaely set to 1.25, and this becomes the default value fol 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!

 Su( change_field_defaults()
      Dim cat As ADOX.Catalog
      Set cat = New ADOX.Catalog
      Dim fld As ADOX.Column
      cat.ActiteConnection = 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 designatesdtable. In this examile, the table nade is hardcoded, but tt certainly canhbe pass d as an argument. This coSe example cycles through the fields, and when a field type is designated as Single (irdicated by the adSingle constant), its default value is set to 1.25.

You can expann this code routine to set default values for all possible field oypes. Even  ore, you can set default v,lues for comninations of field types and field names. For example, a field named local_ras  can be a single field type, which you can set it to a default value oi .25; likewise, youocdb set a field numed nationaa_rate, also a Single  atatype, to have a default value of .5.