How to add a column to an existing table via code?

[John]

I have an existing table in a .mdb database and I want to add a text
column that is not required. I can add the text column but when I tried
to set it to allow nulls I get the error "Multiple-step OLE DB
operations generated errors.", error messasge. My code is shown below.

Dim catCurr As New ADOX.Catalog

catCurr.ActiveConnection = CurrentProject.Connection

With catCurr.Tables("Test1")
.Columns.Append "TransComments", adWChar, 80
.Columns("TransComments").Attributes = adColNullable
End With

Set catCurr = Nothing
End Sub

When Access reaches the line ".Columns("TransComments").Attributes =
adColNullable" it generates the error. What am I doing wrong? I am
running Access 2003. Thanks for your help.


John

 

[Chris O'C via AccessMonster.com]

If you use SQL to create the new column, it's nullable by default.

Public Sub addColumn()

Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
conn.Execute "ALTER TABLE Test1 " _
& "ADD COLUMN TransComments NCHAR (80) " _
& "WITH COMPRESSION;"
conn.Close

End Sub


Chris
Microsoft MVP

 

[Douglas J. Steele]

Not only is it nullable by default, but it's not possible to create a new
column that isn't nullable which only makes sense: what value would you put
in that column for all existing rows?

 

[Chris O'C via AccessMonster.com]

It's possible. Add some records to the Test1 table, then run this query:

ALTER TABLE Test1
ADD COLUMN ShowDoug CHAR (40) NOT NULL;

Existing records will show blanks for the ShowDoug column. That's because
it's got zero length strings in place of nulls you say. So run this query:

ALTER TABLE Test1
ADD COLUMN ShowLong LONG NOT NULL;

Existing records will show blanks for the ShowLong column. That's because
it's got nulls where we told Jet not to.

Chris
Microsoft MVP