Setting default value using SQL in ms access 2007

Question

According to w3schools I can set default value in MS Access this way:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

But this variant gives me

"Syntax error in ALTER TABLE instruction".

Also doesn't work:

"SANDNES"
(SANDNES)

I'm confused about that. My City Column hasn't any constraints or dependencies.

MS Access 2007. What i'm doing wrong ?

What important to mention: I should use SQL VIEW inside MS Access to set default value

@user3106974 this answer isn't helpful for me. I can't use ADO connection. Only simple sql mode in ms Access needed. — serhii.syrotynin
– serhii.syrotynin, Commented Jun 14, 2015 at 14:10
@sergey.syrotynin What prevents you from using an ADO Connection inside Access? CurrentProject.Connection is an ADO Connection, and your statement can be executed without error like this: CurrentProject.Connection.Execute "ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'" — HansUp
– HansUp, Commented Jun 14, 2015 at 14:14
However I suspect you will actually prefer this: CurrentProject.Connection.Execute "ALTER TABLE Persons ALTER COLUMN City SET DEFAULT ""SANDNES""" — HansUp
– HansUp, Commented Jun 14, 2015 at 14:15
@sergey.syrotynin As I explained here, SET DEFAULT is only supported when executed from ADO. When you execute your query from SQL View, you are not using ADO ... so you get that syntax error. — HansUp
– HansUp, Commented Jun 14, 2015 at 14:34

user3106974 · Accepted Answer · 2015-06-14 14:27:03Z

1

Do you have to sql for it? Otherwise do it like this:

Set a default value for a table field

In the Navigation Pane, right-click the table that you want to change, and then click Design View.
Select the field that you want to change.
On the General tab, type a value in the Default Value property box.
The value you that you can enter depends on the data type that is set for the field. For example, you can type =Date() to insert the current date in a Date/Time field.
Save your changes.

answered Jun 14, 2015 at 14:18

user3106974

11010 bronze badges

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

1 Comment

Yes, I have to use SQL view inside MS Access