ALTER COLUMN

[Darren]

How can I modify a column in a table to accept NULLs using an ALTER TABLE
query?

 

[Gary Walter]

How can I modify a column in a table to accept NULLs using an ALTER TABLE
query?

Hi Darren,

First...is the table in Access (or some other db)?
If Access, what version?

If Access 200x, the "short answer" is
(assuming table = "tblBox" and field = "Box" varchar(50) )

CurrentProject.CurrentConnection.Execute "ALTER TABLE tblBox " _
& "ALTER COLUMN Box varchar(50) NULL", dbFailOnError

Note..you have to reset (or change) the type.

Here is code for 4 command buttons
for 2 methods of "allowing null/ not allowing null":

Option Compare Database
Option Explicit

Private Sub cmdAcceptNulls_Click()
On Error GoTo Err_cmdAcceptNulls_Click
Dim dbs As DAO.Database
Dim fldTableDef As DAO.Field

Set dbs = CurrentDb

Set fldTableDef = dbs.TableDefs("tblBox").Fields("Box")
Debug.Print "1. " & fldTableDef.Name & " ;Required = " & fldTableDef.Required
fldTableDef.Required = False
Debug.Print "2. " & fldTableDef.Name & " ;Required = " & fldTableDef.Required
Debug.Print "------------------------------------------------"

Exit_cmdAcceptNulls_Click:
dbs.Close
Set fldTableDef = Nothing
Set dbs = Nothing
Exit Sub
Err_cmdAcceptNulls_Click:
MsgBox Err.Description
Resume Exit_cmdAcceptNulls_Click
End Sub
Private Sub cmdDoNotAcceptNulls_Click()
On Error GoTo Err_cmdDoNotAcceptNulls_Click
Dim dbs As DAO.Database
Dim fldTableDef As DAO.Field

Set dbs = CurrentDb

Set fldTableDef = dbs.TableDefs("tblBox").Fields("Box")
Debug.Print "1. " & fldTableDef.Name & " ;Required = " & fldTableDef.Required
fldTableDef.Required = True
Debug.Print "2. " & fldTableDef.Name & " ;Required = " & fldTableDef.Required
Debug.Print "------------------------------------------------"

Exit_cmdDoNotAcceptNulls_Click:
dbs.Close
Set fldTableDef = Nothing
Set dbs = Nothing
Exit Sub
Err_cmdDoNotAcceptNulls_Click:
MsgBox Err.Description
Resume Exit_cmdDoNotAcceptNulls_Click
End Sub
Private Sub cmdAcceptNulls2_Click()
On Error GoTo Err_cmdAcceptNulls2_Click
Dim dbs As DAO.Database
Dim fldTableDef As DAO.Field
Dim strSQL As String

strSQL = "ALTER TABLE tblBox ALTER COLUMN Box varchar(50) NULL"
CurrentProject().Connection.Execute strSQL, dbFailOnError
Set dbs = CurrentDb
Set fldTableDef = dbs.TableDefs("tblBox").Fields("Box")
Debug.Print "2. " & fldTableDef.Name & " ;Required = " & fldTableDef.Required
Debug.Print "------------------------------------------------"
dbs.Close
Exit_cmdAcceptNulls2_Click:

Set fldTableDef = Nothing
Set dbs = Nothing
Exit Sub
Err_cmdAcceptNulls2_Click:
MsgBox Err.Description
Resume Exit_cmdAcceptNulls2_Click
End Sub
Private Sub cmdDoNotAcceptNulls2_Click()
On Error GoTo Err_cmdDoNotAcceptNulls2_Click
Dim dbs As DAO.Database
Dim fldTableDef As DAO.Field
Dim strSQL As String

strSQL = "ALTER TABLE tblBox ALTER COLUMN Box varchar(50) NOT NULL"
CurrentProject().Connection.Execute strSQL, dbFailOnError
Set dbs = CurrentDb
Set fldTableDef = dbs.TableDefs("tblBox").Fields("Box")
Debug.Print "2. " & fldTableDef.Name & " ;Required = " & fldTableDef.Required
Debug.Print "------------------------------------------------"
dbs.Close
Exit_cmdDoNotAcceptNulls2_Click:

Set fldTableDef = Nothing
Set dbs = Nothing
Exit Sub
Err_cmdDoNotAcceptNulls2_Click:
MsgBox Err.Description
Resume Exit_cmdDoNotAcceptNulls2_Click
End Sub

Good luck,

Gary Walter

 

[Gary Walter]

Two other things probably worth mentioning:

1) These DDL's probably will fail if the
field has an index on it.

2) "Going the other way", I should have
made sure all field values were not Null
before I ran "Not Null" code.

Good luck,

Gary Walter

 

[Gary Walter]

Sorry....I must have not read my debug print stmts correctly!!!

The DDL's will not error out...
but it will NOT change Required.

But... the DAO tabledef code does work!