Changing column from Required to Not Required in Access DB


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Changing column from Required to Not Required in Access DB

Hybrid View

  1. #1
    Adam Dawes Guest

    Changing column from Required to Not Required in Access DB

    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.




  2. #2
    Paul Clement Guest

    Re: Changing column from Required to Not Required in Access DB

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center