Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    56

    Unanswered: SQL Alter Table - Remove NOT NULL

    I think a good solution to the following problem might be useful to many folks out there.

    I have a table with several existing fields that previously we have required to have values. Now, we want to remove that requirement in each user's db file. (In the mdb file, the fields "Required" property is True.) I want to make that property false by running VBA from our Excel (2007) front end. In the past, I've used ALTER TABLE (SQL) to make a field required but can't seem to remove that requirement now, for some reason.

    These fields are not part of a key. I don't see any related constraint, either. They are not indexed. They are simply text fields. As a test I've added a new field to the table (making it required using NOT NULL) but then could not remove the requirement using ALTER TABLE.

    Set rsdata = New ADODB.Recordset

    szSQL = "ALTER TABLE [strTableName] ALTER COLUMN [strFieldName] Text(50) NULL"

    rsdata.Open szSQL, gDBconnect, adOpenStatic, adLockOptimistic, adCmdText

    This codes runs and even changes the field length but it does not change the "Required" property. I've also run a version of this code using EXECUTE and got the same results. Only field length was changed. I can manually open my cope of the mdb file and set the fields Required = false but I need to do this with code for multiple users/files. That is the problem.

    I'm finding numerous posts about making a field required, but nothing really about removing/changing the requirement. Can someone please help with this?

    Thanks in advance.

  2. #2
    Join Date
    May 2004
    Posts
    56
    Here's the solution that worked for me using DAO.
    I ran this VBA from module subprocedure in Excel.

    (IMPORTANT: This requires adding to Excel file reference:
    "Microsoft DAO x Object Library")

    Dim tdf1 As DAO.TableDef
    Dim fld As DAO.Field
    Dim acc As Object
    Dim db1 As Object
    '_________________________________________________ ______
    Set acc = CreateObject("Access.Application")

    acc.visible = False

    '(Password parameter is optional)
    Set db1 = acc.DBEngine.OpenDatabase(gMDBName, False, False, ";PWD=" & gStdPassword)

    acc.OpenCurrentDatabase gMDBName

    Set tdf1 = db1.TableDefs("xxxxx")

    Set fld = tdf1.Fields("ffffff")
    fld.Required = False

    db1.Close

    Set db1 = Nothing

Posting Permissions

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