Modify table structure using code 1

[evalesthy]

I want to distribute a routine to modify an existing database table structure using code. I got one part to work - in short, it looks like this:
Set tdf = db.TableDefs![Subscriber Master]
Set fldSSD = tdf.Fields("SeasonalStartDate"
fldSSD.ValidationRule = ""

However, I cannot modify the existing text data type FieldSize (from 18 to 20) or an existing input mask on another text field (from ###### to CCCCCC). Any suggestions? Thanks.

 

[ghubbell]

Hi evalesthy,
In F1 VB Help (search for CreateField Method) then 'see also' and you should find this:

ALTER TABLE Statement
Modifies the design of a table after it has been created with the CREATE TABLE statement.

Note The Microsoft Jet database engine does not support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases. Use the DAO Create methods instead.

Syntax
ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

The ALTER TABLE statement has these parts:

Part Description
table The name of the table to be altered.
field The name of the field to be added to or deleted from table. Or, the name of the field to be altered in table.
type The data type of field.
size The field size in characters (Text and Binary fields only).
index The index for field. For more information on how to construct this index see CONSTRAINT Clause.
multifieldindex The definition of a multiple-field index to be added to table. For more information on how to construct this index see CONSTRAINT Clause.
indexname The name of the multiple-field index to be removed.

Remarks
Using the ALTER TABLE statement you can alter an existing table in several ways. You can:

Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 25-character Text field called Notes to the Employees table:
ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

You can also define an index on that field. For more information on single-field indexes see CONSTRAINT Clause.

If you specify NOT NULL for a field then new records are required to have valid data in that field.

Use ALTER COLUMN to change the data type of an existing field. You specify the field name, the new data type, and an optional size for Text and Binary fields. For example, the following statement changes the data type of a field in the Employees table called ZipCode (originally defined as Integer) to a 10-character Text field:
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)

Use ADD CONSTRAINT to add a multiple-field index. For more information on multiple-field indexes see CONSTRAINT Clause.
Use DROP COLUMN to delete a field. You specify only the name of the field.
Use DROP CONSTRAINT to delete a multiple-field index. You specify only the index name following the CONSTRAINT reserved word.

Notes
You cannot add or delete more than one field or index at a time.
You can use the CREATE INDEX statement to add a single- or multiple-field index to a table, and you can use ALTER TABLE or the DROP statement to delete an index created with ALTER TABLE or CREATE INDEX.
You can use NOT NULL on a single field or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field. Attempting to apply this restriction more than once restuls in a run-time error.

See Also
ADD USER Statement CREATE USER or GROUP Statement
ALTER USER or DATABASE Statement CREATE VIEW Statement
CONSTRAINT Clause DROP Statement
CREATE INDEX Statement DROP USER or GROUP Statement
CREATE PROCEDURE Statement GRANT Statement
CREATE TABLE Statement REVOKE Statement

Examples:

ALTER TABLE Statement Example
This example adds a Salary field with the data type Money to the Employees table.
Sub AlterTableX1()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot
' Add the Salary field to the Employees table
' and make it a Money data type.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary MONEY;"
dbs.Close
End Sub

This example changes the Salary field from the data type Money to the data type Char.
Sub AlterTableX2()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot
' Add the Salary field to the Employees table
' and make it a Money data type.
dbs.Execute "ALTER TABLE Employees " _
& "ALTER COLUMN Salary CHAR(20);"
dbs.Close
End Sub

This example removes the Salary field from the Employees table.

Sub AlterTableX3()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot
' Delete the Salary field from the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;"
dbs.Close
End Sub

This example adds a foreign key to the Orders table. The foreign key is based on the EmployeeID field and refers to the EmployeeID field of the Employees table. In this example, you do not have to list the EmployeeID field after the Employees table in the REFERENCES clause because EmployeeID is the primary key of the Employees table.

Sub AlterTableX4()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot
' Add a foreign key to the Orders table.
dbs.Execute "ALTER TABLE Orders " _
& "ADD CONSTRAINT OrdersRelationship " _
& "FOREIGN KEY (EmployeeID) " _
& "REFERENCES Employees (EmployeeID);"
dbs.Close
End Sub

This example removes the foreign key from the Orders table.
Sub AlterTableX5()
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot
' Remove the OrdersRelationship foreign key from
' the Orders table.
dbs.Execute "ALTER TABLE Orders " _
& "DROP CONSTRAINT OrdersRelationship;"
dbs.Close
End Sub

Thanks to Microsoft for the help. Gord
gord@ghubbell.com