create table set default value

[mrsbean]

I have written a sql statement which creates a table with a lot of fields. I want to set some default values in some of the fields. One of the fields is a date/time field, and I want to set the default to Now(). Another field is a textt field and I want to set it to a value of ",1,2,3,4,".

What is the syntax for specifying a default value for a create table statement in Access?

MrsBean

 

[lespaul]

not that I know the answer to your question, but wanted to know, do you really have a single field that contains multiple pieces of information? You say:

Another field is a textt (sic) field and I want to set it to a value of ",1,2,3,4,"

Are 1 and 2 and 3 and 4 four separate pieces of information? Then they should be stored separately.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mrsbean]

I know they should be, but I have to do it the way they want me to ... I would never set it up like this. Can you provide any insight as to how to set up default values via SQL statement?

MrsBean

 

[traingamer]

Leslie, Mrsbean is trying to programatically set the default value of a field (and in one example limit the entries to blank or 1 through 4). I know how to do it in design mode, but not programatically.



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

In DAO it's

db.TableDefs("myTable").Fields("myField").Properties("Default").Value = "1,2,3,4"

but the "Default" property may not exist and you will need to add it to the field's properties collection.
(Left as an exercise for the reader.)

 

[lespaul]

Greg, I'm aware of what she was asking for, I stated that I didn't have the answer to her question, but was wondering about her design.

I know they should be, but I have to do it the way they want me to

Unfortunately there are many users who think they know what they want and it's a developers job to present the user with what they want AND follow the rules of database design. There are ways that you could store that information correctly as a single piece of information in each record and when presenting that information to the user show it the way they "want" to see it.

For instance, there is an FAQ that describes how to take the following information:

KeyID Text
1 Brother
1 Sister
1 Father

and combine it to display:

Brother, Sister, Father

The information is stored correctly in the table and presented to the user as a comma separated list. See FAQ701-4233 for information on how to concatenate related records into a comma separated list.

Leslie

 

[mrsbean]

It's the bosses job to be the boss. The boss doesn't understand (I'm working on it) why it should be set up differently. There is an existing framework in place, and I cannot change it overnight.

MrsBean

 

[mrsbean]

Golam,

Thanks for the input. What I'm trying to accomplish is to make some changes to the backend of a website without taking it offline ... No big deal. I can always bring the database down, make the change(s) and upload it again. Would just prefer to keep things running at all times without doing that.

MrsBean