change column format via code

[perry59]

when in design view I know I can have bool fields formatted as "yes/no" or "true/false" etc., as shown in the picture below. Is there some way to do this in code?
I already have a project which creates the database, adds tables, relations etc. but I have not been able to find a way of setting those bool fields to display as "yes no" when looking at those tables in access.
Thanks

[CJ_London]

you should not be applying any format to tables, just because you can does not make it a good idea. use a form or report and set the format on the control.

[ssanfu]

Also be aware that "Name" and "Description" are reserved words in Access. Plus they are not very descriptive.
"Name" of what?
"Description" of what?

Problem names and reserved words in Access

[perry59]

that's a whole new can of worms, I haven't got very far creating reports programmatically

[perry59]

Also be aware that "Name" and "Description" are reserved words in Access. Plus they are not very descriptive.
"Name" of what?
"Description" of what?

Problem names and reserved words in Access

thanks Steve, I'll work on changing those

[CJ_London]

Also using long text indiscriminately will lead to problems as although they allow 65000+ characters they have limitations as a consequence. Use short text unless you either need more than 65k characters and/or you need the rich text formatting capability. Many of your fields look like they will only need one or two characters

[perry59]

Also using long text indiscriminately will lead to problems as although they allow 65000+ characters they have limitations as a consequence. Use short text unless you either need more than 65k characters and/or you need the rich text formatting capability. Many of your fields look like they will only need one or two characters

Quite right Ajax. short text would have been preferable as ALL those fields are short. But when I attempted to create them as such I got errors from Access. I don't recall the message, but if its important I can modify my code to see what it was. So I was forced to use the "memo" type. Here is an example of the code I used for creating one of the tables...
sql = "CREATE TABLE Cables " _
& "(CableID Counter NOT NULL CONSTRAINT PRIMARYKEY PRIMARY KEY, " _
& "DocumentID int, " _
& "CableDefID int, " _
& "SheetID int, " _
& "SheetName Memo, " _
& "CableColor Memo, " _
& "CableLength Memo, " _
& "CableType Memo, " _
& "Valid YesNo, " _
& "Existing YesNo, " _
& "Reference YesNo, " _
& "Stowed YesNo, " _
& "CableName Memo, " _
& "Description Memo, " _
& "PartNumber Memo, " _
& "Vendor Memo, " _
& "DesignatorPrefix Memo, " _
& "Designator Memo, " _
& "DesignatorSuffix Memo, " _
& "DesignatorSegment Memo, " _
& "CableText Memo, " _
& "TestCode Memo, " _
& "FromComponent Memo, " _
& "ToComponent Memo, " _
& "InsertedDate Date, " _
& "InsertedBy Memo, " _
& "UpdatedDate Date, " _
& "UpdatedBy Memo, " _
& "IncludeCutList YesNo, " _
& "ExcludeFromBom YesNo); "


DatabaseOps.executeNonQuery(sql)

[Minty]

Just replace the Memo data type word with Text ?

CurrentDB.Execute "CREATE TABLE Flibble1 (Textfield1 Text);"

[CJ_London]

but if its important

that depends on your requirement. If you need to join on them or need to concatenate with other fields, you will find some of the limitations will have an impact.


With regards your original question, you will find properties like format in the tabledef - it is not something you can specify with sql.

with regards values, you will need to check what value is what for format. set a field as required then use vba to interrogate the tabledef to find the property and the value - see these links
http://www.java2s.com/Code/VBA-Excel...properties.htm
https://www.tek-tips.com/viewthread.cfm?qid=457077

[perry59]

Just replace the Memo data type word with Text ?

CurrentDB.Execute "CREATE TABLE Flibble1 (Textfield1 Text);"

tried that, "text" does not work in the .net code, you have to call it "char" to get "short text" in Access, but I got complaints from Access (record too large)
funny, in the .net code you have to call it "memo" to get "long text" in Access or "char" to get "short text", would be nice if it were consistent!
I will create a new thread to deal with this problem.
Thanks

[Minty]

It's not obvious you are running this from .Net

SQL on a SQL server would require Char or better Varchar(255) for this to indicate the length of the field pretty sure that will fail in Access DB file.

[perry59]

that depends on your requirement. If you need to join on them or need to concatenate with other fields, you will find some of the limitations will have an impact.


With regards your original question, you will find properties like format in the tabledef - it is not something you can specify with sql.

with regards values, you will need to check what value is what for format. set a field as required then use vba to interrogate the tabledef to find the property and the value - see these links
http://www.java2s.com/Code/VBA-Excel...properties.htm
https://www.tek-tips.com/viewthread.cfm?qid=457077

when you say limitations do you mean limitations on "short" text (char in vb.net) or "long text" (memo in vb.net)?
I have not needed concatenations yet, but I DO need joins. Should I stick with long text even though its a huge overkill?
the links you posted are interesting, did not see those when I searched for the topic. I'll convert them over to .net and stash them in my utilities module.
may make for some good debugging tools.
Thanks!

[perry59]

It's not obvious you are running this from .Net

SQL on a SQL server would require Char or better Varchar(255) for this to indicate the length of the field pretty sure that will fail in Access DB file.

true, I did not specify I was using vb.net
I do know that sql server uses slightly different naming conventions, in fact this code was originally for sql server where I used "char" and "varchar" and I am porting it to access.
yeah, I know that sounds like a dumb downgrade, but everyone at my work has access and only a select few can use our sql server.