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.ActiveConne ction = objConn
tbl.Name = "ADTData"
With tbl.Columns
.Append c
End With
cat.Tables.Refr esh
Set cat = Nothing
Set tbl = Nothing
End If
End If
Exit Sub
ErrorCheck:
MsgBox "Error in AddTriggerField " & Err.Description ,
vbInformation, "Error"
End Sub
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.ActiveConne ction = objConn
tbl.Name = "ADTData"
With tbl.Columns
.Append c
End With
cat.Tables.Refr esh
Set cat = Nothing
Set tbl = Nothing
End If
End If
Exit Sub
ErrorCheck:
MsgBox "Error in AddTriggerField " & Err.Description ,
vbInformation, "Error"
End Sub
Comment