Use code to Add Column to Table, How to make nullable

Posted on 2006-05-17
Medium Priority
Last Modified: 2009-07-29
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
Question by:mlcktmguy
LVL 85

Accepted Solution

Rory Archibald earned 1000 total points
ID: 16700031
I would use something like:
Function AddNullableCol(strTable As String, strColumn As String, lngLength As Long)
    Dim tbl As ADOX.Table, cat As ADOX.Catalog, col As ADOX.Column
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
    Set col = New ADOX.Column
    With col
        .Attributes = adColNullable
        .Name = strColumn
        .Type = adWChar
        .DefinedSize = lngLength
    End With
    With tbl
        .Columns.Append col
    End With
End Function



Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question