Changing column from Required to Not Required in Access DB

[Adam Dawes]

I need to write some code that will convert an existing field in a table in
both an Access and SQL Server database from being required (NOT NULL) to not
required (NULLable).

I've got this working in SQL Server very easily using the following SQL
statement:

ALTER TABLE MyTable
ALTER COLUMN MyField varchar(50) NULL

....assuming the MyField column was not nullable before, after executing the
above code it will be.

However, I can not get this to work in Access. I've executed the following
code:

ALTER TABLE MyTable
ALTER COLUMN MyField text(50) NULL

....and it executes without error, but when I go into Access and examing the
table, the field is still marked as required.

Can anyone suggest a way of getting this to work? I'm using ADO to perform
the changes, and DAO isn't available on the target machine.

Many thanks,

--

Adam.

[Paul Clement]

On Fri, 7 Sep 2001 17:21:48 +0100, "Adam Dawes" <dawes@editrack.NOSPAM.com> wrote:

¤ I need to write some code that will convert an existing field in a table in
¤ both an Access and SQL Server database from being required (NOT NULL) to not
¤ required (NULLable).
¤
¤ I've got this working in SQL Server very easily using the following SQL
¤ statement:
¤
¤ ALTER TABLE MyTable
¤ ALTER COLUMN MyField varchar(50) NULL
¤
¤ ...assuming the MyField column was not nullable before, after executing the
¤ above code it will be.
¤
¤ However, I can not get this to work in Access. I've executed the following
¤ code:
¤
¤ ALTER TABLE MyTable
¤ ALTER COLUMN MyField text(50) NULL
¤
¤ ...and it executes without error, but when I go into Access and examing the
¤ table, the field is still marked as required.
¤
¤ Can anyone suggest a way of getting this to work? I'm using ADO to perform
¤ the changes, and DAO isn't available on the target machine.
¤

I believe this will require changing a couple of properties. Here is an ADOX reference to the values
of these fields:

Dim cat As New ADOX.Catalog

cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

? cat.Tables("Table2").Columns("Field2").Attributes AND adColNullable
? cat.Tables("Table2").Columns("Field2").Properties("Jet OLEDB:Allow Zero Length")


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)