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.
The T-SQL statement you have shown is for SQL Server, not Access. So, are you dealing with a SQL Server database that everyone shares through the individual Access databases, or do you have an Access database that has the data in it? This does make a big difference.
I'm not 100% sure, but I don't think you can change from NOT NULL to allowing Null through DDL directly. I think you'll need to recreate the field. I e (note - air code)
.execute "ALTER TABLE clients ADD COLUMN newstate VARCHAR(255)"
.execute "UPDATE TABLE clients SET newstate = state"
.execute "ALTER TABLE clients DROP COLUMN State"
.execute "ALTER TABLE clients ADD COLUMN state VARCHAR(255)"
.execute "UPDATE TABLE clients SET state = newstate"
I'd love to be proven wrong, though. Note - I think you need to verify each step, as I don't think transactions handles DDL, only DML.
You could make it a bit easiser with ADOX - though easier is more a matter of perception, but changing a property through ADOX would at least in my eyes constitute less danger of loosing information thant umpteen DDL statements
The reason I questioned the SQL Server vs Access is the use of the term T-SQL and then the use of the data type of VARCHAR. Both are SQL Server terms rather than Access terms. If by using ADO you can create a VARCHAR type of variable in Access, I did not know that. I was aware that tables and columns could be ALTERed via SQL statements within Access.