Change default value of field through vba

Question

I am trying to change the default value of a field I just added through VBA. I have this line of code below that I added in to do so but for some reason it is saying the item is not in the collection and I am really confused as to why. Any help would be greatly appreciated!

AddColumn = "ALTER TABLE ShouldImportMetricsIDsTable " & _
"ADD COLUMN [ImportStatus] TEXT"

db.Execute AddColumn

CurrentDb.TableDefs("ShouldImportMetricsIDs").Fields("ImportStatus").DefaultValue = "No"

I have also included the line that adds the field.

4dmonster · Accepted Answer · 2014-12-22 19:28:50Z

3

You just pointed to different tables in ALTER TABLE you referenced ShouldImportMetricsIDsTable and in VBA code ShouldImportMetricsIDs.

P.S. DefaultValue property is build in so you can easily set its value

CurrentDb.TableDefs("ShouldImportMetricsIDsTable").Fields("ImportStatus").DefaultValue = "No"

but some other field properties are odd so you have to use constructions like that:

Dim p As Property
Set p = CurrentDb.TableDefs("ShouldImportMetricsIDsTable").Fields("ImportStatus").CreateProperty("DefaultValue", dbText, "No")
CurrentDb.TableDefs("ShouldImportMetricsIDsTable").Fields("ImportStatus").Properties.Append (p)

answered Dec 22, 2014 at 19:28

4dmonster

3,0411 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pablo Over a year ago

Thank you! This did change the default property but this table is a copy of another so there are already records in it. How would I get all the existing records to be no as well?

4dmonster Over a year ago

Execute UPDATE query with WHERE [ImportStatus] is null or WHERE [ImportStatus]=''

Community · Accepted Answer · 2017-05-23 11:57:52Z

1

I think you can add the default value to the ALTER TABLE expression:

CurrentProject.Connection.Execute "ALTER TABLE ShouldImportMetricsIDsTable " & _
"ADD COLUMN [ImportStatus] TEXT" & _
"DEFAULT ""No"";"

I'm basing my answer off this question: SQL SET DEFAULT not working in MS Access

EDIT: Looking at the answer from HansUp in that link, I edited the answer above to reflect the syntax he uses there. I hope this works for you.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Dec 22, 2014 at 18:59

Access_Query

992 silver badges11 bronze badges

2 Comments

Pablo Over a year ago

It says that there is a syntax error in the field definition when I try to run that...

pteranodon Over a year ago

The SQL needs to include a space between TEXT and DEFAULT.

Collectives™ on Stack Overflow

Change default value of field through vba

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related