I'm using DotNet code to cycle thru all fields of an Access table. I'm
trying to detect which of the fields are Primary Keys. I tried the following
code:

-----------------------------------------------------
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
.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
------------------------------------------------------------------------

The line:

..AutoIncrement = tblSourceTable_col.Properties("AutoIncrement").Value

Doesn't work for me. Am I doing something wrong?





--

|
+-- JDMils
|

I think it might be simpler to use the FillSchema method of an
OleDbDataAdapter, then examine the properties of the resulting DataTable.
Something like ...

Dim da As New System.Data.OleDb.OleDbDataAdapter(cmd)
Dim ds As New System.Data.DataSet
da.FillSchema(ds, SchemaType.Source)
Dim col As System.Data.DataColumn
For Each col In ds.Tables(0).PrimaryKey
Console.WriteLine(col.ColumnName)
Next

But you might get a more definitive answer in an ADO.NET forum.

--
Brendan Reynolds
Access MVP

"JDMils" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm using DotNet code to cycle thru all fields of an Access table. I'm
> trying to detect which of the fields are Primary Keys. I tried the
> following code:
>
> -----------------------------------------------------
> 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
> .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
> ------------------------------------------------------------------------
>
> The line:
>
> .AutoIncrement = tblSourceTable_col.Properties("AutoIncrement").Value
>
> Doesn't work for me. Am I doing something wrong?
>
>
>
>
>
> --
>
> |
> +-- JDMils
> |
>
>

Primary keys are indexes and are not propertie of a column/field.
(Otherwise, it would be hard to have a multi-field primary key.) Take a
look at:

http://support.microsoft.com/?id=258013
http://support.microsoft.com/?id=294157

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"JDMils" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm using DotNet code to cycle thru all fields of an Access table. I'm
> trying to detect which of the fields are Primary Keys. I tried the
> following code:
>
> -----------------------------------------------------
> 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
> .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
> ------------------------------------------------------------------------
>
> The line:
>
> .AutoIncrement = tblSourceTable_col.Properties("AutoIncrement").Value
>
> Doesn't work for me. Am I doing something wrong?
>
>
>
>
>
> --
>
> |
> +-- JDMils
> |
>
>

JDMils wrote:
> .AutoIncrement = tblSourceTable_col.Properties("AutoIncrement").Value
>
> Doesn't work for me. Am I doing something wrong?

What is the symptom: exception, missing/erroneous value?

My own equivalent code fails over any error, so maybe I've had problems
too? e.g. (VBA)

isAutoincrement = False
On Error Resume Next
isAutoincrement = _
cat.Tables(tableName).Columns(columnName).Properties("Autoincrement").Value
On Error Goto 0

Jamie.

--

Sylvain Lafontaine (fill the blanks, no spam please) wrote:
> Primary keys are indexes and are not propertie of a column/field.
> (Otherwise, it would be hard to have a multi-field primary key.)

Mixing logical and physical here: a PRIMARY KEY or CONSTRAINT UNIQUE
(logical) is implemented in Jet using an index (physical). The fact an
index is used, rather than some other implementation, is irrelevant in
this case.

A column could legitimately have a property 'is a PK column' to
indicate it is a member of the set 'PK columns'.

Jamie.

--

You're right about mixing logical and physical but to my knowledge, ADOX is
part of the physical world: there is no such thing a ADOX in the theoritical
definition of the SQL language and of relational databases.

Also, in the case of the statement:
..AutoIncrement = tblSourceTable_col.Properties("AutoIncrement").Value

the AutoIncrement property doesn't mean necessarily that the column is a
primary key or part of the primary key.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Jamie Collins" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Sylvain Lafontaine (fill the blanks, no spam please) wrote:
>> Primary keys are indexes and are not propertie of a column/field.
>> (Otherwise, it would be hard to have a multi-field primary key.)
>
> Mixing logical and physical here: a PRIMARY KEY or CONSTRAINT UNIQUE
> (logical) is implemented in Jet using an index (physical). The fact an
> index is used, rather than some other implementation, is irrelevant in
> this case.
>
> A column could legitimately have a property 'is a PK column' to
> indicate it is a member of the set 'PK columns'.
>
> Jamie.
>
> --
>