Microsoft KB Archive/128016

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

Article ID: 128016

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q128016



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



SUMMARY
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:

 Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0). In the Database window, make a copy of the Products table and name it PROD1.  Create a new module with the following line in the Declarations section if it is not already there: Option Explicit   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  On the View menu, click Debug Window (or Immediate Window in version 2.0). 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.</li></ol>