How to create an MS Access table with a checkbox field in VBA

Question

I want to create a MS Access table dynamically with a checkbox field in VBA. Setting the field property to checkbox works with an existing table:

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("myTable")
Set fld = tdf.CreateField("myField", dbBoolean)
tdf.Fields.Append fld
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, CInt(acCheckBox))

However, when the table is created dynamically, it does not work.

Set dbs = CurrentDb()
Set tdf = dbs.CreateTableDef("myTable")
Set fld = tdf.CreateField("myField", dbBoolean)
tdf.Fields.Append fld
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, CInt(acCheckBox))
dbs.TableDefs.Append tdf

Does anybody know what is wrong with this code? Or if it is possible at all to set the checkbox property when the table is not yet created.

Gustav · Accepted Answer · 2022-04-28 12:29:10Z

2

This works for me:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set dbs = CurrentDb()
Set tdf = dbs.CreateTableDef("myTable")

Set fld = tdf.CreateField("myField1", dbBoolean)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)

Set fld = tdf.CreateField("myField2", dbBoolean)
tdf.Fields.Append fld
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)

edited Apr 28, 2022 at 12:29

answered Apr 28, 2022 at 12:03

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

esims Over a year ago

That is setting the checkbox property after the table is created, like in my first code example. My question was if setting this property is also possible before the table is created.

CHill60 Over a year ago

@esims - And the answer to that question is ... no.

Gustav Over a year ago

That's how to do it - using a single Field object once.

esims Over a year ago

So it requires looping your fields twice; the first time to create them, and a second time to change posible checkbox fields.

Gustav Over a year ago

Be a bit creative. See extended answer, please.

Collectives™ on Stack Overflow

How to create an MS Access table with a checkbox field in VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related