ACC: How to Change a Field's Data Type at Run Time with Code

ID: Q128016

The information in this article applies to:

Microsoft Access 2.0, 7.0, 97

SUMMARY

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

The DataType property of a field is not available in Visual Basic for Applications, and the Size property of a field is read-only in a TableDef object. Therefore, the data type of a field cannot be changed directly by using Visual Basic for Applications at run time. However, you can change the data type of a field by using a combination of Data Access Objects (DAO) and Data Definition Language (DDL) in Visual Basic for Applications code.

This article shows you how to create a sample user-defined Sub procedure called AlterFieldType that uses DAO and DDL to change the data type of a field at run time.

NOTE: You will have to delete and rebuild any relationships and indexes that involve the changed field after you use this procedure.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following example shows you how to create and use the AlterFieldType

Sub procedure, which uses DAO to create a data definition query to change

the data type of a field:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

2. In the Database window, make a copy of the Products table and name it

   PROD1.

3. Create a new module with the following line in the Declarations

   section if it is not already there:

      Option Explicit

4. Enter the following code in the module.

   NOTE: In the following sample code, an underscore (_) at the end of a
   line is used as a line-continuation character. Remove the underscore
   from the end of the line when re-creating this code in Access Basic.

   NOTE: When using this code to change a field type to "date/time", it is 
   important that the NewDataType of date/time be specified as 
   "datetime", NOT "date/time" as this code will fail due to the  
   slash ("/").

      '*****************************************************************
      ' The AlterFieldType Sub procedure requires three string
      ' parameters. The first string specifies the name of the table
      ' containing the field to be changed. The second string specifies
      ' the name of the field to be changed. The third string specifies
      ' the new data type for the field.
      '*****************************************************************

      Sub AlterFieldType (TblName As String, FieldName As String, _
         NewDataType As String)
         Dim db As Database
         Dim qdf As QueryDef
         Set db = CurrentDb()

         ' Create a dummy QueryDef object.
         Set qdf = db.CreateQueryDef("", "Select * from PROD1")

         ' Add a temporary field to the table.
         qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN_
            AlterTempField " & NewDataType
         qdf.Execute

         ' Copy the data from old field into the new field.
         qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET_
            AlterTempField = [" & FieldName & "]"
         qdf.Execute

         ' Delete the old field.
         qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN ["_
            & FieldName & "]"
         qdf.Execute

         ' Rename the temporary field to the old field's name.
         db.tabledefs("[" & TblName & "]").Fields("AlterTempField")._
            Name = FieldName
         ' Clean up.
      End Sub

5. On the View menu, click Debug Window (or Immediate Window in

   version 2.0).

6. In the Debug window, type the following line, and then press ENTER:

      AlterFieldType "PROD1", "UnitsInStock", "LONG"

   NOTE: In Microsoft Access 2.0, there are spaces in the Units in Stock
   field name.

   The AlterFieldType Sub procedure changes the data type of the Units
   In Stock field in the PROD1 table from Integer to Long Integer.

Notes

If Microsoft Access cannot convert a particular value to the new data type, it deletes the value, setting the field to null for all records containing the value.
If you convert the data type of a field to a data type with a smaller field size, Microsoft Access truncates any data longer than the allowable field size.
Microsoft Access prohibits the conversion of any field data type to the AutoNumber (or Counter in version 2.0) data type. So, instead of changing a data type to AutoNumber, add an AutoNumber field to your table.
In large tables, changing a data type may take a long time.

REFERENCES

For more information about modifying tables using DDL, search the Help Index for "ALTER TABLE."

Additional query words: programming modify design

Keywords          : kbprg PgmObj 
Version           : WINDOWS:2.0,7.0,97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: February 26, 1999