How to use DDL to change the data type of a field (304321)

MORE INFORMATION

You can use the ALTER COLUMN statement with the Microsoft Jet 4.0 database engine to change the data type of a field without having to create a separate field. In DAO, you used to have to create a separate field.

The ALTER COLUMN statement has three parts as follows:

• The name of the existing field
• The new data type
• An optional size for the text field and for the binary field

You can use the ALTER COLUMN statement as follows.

Note You cannot change the data type of an existing field to "AutoNumber Replication," to "HyperLink," or to "Lookup" by using a Microsoft Access DDL SQL statement. These field types are not native Jet-field types. Therefore, these field types can be created and used only by the Access user interface.

1. Create a module, and then type the following line in the "Declarations" section if it is not already there:

   Option Explicit
   					

2. Put the following procedure in the module:

    
   'The AlterFieldType Sub procedure requires three string parameters with an
   'optional fourth. The first string specifies the name of the table
   'that contains the field to be changed. The second string specifies the
   'name of the field to be changed. The third is the data type of the field.
   'The fourth and optional parameter holds the size value when you specify
   'binary data types and text data types.
   
   Sub AlterFieldType(TblName As String, FieldName As String, DataType As _
      String, Optional Size As Variant)
   Dim cd
   If IsMissing(Size) Then
   DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
      "] " & DataType
   Else
   DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
      "] " & DataType & "(" & Size & ")"
   End If
   End Sub
   					

   
   
3. To test this function, type the following line in the Immediate window, and then press ENTER:

   AlterFieldType "Employees","Country","TEXT","50"
   					

Notice that this changes the Country field in the Employees table to a text field with a field length of 50 characters.