Changing the Size of a Text Column

It's easy to increase or decrease the size of a Text column. Simply use the ALTER TABLE statement followed by the name of the table, and the ALTER COLUMN clause followed by the name of the column whose size you want to modify. Next, specify the data type of the column and the new column size. Hands-On 19-8 modifies the size of the SchoolName field from the default 255 characters to 40.

©Hands-On 19-8: Changing the Size of a Field

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 ChangeFieldSize procedure as shown below.

Sub ChangeFieldSize()

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

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection

Creating, Modifying, and Deleting Tables and Fields | 405 Programming with the Jet Data Definition Language strTable = "tblSchools" strCol = "SchoolName"

conn.Execute "ALTER TABLE " & strTable & _

" ALTER COLUMN " & strCol & " CHAR(40);" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

0 0

Post a comment