Changing the Data Type of a Table Column

You can use the ALTER COLUMN clause in the ALTER TABLE statement to change the data type of a table column. You must specify the name of the field, the desired data type, and the size of the data type, if required. The procedure in Hands-On 19-7 changes the data type of the SchoolId field in the tblSchools table from AutoNumber to a 15-character Text field.

Part III

©Hands-On 19-7: Changing the Field Data Type

This hands-on uses the tblSchools table created in Hands-On 19-2.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the ChangeFieldType procedure as shown below.

Sub ChangeFieldType()

Dim conn As ADODB.Connection Dim strTable As String Dim strCol As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "tblSchools" strCol = "SchoolId"

conn.Execute "ALTER TABLE " & strTable & _ " ALTER COLUMN " & strCol & " CHAR(15);"

ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

0 0

Post a comment