Use code to Add Column to Table, How to make nullable
Posted on 2006-05-17
Whenever I have to alter existing tables in the MSJet (4.0) database of a VB6.0 app that is already in production, I do it using code such as:
Set tbl = cat.Tables(passedTable)
.Columns.Append passedField, adVarWChar, passedLength
passedTable, passedField and passedLength are set to the appropriate values prior to this.
This way it is transparent to the user. Sometimes I have to initialize the new field to a value (which I do using code) but sometimes the field can be left blank. When I leave the fiield blank I run into problems when I create a new record without specifically putting something into the new field. It is the 'Nulls not allowed" error.
Setting the 'allow nulls' property to yes at the time I append the field to the table would eliminate this problem but I don't know how to do that when adding a field to an existing table. How do I set the 'allow nulls' (adNullable) property when adding a field to a table?
I do it when creating a new table with the following code:
Set tbl = New ADOX.Table
.Name = TableToCheck
Set .ParentCatalog = cat
' Create fields and append them to the new Table object.
.Columns.Append "RecType", adVarWChar, 2
.Columns.Append "DetectOp", adVarWChar, 2
.Columns.Append "Base", adDouble
.Columns.Append "UpgradeVal", adDouble
.Columns.Append "Upgrade", adDouble
' set field Properties
.Columns("RecType").Attributes = adColNullable
.Columns("DetectOp").Attributes = adColNullable
.Columns("Base").Attributes = adColNullable
.Columns("UpgradeVal").Attributes = adColNullable
.Columns("Upgrade").Attributes = adColNullable