ALTER TABLE to Allow Null Values

Question

_{Friday, May 4, 2007 6:50 PM}

I have an (Access 2003) database and I'm trying to update the schema of the database to allow null values in a column.  The column already exists and currently will not allow null values.  This is a distributed application (everyone has their own different MDB file) so I need to be able to modify the column through T-SQL.

My statement to try and do this is:
ALTER TABLE clients ALTER COLUMN state VARCHAR(255) NULL

However, when I view the table after running that SQL statement the table is still not allowing null values.  Please don't tell me I need to drop the column before allowing null values.

Thanks,
Ryan

All replies (8)

_{Monday, May 7, 2007 2:23 PM ✅Answered}

Yes, you will need to create a new column, copy the data from the old column using an update query...then rename the new column (if desired). I don't believe that Jet SQL DDL will allow you to change this property on an existing column.

_{Monday, May 7, 2007 10:18 PM}

Well that was what I was afraid of someone telling me.  However, if that is what has to be done.  Well then that is what will be done.  Thanks for giving me the heads up Paul!

_{Tuesday, May 8, 2007 11:16 AM}

I will use this post because is almost the same problem...

I'm creating a program (VB .net 205) that recreates an access table into another, and i have that problem.

When i create a column, it creates as NOT NULL, and i'm unable to create it as NULL, and the
"alter table [table] alter column [column] [type data] NULL" don't change it.

I've tried this ddl into ACCESS and don't change the requiered, but the

"alter table [table] alter column [column] [type data] NOT NULL" ddl change it to not null.

Resuming:
From NOT NULL to NULL -> Don't works
from NULL to NOT NULL -> work

Why can it be?

_{Tuesday, May 8, 2007 12:56 PM}

When creating a new column using JET SQL DDL the default is NULL so you shouldn't have to change it.

ALTER TABLE tblCustomers ADD COLUMN NewField TEXT(50)

_{Tuesday, May 15, 2007 2:48 PM}

Thanks Paul, harsh but great answer.
Just wanted to note that it's not possible to rename a column.
So the full script to Allow Null Values will be:
ALTER TABLE tablename ADD COLUMN  TEMP  double NULL
UPDATE tablename SET TEMP = fieldname
ALTER TABLE tablename DROP COLUMN   fieldname
ALTER TABLE tablename ADD COLUMN  fieldname double NULL
UPDATE tablename SET fieldname= TEMP
ALTER TABLE tablename DROP COLUMN   TEMP

_{Tuesday, May 15, 2007 5:17 PM}

Yeah, I guess I should have mentioned that you have to use ADOX or DAO to change the name of the column:

DAO:
db.TableDefs("Table2").Fields("NewField").Name = "Field2"

ADOX:
cat.Tables("Table2").Columns("NewField").Name = "Field3" 

_{Monday, May 21, 2007 10:28 PM}

Paul,

I'm having a bit of trouble with the syntax of handling all of this.  The way that I have this update flow charted I should:

1. Copy all the data out of the column that is going to be modified (SELECT INTO)
2. Drop the column out of the table. (ALTER TABLE DROP COLUMN)
3. Add the column back with new schema. (ALTER TABLE ADD COLUMN)
4. Copy the data back from the temporary table.

The problem is that there does not seem to be a one line copy that I'm looking for.  I tried the INSERT INTO command with the SELECT instead of values, but that isn't going to work for two reasons.  One is that it's going to just add new rows with the values from the table and the other that it is not going to know which row to insert the data back into.

This all seems like it should be fairly simple and straight forward but instead it seems to be growing in complexity with each turn.

Cheers,
Ryan

_{Tuesday, May 22, 2007 2:13 PM}

The example provided by MEindert should be pretty close to what you need. You just need to rename the new column, after dropping the old column, using either ADOX or DAO.