Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

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").Attributes = adColNullable
    .Columns("DetectOp").Attributes = adColNullable
    .Columns("Base").Attributes = adColNullable
    .Columns("UpgradeVal").Attributes = adColNullable
    .Columns("Upgrade").Attributes = adColNullable
End With

cat.Tables.Append tbl
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial