Adding New Field To Access Table

[Tom Loach]

After reviewing the messages on google and using the Access 2000
developers handbook I came up with the following code to add a field
to an access table.
I'm working in Access 2002, and the code below steps through without
a glich. Problem is it doesn't add the column to the table.

Any help appreciated:

Thanks,
Tom

using VB 6 SP 5, windowx XP, Access 2002 , DSNless connection string.
ADO 2.8

Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim cat As ADOX.Catalog
Dim c As ADOX.Column
Dim intI As Integer
Dim FoundIt As Boolean
Dim sqlStr As String
Dim tbl As ADOX.Table

On Error GoTo ErrorCheck

FoundIt = False

Set rs = New ADODB.Recordset
rs.Open "ADTData", objConn
For intI = 0 To rs.Fields.Count - 1
Set fld = rs.Fields(intI)
If fld.Name = "Trigger" Then
FoundIt = True
Exit For
End If
Next intI
rs.Close
Set rs = Nothing

If objConn.State = adStateOpen Then
If Not FoundIt Then
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Set c = New ADOX.Column
c.Name = "Trigger"
c.Type = adChar 'I've also used every version of char I could
'find
c.DefinedSize = 3
cat.ActiveConnection = objConn
tbl.Name = "ADTData"
With tbl.Columns
.Append c
End With
cat.Tables.Refresh
Set cat = Nothing
Set tbl = Nothing
End If
End If

Exit Sub
ErrorCheck:
MsgBox "Error in AddTriggerField" & Err.Description,
vbInformation, "Error"
End Sub

[Pieter Linden]

Maybe it's just because it's late, but I don't see where this line gets a value:

cat.ActiveConnection = objConn

what is the value of objConn? I don't see anything like

Set objConn=CurrentProject.Connection

or anything like that....

[Pieter Linden]

Maybe it's just because it's late, but I don't see where this line gets a value:

cat.ActiveConnection = objConn

what is the value of objConn? I don't see anything like

Set objConn=CurrentProject.Connection

or anything like that....