Cycling thru Access table fields in DotNet and enumerating column definitions.

I am having trouble finding the AutoNumber field of my database with this
code. The code is used to replicate a specific table, reproducing all
columns including indexes and Primary Keys (there is a good reason to do it
this way), but I can not detect the columns which are Primary Keys as I
cycle thru the column collection

Can someone pls help me?


ADOCatODBC = New ADOX.Catalog
ADOCatODBC.let_ActiveConnection(CnnODBC)

FieldNameColl = New Collection
Call ListViewMakeRow(lvLog, Block_def.NoChange, "GetFieldNames",
"Collecting field objects into collection object")
tblSourceTable = ADOCatODBC.Tables(FromTable) ' Make a link to
the tblSourceTable table.
Call ListViewMakeRow(lvLog, Block_def.NoChange, "GetFieldNames", "",
"Table " & FromTable & " opened")

For Each tblSourceTable_col In tblSourceTable.Columns
Call ListViewMakeRow(lvLog, Block_def.NoChange, "GetFieldNames",
"", "", "Field '" & tblSourceTable_col.Name & "' found. Type is '" &
tblSourceTable_col.Type)
'
' Read the properties of the original fields and place these in
a collection.
'
On Error Resume Next
FieldNameProperties = New clsADOX_CollectionProperties
With FieldNameProperties
.ColName = tblSourceTable_col.Name ' Record the
field name.
'
' For some reason, TimeDate fields come across as
adDBTimeStamp types and Currency fields come across as adNumeric.
' The only field types you can write to the temporary table
are:
'
' Type Number Data Type
' 2 Number (Integer)
' 3 Number (Long Integer)
' 4 Number (Single)
' 5 Number (Double)
' 6 Currency
' 7 Date/Time
' 11 Yes/No
' 17 Number (Byte)
' 72 Number (Replication ID)
' 128 Binary
' 130 Text
' 202 Text
' 203 Memo
' 204 Binary
' 205 OLE Object
'

' The following types returned need to be converted to new
types as VB.Net doesn't
' recognise them.
Select Case tblSourceTable_col.Type
Case ADOX.DataTypeEnum.adDBTimeStamp
' Date & Time.
.ColType = ADOX.DataTypeEnum.adDate
Case ADOX.DataTypeEnum.adNumeric
' Currency.
.ColType = ADOX.DataTypeEnum.adCurrency
Case Else
' The rest ....
.ColType = tblSourceTable_col.Type
End Select
.Precision = tblSourceTable_col.Precision
'
' Depending on the field type, there are certain default
settings which need to be imposed
' if they are not specified on the original field.
' Note that some properties are not defined and by reading
them you may generate an error
' condition. Also, if properties are not defined, defaults
need to be assigned instead.
'
Select Case tblSourceTable_col.Type
Case DataTypeEnum.adGUID Or
ADOX.ColumnAttributesEnum.adColFixed
.AutoIncrement = tblSourceTable_col.Properties("Jet
OLEDB:AutoGenerate").Value
Case DataTypeEnum.adBoolean
'skip
Case Else
.AutoIncrement =
tblSourceTable_col.Properties("AutoIncrement").Value
If Not .AutoIncrement Then
If tblSourceTable_col.DefinedSize = 0 Then
.FieldSize = 50
Else
.FieldSize =
IIf(tblSourceTable_col.DefinedSize > 65535, 65535,
tblSourceTable_col.DefinedSize)
End If
If
tblSourceTable_col.Properties("Default").Value = Nothing Then
.DefaultValue = ""
Else
.DefaultValue =
tblSourceTable_col.Properties("Default").Value
End If
If tblSourceTable_col.Properties("Jet
OLEDB:Allow Zero Length").Value = False Then
.AllowZeroLength = False
Else
.AllowZeroLength = True
End If
If
tblSourceTable_col.Properties("Nullable").Value Then
.Required = False
Else
.Required = True
End If
End If
End Select
End With

FieldNameColl.Add(FieldNameProperties)

Application.DoEvents()
Next tblSourceTable_col



VB.Net 2003.



--

|
+-- JDMils
|