ADD Column will not show CheckBox

StewartBW 260 Reputation points
2024-04-03T02:01:31.49+00:00

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.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,218 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,568 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
818 questions
Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
1,998 questions
0 comments No comments
{count} votes

Accepted answer
  1. KOZ6.0 4,735 Reputation points
    2024-04-03T04:45:27.4133333+00:00

    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
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Albert Kallal 4,646 Reputation points
    2024-04-12T20:24:10.1966667+00:00

    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()
    
    
    
    1 person found this answer helpful.
    0 comments No comments