Adding New Fields to a Table

Use the ALTER TABLE statement followed by a table name to modify the design of a table after it has been created with the CREATE TABLE statement. Prior to modifying the structure of an existing table, it's recommended that you make a backup copy of the table.

Programming with the Jet Data Definition Language

The ALTER TABLE statement can be used with the ADD COLUMN clause to add a new field to the table. For example, the procedure in Hands-On 19-6 adds a Currency field called Budget2004 to the tblSchools table with the following statement:

ALTER TABLE tblSchools ADD COLUMN Budget2004 MONEY

When you add a new field to a table you should specify the name of the field, its data type and, for Text and Binary fields, the size of the data type.

©Hands-On 19-6: Adding a New Field to an Existing Table

1. Run the procedure in Hands-On 19-2 to create the tblSchools table in the current database.

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

3. In the module's Code window, enter the AddNewField procedure as shown below.

Sub AddNewField()

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

On Error GoTo ErrorHandler

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

conn.Execute "ALTER TABLE " & strTable & _ " ADD COLUMN " & strCol & " MONEY;" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

0 0

Post a comment