Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to use DDL to change the data type of a field

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

View products that this article applies to.

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.

↑ Back to the top

Summary

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

↑ Back to the top

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.

↑ Back to the top

References

For more information about how to use DLL to change tables, click Microsoft Access Help on the Help menu, type alter table in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top

Keywords: KB304321, kbhowto

↑ Back to the top

Article Info
Article ID : 304321
Revision : 2
Created on : 6/30/2004
Published on : 6/30/2004
Exists online : False
Views : 234