Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    13

    Unanswered: ALTER TABLE to Allow Null Values

    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

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    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)
    Code:
    with <YourConnection>
        .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"
    end with
    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

    Check out http://allenbrowne.com/func-ADOX.htm...yFieldPropAdox

    Edit: You may want to expose the ZLS property of the same treatment, as the Nullable property (col.Properties("Jet OLEDB:Allow Zero Length") - set it to false)

    (GolferGuy, this is valid Jet SQL for instance when executed on OLEDB/ADO connection, check out http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx and the two other articles referenced at the end)
    Last edited by RoyVidar; 05-05-07 at 04:37.
    Roy-Vidar

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Roy,
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •