Adding Columns with ADOX

H

howard

Please, Help!

Everytime I run the code below, to add columns to an
existing table, Access crashes - on the line that reads:
"Set tblADD = catADD.Tables(strTBL_to_UPDATE)".

Am I doing something wrong? Is there an alternative to
using ADOX to add columns to a table. Please, help

Sub myAddColumns(strTBL_to_UPDATE As String)

Dim catADD As New ADOX.Catalog
Dim tblADD As ADOX.Table
Dim colADD As ADOX.Column
Dim rstADD As New ADODB.Recordset

Set rstADD = myFetchRecord
("Z_AddColumns", "ReportType", Left(strTBL_to_UPDATE, 4))

catADD.ActiveConnection = CurrentProject.Connection
Set tblADD = catADD.Tables(strTBL_to_UPDATE)

Do Until rstADD.EOF
Set colADD = New ADOX.Column
Set colADD.ParentCatalog = catADD
With colADD
.Name = rstADD.Fields(2)
.Type = rstADD.Fields(3)
.DefinedSize = rstADD.Fields(4)
.Properties("Nullable") = True
End With
tblADD.Columns.Append colADD
rstADD.MoveNext
Loop
rstADD.Filter = adFilterNone

Set colADD = Nothing
Set tblADD = Nothing
Set catADD = Nothing
Set rstADD = Nothing

End Sub
 
Top