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.

score 3 · Accepted Answer · answered Dec 22 '14 at 19:28

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 '14 at 19:28

4dmonster

3,012
1
14
24

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? – Pablo Dec 22 '14 at 19:40
Execute UPDATE query with `WHERE [ImportStatus] is null` or `WHERE [ImportStatus]=''` – 4dmonster Dec 22 '14 at 19:42

score 1 · Answer 2 · edited May 23 '17 at 11:57

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 '17 at 11:57

Community

1
1

answered Dec 22 '14 at 18:59

Access_Query

99
1
11

It says that there is a syntax error in the field definition when I try to run that... – Pablo Dec 22 '14 at 19:09
The SQL needs to include a space between TEXT and DEFAULT. – pteranodon Dec 22 '14 at 21:34

Change default value of field through vba

2 Answers2