Setting Default Values for all fields in a table

[MrAccessUniversity]

I recently learned how to set default values for all fields in a table using VB (DAO).

Public Sub Make_defs()

Dim x As Integer

For x = 1 To CurrentDb.TableDefs("import_format").Fields.Count
CurrentDb.TableDefs("import_format").Fields(x - 1).DefaultValue = "=null"
Next

End Sub

I had a table used for importing data into an application and most of the fields default values were a literal string "null". This code was quite simple but most of the samples found on other forums were useless.

 

[HiTechCoach]

The default value for fields is normally Null for none numeric fields. Why would you need to specifically set it to Null for non numeric fields?

 

[MrAccessUniversity]

The table was used for creating an import file into another application for data conversion. Many of the columns of data in the import specification were not required and were set to null. In the receiving application, you had to pass literally the word <'null'> as a string.

 

[HiTechCoach]

The table was used for creating an import file into another application for data conversion. Many of the columns of data in the import specification were not required and were set to null. In the receiving application, you had to pass literally the word <'null'> as a string.

Ah .. that makes sense. I have had to deal with import/export between system that had similar requirements.

Glad you found a working solution. Thanks for sharing.

 

[MrAccessUniversity]

You were right. The prescribed solution didn't make sense because all columns are by default null (empty). By the way, the illustrated text <'null'> is just an example. There is nothing specifically useful about how the string is formatted using the single quotes inside the brackets. Don't know why it took me so long to figure out such an easy way of doing it. I spent hours running down tables using the Access table designer to set default values for such tables.