Adding required fields with default values to existing tables
| Author |
Message |
|
0to6
#1 / 9
|
 Adding required fields with default values to existing tables I have a table, Table1, with say 2566 rows of data in it. Now, a VB application will add a field to the table at runtime via the following code: With OpenDatabase(path, True)
With .TableDefs!Table1
.Fields.Append .CreateField("NewField", dbLong)
With .Fields("NewField")
.Required = True
.DefaultValue = somedefaultvalue
End With
End With
End With
The field is definately added like I wanted, but the existing data has
nothing in the new field. How can this be? If the field is required,
shouldn't the existing data get the default value in the new field? How can
the field be left Null on all that data?
What the hell, Mel?
|
| Fri, 22 Jun 2001 03:00:00 GMT |
|
 |
|
Kieron
#2 / 9
|
Adding required fields with default values to existing tables I believe that default values are assigned to fields for new records only. As this is the case, you must populate the field yourself for existing records --
Cheers, Kez
Quote: >I have a table, Table1, with say 2566 rows of data in it. Now, a VB >application will add a field to the table at runtime via the following code: > With OpenDatabase(path, True)
> With .TableDefs!Table1
> .Fields.Append .CreateField("NewField", dbLong)
> With .Fields("NewField")
> .Required = True
> .DefaultValue = somedefaultvalue
> End With
> End With
> End With
>The field is definately added like I wanted, but the existing data has
>nothing in the new field. How can this be? If the field is required,
>shouldn't the existing data get the default value in the new field? How
can
>the field be left Null on all that data?
>What the hell, Mel?
|
| Fri, 22 Jun 2001 03:00:00 GMT |
|
|
|
Andy Bar
#3 / 9
|
Adding required fields with default values to existing tables The new constraint is not applied to existing records and certainly no data is added to the table by adding a required field. To get the effect you want, you would need to execute an update query after adding the field. The SQL would be something like: UPDATE Table1 SET Table1.NewField = somedefaultvalue WHERE (((Table1.NewField) Is Null)); -- Andy
Quote:
>I have a table, Table1, with say 2566 rows of data in it. Now, a VB >application will add a field to the table at runtime via the following code: > With OpenDatabase(path, True)
> With .TableDefs!Table1
> .Fields.Append .CreateField("NewField", dbLong)
> With .Fields("NewField")
> .Required = True
> .DefaultValue = somedefaultvalue
> End With
> End With
> End With
>The field is definately added like I wanted, but the existing data has
>nothing in the new field. How can this be? If the field is required,
>shouldn't the existing data get the default value in the new field? How can
>the field be left Null on all that data?
>What the hell, Mel?
|
| Fri, 22 Jun 2001 03:00:00 GMT |
|
|
|
0to6
#4 / 9
|
Adding required fields with default values to existing tables Quote:
>The new constraint is not applied to existing records and certainly no
>data is added to the table by adding a required field. To get the
>effect you want, you would need to execute an update query after
>adding the field. The SQL would be something like:
>UPDATE Table1 SET Table1.NewField = somedefaultvalue
>WHERE (((Table1.NewField) Is Null));
That's what I'm doing, but if I don't, I have many rows with Null in a field whose required property is set to True. How does Access deal with this?
|
| Fri, 22 Jun 2001 03:00:00 GMT |
|
|
|
Terry Kref
#5 / 9
|
Adding required fields with default values to existing tables The required property means: "When a new record is added or if a record is editted ensure that data is
entered in this field"
It doesn't mean
"For all the records make sure that this field has a value in it"
Which is what you seem to think it means.
The required property only has an affect when a new record is added or if an
existing one is editted. Adding a field with it's required property set to
yes does not have any impact on existing records, unless you edit them.
As Andy says, you need to run an update query in order to assign the default
value to the field.
Quote:
>>The new constraint is not applied to existing records and certainly no
>>data is added to the table by adding a required field. To get the
>>effect you want, you would need to execute an update query after
>>adding the field. The SQL would be something like:
>>UPDATE Table1 SET Table1.NewField = somedefaultvalue
>>WHERE (((Table1.NewField) Is Null));
>That's what I'm doing, but if I don't, I have many rows with Null in a
field
>whose required property is set to True. How does Access deal with this?
|
| Sat, 23 Jun 2001 03:00:00 GMT |
|
|
|
Gary Labowit
#6 / 9
|
Adding required fields with default values to existing tables Just an aside: Shouldn't you set the field properties BEFORE you do the APPEND? I.e. do the CreateField, set the properties to the field object, then append it to the table. I was under the impression that the properties were read only once the field was appended. Good luck. -- Gary (MCT, MCPS, MCSD) http://www.enter.net/~garyl/ for references to good books
ICQ 6375624
Quote: >I have a table, Table1, with say 2566 rows of data in it. Now, a VB >application will add a field to the table at runtime via the following code: > With OpenDatabase(path, True)
> With .TableDefs!Table1
> .Fields.Append .CreateField("NewField", dbLong)
> With .Fields("NewField")
> .Required = True
> .DefaultValue = somedefaultvalue
> End With
> End With
> End With
>The field is definately added like I wanted, but the existing data has
>nothing in the new field. How can this be? If the field is required,
>shouldn't the existing data get the default value in the new field? How
can
>the field be left Null on all that data?
>What the hell, Mel?
|
| Sat, 23 Jun 2001 03:00:00 GMT |
|
|
|
Andy Bar
#7 / 9
|
Adding required fields with default values to existing tables Properties need to be set before appending and some don't . These don't - you can use DAO to change these properties on existing fields. -- Andy
Quote:
>Just an aside: >Shouldn't you set the field properties BEFORE you do the APPEND? >I.e. do the CreateField, set the properties to the field object, then append >it to the table. >I was under the impression that the properties were read only once the field >was appended. >Good luck. >-- >Gary (MCT, MCPS, MCSD) >http://www.enter.net/~garyl/ for references to good books
>ICQ 6375624
>>I have a table, Table1, with say 2566 rows of data in it. Now, a VB >>application will add a field to the table at runtime via the following >code: >> With OpenDatabase(path, True)
>> With .TableDefs!Table1
>> .Fields.Append .CreateField("NewField", dbLong)
>> With .Fields("NewField")
>> .Required = True
>> .DefaultValue = somedefaultvalue
>> End With
>> End With
>> End With
>>The field is definately added like I wanted, but the existing data has
>>nothing in the new field. How can this be? If the field is required,
>>shouldn't the existing data get the default value in the new field? How
>can
>>the field be left Null on all that data?
>>What the hell, Mel?
|
| Sat, 23 Jun 2001 03:00:00 GMT |
|
|
|
Andy Bar
#8 / 9
|
Adding required fields with default values to existing tables And just to add a little to Terry's good clarification, you can even successfully go in and edit a record with a null Required field, as long as you don't try to set that field to null once it has been given a value. -- Andy
Quote:
>The required property means: >"When a new record is added or if a record is editted ensure that data is
>entered in this field"
>It doesn't mean
>"For all the records make sure that this field has a value in it"
>Which is what you seem to think it means.
>The required property only has an affect when a new record is added or if an
>existing one is editted. Adding a field with it's required property set to
>yes does not have any impact on existing records, unless you edit them.
>As Andy says, you need to run an update query in order to assign the default
>value to the field.
>>>The new constraint is not applied to existing records and certainly no
>>>data is added to the table by adding a required field. To get the
>>>effect you want, you would need to execute an update query after
>>>adding the field. The SQL would be something like:
>>>UPDATE Table1 SET Table1.NewField = somedefaultvalue
>>>WHERE (((Table1.NewField) Is Null));
>>That's what I'm doing, but if I don't, I have many rows with Null in a
>field
>>whose required property is set to True. How does Access deal with this?
|
| Sat, 23 Jun 2001 03:00:00 GMT |
|
|
|
John F. Stendo
#9 / 9
|
Adding required fields with default values to existing tables
You are Correct, the required only applys to new records not modified
table structures after the fact.
Quote:
> I believe that default values are assigned to fields for new records only.
> As this is the case, you must populate the field yourself for existing
> records
> --
> Cheers, Kez
> >I have a table, Table1, with say 2566 rows of data in it. Now, a VB
> >application will add a field to the table at runtime via the following
> code:
> With OpenDatabase(path, True)
> With .TableDefs!Table1
> .Fields.Append .CreateField("NewField", dbLong)
> With .Fields("NewField")
> .Required = True
> .DefaultValue = somedefaultvalue
> End With
> End With
> End With
|
| Sat, 23 Jun 2001 03:00:00 GMT |
|
| |
|
