Try DoCmd.RunSQL "ALTER TABLE.......
You need ALTER COLUMN and SET
If its a boolean field
Or for a text fieldCode:CurrentDB.Execute "ALTER TABLE Families ALTER COLUMN FamilyDonOnly SET Default=False"
I recommend using https://www.w3schools.com/sqlCode:CurrentDB.Execute "ALTER TABLE Families ALTER COLUMN FamilyDonOnly SET Default='No'"
Using DoCmd.RunSQL is less efficient than CurrentDB.Execute
I've double checked several sites and the = should have been omitted
However I'm also getting the syntax error.
Some reference sites say you need to ADD / DROP CONSTRAINT first but that's not worked either
I'm sure I've done this before but at the moment I can't solve it
The answer just came to me.
This IS correct
or you can do that as a DDL query in query SQL viewCode:CurrentDB.Execute "ALTER TABLE Families ALTER COLUMN FamilyDonOnly SET DEFAULT False"
but the SET DEFAULT statement is only supported if you use ANSI 92 syntax.
To do that, tick the box below
However you will get a warning that enabling this will have knock-on effects on the rest of your database.
A couple of years ago, I ticked that & forgot about it then started getting issues.
Eventually when I realised the link I unticked it again
Personally I would just set the default(s) manually and leave well alone but its up to you
Colin,
The issue isn't a show stopper. I have backend DB's in the wild that needed the default updated IF I wanted to tweak some code in the frontend. My app includes a general module that maintains compatibility between app versions and corresponding backend versions. I would need to add the "ALTER TABLE" statement to that module for the next app version release. I'm going to scrap the idea completely but grateful to you for your persistence in finding a solution.
Thanks,
Bill
(PS) I made a copy of my app's frontend, ticked the box, closed and reopened and ran your statement but it still fails with a syntax error. Please don't spend anymore time on this.
You can still use ALTER TABLE with ALTER COLUMN without the ANSI92 setting but not the SET DEFAULT part.
One solution might be to set it temporarily to ANSI92 for the upgrade, then use code to reset back to 'normal' once changes are completed.
Or have a separate utility that runs the BE updates and isn't used for any other purpose.
Or use a VB Script...there's always a way