none
Adding a Boolean Field with True/False Format Using ADOX RRS feed

  • Question

  • I am using VB 2010 with an Access 2010 database.

    I need to have my program add a boolean field to a table. Currently, my program has the following code:

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    
    cnn.Open(My.Settings.rubsConnectionString)
    cat.ActiveConnection = cnn
    
    tbl = cat.Tables("BillResidentsHeader")
    tbl.Columns.Append("Success", ADOX.DataTypeEnum.adBoolean)
    tbl.Columns("Success").Properties("Description").Value = "Whether or not the bill was successfully processed"
    

    This adds the field, just as I want. The only problem is that it when I view the database in Access, the field does not have the same format as if I had created it there. Boolean fields created in Access have a True/False format, which shows up as a checkbox when you view the data. Boolean fields created by the code above have no format and when I view the data, the field contains a zero. So, what code can I use to create the field with the True/False format?

    Thanks.

    Monday, July 8, 2013 11:20 PM

Answers

  • The format of the column in Access should be determined by Format property of the Table Column (e.g. On/Off, Yes/No, etc.) when viewing the table in design mode. This property is not available through ADOX since it's specific to Access.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Spencersoft Wednesday, July 10, 2013 8:16 PM
    Tuesday, July 9, 2013 5:21 PM

All replies

  • The format of the column in Access should be determined by Format property of the Table Column (e.g. On/Off, Yes/No, etc.) when viewing the table in design mode. This property is not available through ADOX since it's specific to Access.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Spencersoft Wednesday, July 10, 2013 8:16 PM
    Tuesday, July 9, 2013 5:21 PM
  • It's disappointing that I can't set the format in my program. At least, this was just a minor issue.

    Thank you for your response.

    Wednesday, July 10, 2013 8:16 PM
  • Not with ADOX. You would have to use DAO and set the property through the Fields collection of the TableDef object.

    There are DAO examples at the below link that demonstrates how to create TableDefs, Fields and set Properties.

    http://allenbrowne.com/func-DAO.html


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 10, 2013 8:26 PM