2

I have a 12 years old mdb database and I was asked to add a new column to a table and set to a default value of "1".

My knowledge of asp/mdb is close to zero. I also have no Access or similar softwares.

I tried with:

 ALTER TABLE Members ADD COLUMN Privacy Double Default 1

but generates error:

Error: An action query cannot be used as a row source.

Then I tried with:

 ALTER TABLE Members MODIFY COLUMN Privacy VARCHAR(4) NOT NULL DEFAULT 'Yes';

but this also triggers another error:

Microsoft JET Database Engine error '80040e14'

How can I set an existing column to a default value?

Should I use an offline tool? If so which one?

Note: I also used an online tool to create the new column but it has no option to set a default value.

So I can either create the new column with the tool and set a default value with SQL, or do the creation of the column with the default value still with SQL.

Newd
  • 2,174
  • 2
  • 17
  • 31
Mario
  • 420
  • 1
  • 11
  • 23
  • have you tried [this syntax](http://stackoverflow.com/questions/14057085/sql-set-default-not-working-in-ms-access)? – luk2302 Jun 29 '15 at 12:59
  • @luk2302 yes I tried but I get the same error "Error: An action query cannot be used as a row source." – Mario Jun 29 '15 at 13:03
  • The above comment suggests that you are setting a recordset to the result of the query, just execute it. – Fionnuala Jun 29 '15 at 13:17
  • just a stupid question: can't you edit the table via design view? O_O – Krish Jun 29 '15 at 13:23
  • @krish from the above " I also have no Access or similar softwares.)" – Fionnuala Jun 29 '15 at 13:26
  • @Fionnuala: I run SQL commands from an old online tool called "Table Editor" that reads the mdb file, is similar to PHPMyAdmin but very simple and basic. I just run the above query with no results... should I create specific ASP files and run them? – Mario Jun 29 '15 at 13:40
  • Try it, but be careful and use a copy of the mdb make sure you use the right connection. I know nothing about Table Editor. – Fionnuala Jun 29 '15 at 13:45
  • There's a free tool called WinSql lite which you can use to edit an mdb file via an odbc connection. – John Jun 29 '15 at 14:34

2 Answers2

1

I solved using AxBase from SourceForge.net.

I could properly open the *.mdb database and run the above SQL commands and they worked perfectly:

 ALTER TABLE Members ALTER COLUMN Privacy SET DEFAULT 1
Mario
  • 420
  • 1
  • 11
  • 23
-1

hi your access database is may be in office 2003 or office xp. You can try to enable sql syntax and then try to run your query.

Tools -> Options -> Tables/Queries -> (At the bottom right:) Sql Server Compatible Syntax - turn option on for this database.

ALTER TABLE Members ADD COLUMN Privacy number Default 1
Hussainsoni
  • 87
  • 1
  • 2
  • 9
  • You can run such statements from VBA, but not from the query design window. Changing to Sql Server Compatible Syntax may ruin a dozen things. Furthermore, the OP is using asp classic. – Fionnuala Jun 29 '15 at 13:14