How do I set the same default values on all fields in a table?

[Guest]

I am trying to set the default values on all the fields in my table to null
(default seems to be zero). How do I do this globally without going through
hundreds of fields one by one?

 

[Douglas J. Steele]

You could do it through VBA.

Something like the following untested air-code should work:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
fldCurr.DefaultValue = vbNullString
Next fldCurr
End If
Next tdfCurr

Set dbCurr = Nothing

 

[Jamie Collins]

I am trying to set the default values on all the fields in my table to null
(default seems to be zero).

So you *all* your tables' columns are numeric and now nullable? Then
you have no keys! Technically, they are 'heaps' rather than 'tables'.
If 'heap' is the collective noun of 'trash', what's the collective noun
of 'heaps' <g>?

Jamie.

--

 

[Douglas J. Steele]

There's a difference between setting the Default Value to Null, and setting
the field's value to Null, Jamie.

If the field's Required and there is no Default Value and no value is
provided, inserts will fail.

 

[Jamie Collins]

There's a difference between setting the Default Value to Null, and setting
the field's value to Null, Jamie.

If the field's Required and there is no Default Value and no value is
provided, inserts will fail.

There's a difference between a column with a Default Value of Null and
a column with no default, Doug. I was giving the OP the benefit of the
doubt when I assumed the columns were nullable: what would be the sense
of a column with a DEFAULT value that is not a legal value?!

Jamie.

--

 

[Douglas J. Steele]

There's a difference between a column with a Default Value of Null and
a column with no default, Doug. I was giving the OP the benefit of the
doubt when I assumed the columns were nullable: what would be the sense
of a column with a DEFAULT value that is not a legal value?!

Whereas I gave him the benefit of the doubt that he meant he didn't want a
default.

 

[Jamie Collins]

There's a difference between a column with a Default Value of Null and
a column with no default

We may have a different interpretation of the OP's request.

I tested your code and it seems to *remove* the default i.e. (in schema
information catalog terms)

COLUMN_HASDEFAULT = False

My interpretation is the OP wants to retain a default value and make it
the null value i.e.

COLUMN_HASDEFAULT = True AND COLUMN_DEFAULT = 'NULL'

Perhaps the OP should clarify...?

Jamie.

--

 

[Douglas J. Steele]

We may have a different interpretation of the OP's request.

Perhaps the OP should clarify...?

Yup.