Setting property to yes/no using alter table

[jgc31]

I want to add a column to an existing table using the sql statement Alter table. The new column needs to be a yes/no type. The following code almost works but it only sets the column to a general logical type not specifically the yes/no check box .

DoCmd.RunSQL "Alter Table TblIssues Add [" & Issue & "] logical;"

Any ideas?

 

[RoyVidar]

I think the checkbox is a formatting thingie not addressed by the alter table statement. Not sure, but I think I'd try the version below:

DoCmd.RunSQL "Alter Table TblIssues Add column [" & Issue & "] bit;"

But what you could try to format this column, is to alter the property (by default it is Text Box) programaticly after altering the table, perhaps something like this?

currentdb.tabledefs("tblissues").fields(Issue).properties("DisplayControl")=accheckbox

But, you wouldn't allow your users to get anywhere near your tables, so there shouldn't really be any need for this, should there?

Edit: Wondered if I should make some further comments, and decided yes after initial posting.

Whenever I see a scheme altering tables/adding columns at runtime, I get a little uncomfortable. To me it indicates that the datastructure isn't flexible enough, perhaps not normalized - perhaps you should take a closer look at your table structure and normalization and verify that adding fields dynamicly isn't going to create future headaches

 

[jgc31]

Roy

Unfortunately the issues involved will only be defined once the database has started being used and yes this will cause all sorts of headaches. The issues could be collected as a series of notes but this would make analysing them impossible. I may drop this bit altogether

 

[RoyVidar]

Sounds ugly. Adding columns in a table, is normally breaking rules of normalization. I'm not very good at US/UK terms, but say you have a base table tblIssue, to which "something", a Yes/No Issue needs to be added at runtime. What is the relationship between one Issue and the the new fields?

Just throwing one idea out

tblIssue
IID - PK
field1
field2
...

tblSubIssues
SID
SIDesc
YourYesNo
IID - FK

Primary Key? Only the subissue ID? Combination of the foreign key field from tblIssue and perhaps a foreign key from another table?

Perhaps add another table for descritption, and let the suggested tblSubIssue table be a junction table between more tables?

Now this is probably not addressing your particular challenge, but the point is that it is almost always possible to create a datastructure (analysis/datamodelling) that will address such challenges in a data driven way - i e without altering table/datastructure at runtime.

Perhaps if you post some information about your structure, what it's supposed to address... someone with a better grasp of your buisiness rules can give a hand?

 

[jgc31]

Roy

I have pretty much given up on including the issues in the db not because of the yes/no issue which is a fairly minor programming issue but because of the more general difficulties. Basically the db is to include details of approx 500 individuals part of this ideally would be be basically a survey of what issues are important to the individuals. The issues themselves and the number of issues would not be known untill feedback was recieved from members. In an ideal world these respnses could then be analysed to show what where the most important issues to the individuals surveyed. One approach would be to create a table with a number ( say 20) of holding issues the names of which could then be changed when responses come in ( say "issue 1" to "education" ) but this is very inelegant and makes many of the forms clunky with much space taken up with redundant information. I realise that adding to the tables as an intregal part of the programme is not ideal and it also causes other problems as forms and reports etc will all need to be changed programatically to take into account the new issues but I cant see any other approach when so much information is not available when the db is created.