DAO ALTER TABLE syntax error

[GraeagleBill]

Not having much success with a DAO ALTER TABLE syntax. Both of the sample tries pictured below fail because of syntax. What am I missing?

[Thompyt]

Try DoCmd.RunSQL "ALTER TABLE.......

[isladogs]

You need ALTER COLUMN and SET
If its a boolean field

CurrentDB.Execute "ALTER TABLE Families ALTER COLUMN FamilyDonOnly SET Default=False"

Or for a text field

CurrentDB.Execute "ALTER TABLE Families ALTER COLUMN FamilyDonOnly SET Default='No'"

I recommend using https://www.w3schools.com/sql

[GraeagleBill]

Hi Colin,
Neither your suggestion or Thompty's was met with success: (Both failed because of syntax error) And yes, "FamilyDonOnly" is YES/NO.

[isladogs]

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

[isladogs]

The answer just came to me.
This IS correct

CurrentDB.Execute "ALTER TABLE Families ALTER COLUMN FamilyDonOnly SET DEFAULT False"

or you can do that as a DDL query in query SQL view
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

[GraeagleBill]

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.

[isladogs]

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