Microsoft KB Archive/210304

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

Article ID: 210304

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210304



For a Microsoft Access 97 and earlier version of this article, see 128016.

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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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



MORE INFORMATION
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.

 Start Microsoft Access, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp. Note the data type of the Extension field in the Employees table. Create a new module, and then type Option Explicit in the Declarations section if it is not already there.  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  On the View menu, click Immediate Window. 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.</li></ol>

Additional Notes About the Code
<ul> 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].

</li> 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.

</li> 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'.

</li> Data type changes can take a long time if you have a lot of data in the table.</li></ul>

<div class="references_section">