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

[JDMils]

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_ ActiveConnectio n(CnnODBC)

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

For Each tblSourceTable_ col In tblSourceTable. Columns
Call ListViewMakeRow (lvLog, Block_def.NoCha nge, "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
FieldNameProper ties = New clsADOX_Collect ionProperties
With FieldNameProper ties
.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.DataTypeEn um.adDBTimeStam p
' Date & Time.
.ColType = ADOX.DataTypeEn um.adDate
Case ADOX.DataTypeEn um.adNumeric
' Currency.
.ColType = ADOX.DataTypeEn um.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.ad GUID Or
ADOX.ColumnAttr ibutesEnum.adCo lFixed
.AutoIncrement = tblSourceTable_ col.Properties( "Jet
OLEDB:AutoGener ate").Value
Case DataTypeEnum.ad Boolean
'skip
Case Else
.AutoIncrement =
tblSourceTable_ col.Properties( "AutoIncrement" ).Value
If Not .AutoIncrement Then
If tblSourceTable_ col.DefinedSize = 0 Then
.FieldSize = 50
Else
.FieldSize =
IIf(tblSourceTa ble_col.Defined Size 65535, 65535,
tblSourceTable_ col.DefinedSize )
End If
If
tblSourceTable_ col.Properties( "Default").Valu e = Nothing Then
.DefaultValue = ""
Else
.DefaultValue =
tblSourceTable_ col.Properties( "Default").Valu e
End If
If tblSourceTable_ col.Properties( "Jet
OLEDB:Allow Zero Length").Value = False Then
.AllowZeroLengt h = False
Else
.AllowZeroLengt h = True
End If
If
tblSourceTable_ col.Properties( "Nullable").Val ue Then
.Required = False
Else
.Required = True
End If
End If
End Select
End With

FieldNameColl.A dd(FieldNamePro perties)

Application.DoE vents()
Next tblSourceTable_ col

VB.Net 2003.

--

|
+-- JDMils
|