Alter Table with NULL/NOT NULL options 
Author Message
 Alter Table with NULL/NOT NULL options

Hi,

I'm trying to set up scripts for switching between NULL
and NOT NULL options on a number of fields in an Access
database.

The following line of code works (changing state of
MyField from NULL to NOT NULL):

ds.Execute "ALTER TABLE MyTable ALTER COLUMN MyField
CHARACTER(50) NOT NULL"

This line of code does not (changing state of MyField from
NOT NULL to NULL):

ds.Execute "ALTER TABLE MyTable ALTER COLUMN MyField
CHARACTER(50) NULL"

Is there something wrong with my code or are there some
constraints on the switching between NULL and NOT NULL
states that I don't know about?

Thanks!



Mon, 15 Aug 2005 18:00:31 GMT  
 Alter Table with NULL/NOT NULL options
It seems a bit odd to be doing this, but would it be okay with you to
use DAO instead of SQL DDL?  If so, I believe you could accomplish the
same goal with this:

    db.TableDefs("MyTable").Fields("MyField").Required = False

I'm not an SQL expert, and it's not obvious to me how you can do this
with SQL.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


Quote:
> Hi,

> I'm trying to set up scripts for switching between NULL
> and NOT NULL options on a number of fields in an Access
> database.

> The following line of code works (changing state of
> MyField from NULL to NOT NULL):

> ds.Execute "ALTER TABLE MyTable ALTER COLUMN MyField
> CHARACTER(50) NOT NULL"

> This line of code does not (changing state of MyField from
> NOT NULL to NULL):

> ds.Execute "ALTER TABLE MyTable ALTER COLUMN MyField
> CHARACTER(50) NULL"

> Is there something wrong with my code or are there some
> constraints on the switching between NULL and NOT NULL
> states that I don't know about?

> Thanks!



Fri, 19 Aug 2005 23:13:31 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Null Null Null!!!!!!!!!

2. Null, Null, Null!

3. double null question [2 * Null = Null] !!!

4. Null = Null Not Working ?

5. null not null

6. When is Null not Null

7. To Null, or not to Null...?

8. To Null, or not to Null...?

9. Some fields in my table does not store the new data but only NULL

10. Dynamic HML Tables based on # of NOT NULL columns in RS

11. ADOX Access Table Creation Not null Fields

12. Wanto to assign a Empty Date (not NULL) to VFP table

 

 
Powered by phpBB® Forum Software