RE: Updating column property on table gives error

Thanks for all the time you have taken on this.
I have the append column working, but as I mentioned updating the column
properties is a problem.
Below is the code as I ran it just now with debug output to show you exactly
what I am facing.


If oTableSource.Type = "TABLE" Then
' *** Get all fields in the right order to create them
' only the first column is appended the rest are done in ADDFIELDS
Set oRecord = New ADODB.Recordset
oRecord.Open "Select * From [" & .Name & "]",
oCatalogSource.ActiveConnection, adOpenForwardOnly
Set oColDest = New ADOX.Column

With oColDest
.Type =
oTableSource.Columns(oRecord.Fields(0).Name).Type
.Name =
oTableSource.Columns(oRecord.Fields(0).Name).Name
.DefinedSize =
oTableSource.Columns(oRecord.Fields(0).Name).DefinedSize
.Attributes =
oTableSource.Columns(oRecord.Fields(0).Name).Attributes
.ParentCatalog = oTableDest.ParentCatalog
Debug.Print "Destination: "; Tab; oTableDest.Name; " BEFORE APPEND"
Debug.Print "Column: "; .Name; Tab; "Type: "; .Type; Tab; "Size: ";
..DefinedSize
Debug.Print "Source: "; Tab; oTableSource.Name
Debug.Print "Column: "; oTableSource.Columns(oRecord.Fields(0).Name).Name _
; Tab; "Type: ";
oTableSource.Columns(oRecord.Fields(0).Name).Type _
; Tab; "Size: ";
oTableSource.Columns(oRecord.Fields(0).Name).DefinedSize

For iCount = 0 To
oTableSource.Columns(oRecord.Fields(0).Name).Properties.Count - 1
Set oPropertySrc =
oTableSource.Columns(oRecord.Fields(0).Name).Properties(iCount)
.Properties(iCount).Value = oPropertySrc.Value
Debug.Print "Destination: "; Tab; oTableDest.Name; Tab;
..Properties(iCount).Name; Tab; .Properties(iCount).Value
Debug.Print "Source: "; Tab; oTableSource.Name; Tab; oPropertySrc.Name; Tab;
oPropertySrc.Value
Next
End With


.Columns.Append oColDest.Name, oColDest.Type,
oColDest.DefinedSize
Debug.Print
Debug.Print
Debug.Print "Destination: "; Tab; oTableDest.Name; " AFTER APPEND"
Debug.Print "Column: "; oTableDest.Columns(oColDest.Name).Name; Tab; "Type:
" _
; oTableDest.Columns(oColDest.Name).Type; Tab; "Size: ";
oTableDest.Columns(oColDest.Name).DefinedSize

For iCount = 0 To
oTableDest.Columns(oColDest.Name).Properties.Count - 1
Debug.Print "Destination: "; Tab; oTableDest.Name; Tab;
oTableDest.Columns(oColDest.Name).Properties(iCount).Name _
; Tab;
oTableDest.Columns(oColDest.Name).Properties(iCount).Value

Next

'
oTableDest.ParentCatalog.Tables(oTableDest.Name).Columns.Append
(oColDest.Name), oColDest.Type, oColDest.DefinedSize
End If
End With ''With oTableDest


Destination: Control BEFORE APPEND
Column: SystemID Type: 3 Size: 0
Source: Control
Column: SystemID Type: 3 Size: 0
Destination: Control Autoincrement True
Source: Control Autoincrement True
Destination: Control Default
Source: Control Default
Destination: Control Description
Source: Control Description
Destination: Control Nullable True
Source: Control Nullable True
Destination: Control Fixed Length True
Source: Control Fixed Length True
Destination: Control Seed 1
Source: Control Seed 1
Destination: Control Increment 1
Source: Control Increment 1
Destination: Control Jet OLEDB:Column Validation Text
Source: Control Jet OLEDB:Column Validation Text
Destination: Control Jet OLEDB:Column Validation Rule
Source: Control Jet OLEDB:Column Validation Rule
Destination: Control Jet OLEDB:IISAM Not Last Column False
Source: Control Jet OLEDB:IISAM Not Last Column False
Destination: Control Jet OLEDB:AutoGenerate False
Source: Control Jet OLEDB:AutoGenerate False
Destination: Control Jet OLEDB:One BLOB per Page False
Source: Control Jet OLEDB:One BLOB per Page False
Destination: Control Jet OLEDB:Compressed UNICODE Strings False
Source: Control Jet OLEDB:Compressed UNICODE Strings False
Destination: Control Jet OLEDB:Allow Zero Length False
Source: Control Jet OLEDB:Allow Zero Length False
Destination: Control Jet OLEDB:Hyperlink False
Source: Control Jet OLEDB:Hyperlink False


Destination: Control AFTER APPEND
Column: SystemID Type: 3 Size: 0
Destination: Control Autoincrement False
Destination: Control Default
Destination: Control Description
Destination: Control Nullable False
Destination: Control Fixed Length True
Destination: Control Seed 1
Destination: Control Increment 1
Destination: Control Jet OLEDB:Column Validation Text
Destination: Control Jet OLEDB:Column Validation Rule
Destination: Control Jet OLEDB:IISAM Not Last Column False
Destination: Control Jet OLEDB:AutoGenerate False
Destination: Control Jet OLEDB:One BLOB per Page False
Destination: Control Jet OLEDB:Compressed UNICODE Strings False
Destination: Control Jet OLEDB:Allow Zero Length False
Destination: Control Jet OLEDB:Hyperlink False

--
Thanks,
Uwe


"Uwe" wrote:

When it tries to update the column properties it appears that they are read
only and errors where I marked the code with #######.

Set oRecord = New ADODB.Recordset
oRecord.Open "Select * From [" & oTableSource.Name & "]",
oTableSource.ParentCatalog.ActiveConnection, adOpenForwardOnly
For Each oField In oRecord.Fields
Set colMD = oTableSource.Columns(oField.Name)
With colMD

If Not ExistsColumn(.Name, oTableDest) Then

Set colDB = New ADOX.Column
With colDB
.ParentCatalog = catDest
.Name = colMD.Name
.Type = colMD.Type
.DefinedSize = colMD.DefinedSize
.Attributes = colMD.Attributes

End With
oTableDest.Columns.Append colDB
' *** Now as the column is added, set all it's properties
For i = 0 To colMD.Properties.Count - 1
####### colDB.Properties(i).Value =
colMD.Properties(i).Value

Next


Set colDB = Nothing
End If
End With
Set colMD = Nothing
Next

oRecord.Close
Set oRecord = Nothing

--
Thanks,
Uwe
.