table modification in VB (1 Viewer)

[alexl]

I am trying to change the default value and validation rule for a field in a table as well as delete a field in a table. These are existing fields, not newly created. So far, all I have found indicates this can't be done in code. Has anyone found different? Or maybe there is a way to do this in a macro?

 

[Simon C]

Try the following:

Public Sub Alter_Table()

Dim db As Database
Dim tdf As TableDef
Dim fld As Field

Set db = CurrentDb()
Set tdf = db.TableDefs("NameOfTable")

'Where NameOfField is the name of the field whose property you are changing
tdf.Fields("NameOfField").ValidationRule = ">0"
tdf.Fields("NameOfField").DefaultValue = 72

'Loop through the table definition checking that the field to delete exists
For Each fld In tdf.Fields
If fld.Name = "NameOfField" Then
'If it does delete it
tdf.Fields.Delete fld.Name
End If
Next

Set db = Nothing

End Sub

[This message has been edited by Simon C (edited 10-01-1999).]