ADD Column will not show CheckBox

Question

Hello,

Using Access database engine + oledbreader on mdb, this code will add a new boolean yes/no column:

New OleDbCommand("ALTER TABLE BlahTable ADD Column BlahOption BIT NOT NULL", strConnection) :: ExecuteNonQuery

This will add a yes/no column and it works, but when I open the mdb in Access 2010 I won't see the CheckBox for that field, and a TextBox is shown instead, what did I miss? :(

Thanks.

Answer 1

Set the field's DisplayControl property. Reference the Microsoft Access xx.x ObjectLibrary (COM) and run the following code.

Imports Access = Microsoft.Office.Interop.Access
Imports Microsoft.Office.Interop.Access.Dao
Imports System.Runtime.InteropServices

Module Module1

    Sub Main()

        Dim acc As New Access.Application
        acc.OpenCurrentDatabase("your database file")
        Dim db As Database = acc.CurrentDb
        Dim tableDefs = db.TableDefs
        Dim tableDef As TableDef = tableDefs("BlahTable")
        Dim fields As Fields = tableDef.Fields
        Dim field As Field = fields("BlahOption")
        Dim props As Properties = field.Properties
        Try
            props.Delete("DisplayControl")
        Catch ex As Exception

        Finally
            Dim newProp As Access.Dao.Property =
                                 field.CreateProperty("DisplayControl",
                                                      DataTypeEnum.dbInteger,
                                                      Access.AcControlType.acCheckBox)
            props.Append(newProp)
            ReleaseComObject(newProp)
        End Try
        ReleaseComObject(props)
        ReleaseComObject(field)
        ReleaseComObject(fields)
        ReleaseComObject(tableDef)
        ReleaseComObject(tableDefs)
        ReleaseComObject(db)

        acc.Quit()
        ReleaseComObject(acc)

    End Sub

    Sub ReleaseComObject(Of T)(ByRef obj As T)
        If Marshal.IsComObject(obj) Then
            Marshal.ReleaseComObject(obj)
        End If
        obj = Nothing
    End Sub


End Module

Answer 2

Ok, in place of creating a whole running copy of Access (which also means the startup forms and code will run), then about the only practical way to do this is to use a reference to DAO (using the intern-op assemblies).

On the other hand, in most cases, the fact of a default data table display not showing a check box? Well, in most cases you would use a form, or even say a continues form (multiple items form), and you thus can drop in a check box for that column anyway).

However, assuming you "really" need that defaulted display as a check box?

Then in place of using COM automation (and launching that whole copy of MS-Access), then you can use the DAO database object, and add the column that way. This thus does not use a COM copy of MS-Access application, but only uses the DAO object library. So, we use DAO and use opendatabase. This ONLY uses the data engine object, and not the MS-Access object.

Note that the order of the code below is BEYOND critical, and you MUST add the column first to the table def, AND THEN create the additional DisplayControl property. You can use the same reference to the field object, but you must add the field first to the table, and THEN set the DisplayProperty.

Hence, this code works:

Assuming:

Imports Microsoft.Office.Interop.Access.Dao
```(you can add the above via NuGet if you wish).

Hence this code works:


```vb
        Dim fDbFile As String = "c:\test\test444.accDB"

        Dim dbEngine As New DBEngine
        Dim Currentdb As Database = dbEngine.OpenDatabase(fDbFile)

        Dim tblHotelsDef As Microsoft.Office.Interop.Access.Dao.TableDef

        tblHotelsDef = Currentdb.TableDefs("tblHotelsA")

        Dim NewCheckbox As Field = tblHotelsDef.CreateField("MyCheck", DataTypeEnum.dbBoolean)
        tblHotelsDef.Fields.Append(NewCheckbox)

        Dim fProp As Object = NewCheckbox.CreateProperty("DisplayControl", DataTypeEnum.dbInteger, 106)
        NewCheckbox.Properties.Append(fProp)

        Currentdb.Close()