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

Posted on 2006-05-17
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

    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


    LVL 26

    Expert Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now