1

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

Community
  • 1
  • 1
serhii.syrotynin
  • 241
  • 1
  • 14
  • Same question here http://stackoverflow.com/questions/14057085/sql-set-default-not-working-in-ms-access – user3106974 Jun 14 '15 at 13:54
  • @user3106974 this answer isn't helpful for me. I can't use ADO connection. Only simple sql mode in ms Access needed. – serhii.syrotynin Jun 14 '15 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 Jun 14 '15 at 14:14
  • However I suspect you will actually prefer this: `CurrentProject.Connection.Execute "ALTER TABLE Persons ALTER COLUMN City SET DEFAULT ""SANDNES"""` – HansUp Jun 14 '15 at 14:15
  • @sergey.syrotynin As I explained [here](http://stackoverflow.com/a/14057408/77335), `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 Jun 14 '15 at 14:34
  • @HansUp That's weird. I thought there is some way to use simple Default keyword in MS Access. I even have such task on exam – serhii.syrotynin Jun 14 '15 at 14:45

1 Answers1

1

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

Set a default value for a table field

  1. In the Navigation Pane, right-click the table that you want to change, and then click Design View.

  2. Select the field that you want to change.

  3. On the General tab, type a value in the Default Value property box.

  4. 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.

  5. Save your changes.

user3106974
  • 112
  • 10