Set default value to 0 with ALTER TABLE

[Nacho]

I have a large database that I need to load massive amounts of data. After
appending the data, numerical fields apear as Null, as no default value was
created.

As I need to create this DB once and again, cannot set the default with
Desing View for Tables.

Traying to use ALTER TABLE "SET DAFULT" command, by always promt for syntax
error:

ALTER TABLE db ALTER COLUMN [my number] SET DEFAULT 0.00;

Have already tryed checking ANSI 92 compatibility, but no luck.

Any idea?

Thks, Rds
Nacho

 

[Allen Browne]

From memory, DEFAULT only works if you execute the query under ADO.

You can set it programmatically under DAO, by setting the DefaultValue of
the Field in the TableDef.

But even when it is set, Access is inconsistent about using it. In different
versions, using different ways of adding records (interface, append query,
recordset, ...) you don't get the same results.

So, you might consider executing an UPDATE query to set the fields to zero
that are null.

Better still: set up the table the way you want it. Then delete the existing
records and append the import, rather than creating and destroying the
table(s.)