Microsoft KB Archive/304321

= How to use DDL to change the data type of a field =

Article ID: 304321

Article Last Modified on 6/30/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q304321



This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article describes how to programmatically change the data type of a field at run time.



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 &quot;AutoNumber Replication,&quot; to &quot;HyperLink,&quot; or to &quot;Lookup&quot; 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.   Create a module, and then type the following line in the &quot;Declarations&quot; section if it is not already there: Option Explicit   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 &quot;ALTER TABLE [&quot; & TblName & &quot;] ALTER COLUMN [&quot; & FieldName & _ &quot;] &quot; & DataType Else DoCmd.RunSQL &quot;ALTER TABLE [&quot; & TblName & &quot;] ALTER COLUMN [&quot; & FieldName & _ &quot;] &quot; & DataType & &quot;(&quot; & Size & &quot;)&quot; End If End Sub

  To test this function, type the following line in the Immediate window, and then press ENTER: AlterFieldType &quot;Employees&quot;,&quot;Country&quot;,&quot;TEXT&quot;,&quot;50&quot; 

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

