Add Yes/No column using Access 2003 VBA

[Webkins]

I am trying to add a column to my table using VBA. The data type is Yes/No but I connot figure out how to define the data type. I am trying to use this code:

DoCmd.RunSQL "alter table tbl_words add column [" & NewCategoryTable & "] yes/no;"

Any suggestions ? Thank you so much in advance.

 

[dhookom]

Try use

DoCmd.RunSQL "ALTER Table tbl_words add column [" & NewCategoryTable & "] bit"

Duane
Hook'D on Access
MS Access MVP

 

[Webkins]

That works kind of but now I need to set the format to Yes/No. It does not appear as a yes/no check box within the table. Thank you.

 

[dhookom]

I rarely if ever format a field in the table design. I leave the formatting to the controls on forms and reports. I expect you would need some DAO code to modify the format property. I doubt this can be done through SQL.

Duane
Hook'D on Access
MS Access MVP

 

[Webkins]

This works to set the data type to Yes/No:

DoCmd.RunSQL "alter table tbl_words add column [" & NewCategoryTitle & "] yesno"

But I do not get the little square check box to appear in the table.

How do I set the format under the General tab to Yes/No also ?

 

[randy700]

As dHookom mentioned, the little square box is only a format. Why do you need it in the table? The table is a
location to hold your DATA. Use formatting tools on the forms.

Randy

 

[RoyVidar]

First, adding another column through code, as this, with a generic name on the column, and a fixed table, indicates to me there might be a denormalized structure on the table.

If so, you should consider normalizing it.

To change the format of the field at table level, you should be able to do something like this:

[tt]currentdb.tabledefs("tbl_words").fields(NewCategoryTable).properties("Format").Value = "Yes/No"[/tt]

Or mayhaps do the whole thing in dao...

[tt] Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fl As DAO.Field
Dim pr As DAO.Property

Set db = CurrentDb
Set td = db.TableDefs("tbl_words")
Set fl = td.CreateField(NewCategoryTable, dbBoolean)
td.Fields.Append fl
Set pr = fl.CreateProperty("Format", dbText, "Yes/No")
fl.Properties.Append pr

Set pr = Nothing
Set fl = Nothing
Set td = Nothing
Set db = Nothing[/tt]

Roy-Vidar