mlcktmguy
asked on
Use code to Add Column to Table, How to make nullable
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:
Dim tbl
Set tbl = cat.Tables(passedTable)
'
With tbl
.Columns.Append passedField, adVarWChar, passedLength
End With
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:
Dim tbl
Set tbl = New ADOX.Table
With tbl
.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").Attrib utes = adColNullable
.Columns("DetectOp").Attri butes = adColNullable
.Columns("Base").Attribute s = adColNullable
.Columns("UpgradeVal").Att ributes = adColNullable
.Columns("Upgrade").Attrib utes = adColNullable
End With
cat.Tables.Append tbl
Dim tbl
Set tbl = cat.Tables(passedTable)
'
With tbl
.Columns.Append passedField, adVarWChar, passedLength
End With
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:
Dim tbl
Set tbl = New ADOX.Table
With tbl
.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").Attrib
.Columns("DetectOp").Attri
.Columns("Base").Attribute
.Columns("UpgradeVal").Att
.Columns("Upgrade").Attrib
End With
cat.Tables.Append tbl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/questions/10297409/ADOX-adding-columns.html