ACC2000: How to Use Code to Change a Field's Data Type at Run Time

You can use ActiveX Data Objects (ADO) and Data Definition Language (DDL) in Visual Basic for Applications code to change the data type of a field in an Access database (.mdb) or an Access project (.adp).

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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

To create and use the sChangeDataType procedure, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. Note the data type of the Extension field in the Employees table.
3. Create a new module, and then type Option Explicit in the Declarations section if it is not already there.
4. Type or paste the following code in the module:

   Sub sChangeDataType()
       Dim cnn As ADODB.Connection
       Dim strSQL As String
       Set cnn = CurrentProject.Connection
       strSQL = "ALTER TABLE [Employees] ALTER COLUMN Extension int"
       cnn.Execute strSQL
   End Sub
   					

5. On the View menu, click Immediate Window.
6. In the Immediate window, type the following line, and then press ENTER:
   sChangeDataType
   Note the changes to the Extension field in the table's design.

Additional Notes About the Code

• If the code cannot convert the values in the field to the new data type, you receive the following message, and the data type change will not occur:
  
  In an Access database (.mdb):
  Data type mismatch in criteria expression.
  In an Access project (.adp):
  Syntax error converting the [olddatatype] value [value] to a column of data type [newdatatype].
• If you set a column size smaller than the data currently in the field, you receive the following message, and the data type change will not occur:
  
  In an Access database (.mdb):
  The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
  In an Access project (.adp):
  String or binary data would be truncated.
• Microsoft Access prohibits the conversion of any field data type to the AutoNumber data type. You receive the following message if you try to change the column by using the IDENTITY keyword:
  
  In an Access database (.mdb):
  Syntax error in ALTER TABLE statement.
  In an Access project (.adp):
  Incorrect syntax near the keyword 'IDENTITY'.
• Data type changes can take a long time if you have a lot of data in the table.

For more information about changing a field's data type, click Microsoft Access Help on the Help menu, type change a field's data type in the Office Assistant or the Answer Wizard, and then click Search to view the topic.