ACC: How to Alter Field's Data Type at Run Time Using Code

• Microsoft Access 2.0, 7.0, 97

SUMMARY

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

A field's DataType property is not available using Visual Basic for Applications, and its Size property is read-only in a TableDef object. Therefore, a field's data type cannot be altered directly using Visual Basic for Applications at run time. However, you can alter a field's data type using a combination of data access objects (DAO) and Data Definition Language (DDL) in Visual Basic for Applications code.

This article demonstrates how to create and use a sample user-defined Sub procedure called AlterFieldType that uses DAO and DDL to alter a field's type at run time.

NOTE: You will have to delete and rebuild any relationships and indexes that involve the altered field after using 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 demonstrates how to create and use the AlterFieldType Sub procedure, which uses DAO to create a data definition query to alter a field's data type:

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:

         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.

         '*****************************************************************
         ' 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 Units in Stock

   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 Counter data type. So, instead of changing a data type to Counter, add a Counter 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."