Detect a column which is a Primary Key.

[JDMils]

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?

 

[Brendan Reynolds]

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.

 

[Sylvain Lafontaine]

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

 

[Jamie Collins]

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

--

 

[Jamie Collins]

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.

--

 

[Sylvain Lafontaine]

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.