Post by JahelkaI think the main problem with my trial run is that I was trying to
just update everything, whether it changed or not. Of course, that is
because I thought this would be easy. =) Hopefully I am very close
to figuring out what doesn't work and why...
Some initial findings:
So far I have just been trying to UPDATE and APPEND columns in an
existing table that has data.
Here is my code with findings commented for UPDATING an existing
column:
Dim conADO As New ADODB.Connection
Dim Cat As New ADOX.Catalog
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
My.Settings.TargetMDB & ";Jet OLEDB:Engine Type=5;")
Cat.ActiveConnection = conADO
Dim col As ADOX.Column
col = Cat.Tables(gTblName).Columns(gColName)
' cannot CHANGE!
If txbDefinedSize.Tag = "Changed" Then
If IsNumeric(txbDefinedSize.Text) Then
col.DefinedSize = CInt(txbDefinedSize.Text)
Else
col.DefinedSize = 0
End If
End If
' cannot CHANGE!
If txbAttributes.Tag = "Changed" Then
col.Attributes = CInt(txbAttributes.Text)
End If
If txbDefaultValue.Tag = "Changed" Then
col.Properties("Default").Value = txbDefaultValue.Text
End If
If txbDescription.Tag = "Changed" Then
col.Properties("Description").Value = txbDescription.Text
End If
' cannot CHANGE!
If ckbNullable.Tag = "Changed" Then
col.Properties("Nullable").Value = ckbNullable.Checked
End If
If ckbAllowZeroLength.Tag = "Changed" Then
col.Properties("Jet OLEDB:Allow Zero Length").Value =
ckbAllowZeroLength.Checked
End If
' cannot CHANGE!
If ckbCompressed.Tag = "Changed" Then
col.Properties("Jet OLEDB:Compressed UNICODE
Strings").Value = ckbCompressed.Checked
End If
Cat = Nothing
conADO.Close()
conADO = Nothing
Here is my code for APPENDING a column to an existing table WITH data:
Dim conADO As New ADODB.Connection
Dim Cat As New ADOX.Catalog
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
My.Settings.TargetMDB & ";Jet OLEDB:Engine Type=5;")
Cat.ActiveConnection = conADO
Dim tbl As New ADOX.Table
Dim col As New ADOX.Column
tbl = Cat.Tables(gTblName)
If txbDefinedSize.Text.Length = 0 Then
tbl.Columns.Append(txbName.Text, cbxType.SelectedValue)
Else
tbl.Columns.Append(txbName.Text, cbxType.SelectedValue,
CInt(txbDefinedSize.Text))
End If
col = tbl.Columns(txbName.Text)
'col.ParentCatalog = Cat ' ERROR: Operation is not allowed in
this context. (makes sense to me, the table already exists in this
case)
' cannot CHANGE!
If txbAttributes.Tag = "Changed" Then
col.Attributes = CInt(txbAttributes.Text)
End If
If txbDefaultValue.Tag = "Changed" Then
col.Properties("Default").Value = txbDefaultValue.Text
End If
If txbDescription.Tag = "Changed" Then
col.Properties("Description").Value = txbDescription.Text
End If
' cannot CHANGE!
If ckbNullable.Tag = "Changed" Then
col.Properties("Nullable").Value = ckbNullable.Checked
End If
If ckbAllowZeroLength.Tag = "Changed" Then
col.Properties("Jet OLEDB:Allow Zero Length").Value =
ckbAllowZeroLength.Checked
End If
' cannot CHANGE!
If ckbCompressed.Tag = "Changed" Then
col.Properties("Jet OLEDB:Compressed UNICODE
Strings").Value = ckbCompressed.Checked
End If
Cat = Nothing
conADO.Close()
conADO = Nothing
As you (and more importantly, ME) can see, there are a lot of things
you can't do with ADOX (at least with an existing schema and data)!
The more I think about it the more I suppose that it makes sense that
this is a much more complicated thing to do than I give it credit
for. Although I have seen MS program some very complicated stuff,
what I'm trying to do still seems pale in comparison. =)
Next I'll move on to adding a completely new table and see what ADOX
can and can't do when I do that. It is looking to me like my only
solution is going to have to include using DDL AND ADOX. Shesh, just
what I really wanted to do, have to use two completely different
technologies to accomplish one task. LOL!