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

MORE INFORMATION

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.