[AccessD] Identifying Primary Key's

I did change it a bit by adding a routine at the bottom that knew when to 
return "No primary key" -- I just couldn't fit into the If block logic. The 
original only returned "No primary key when there was an index, but no 
primary key -- couldn't figure out how to fit in tables that had no index 
and thus no primary key -- tried and tried but it just never worked right.

And yes, I agree, I've always found ADO required more work than I liked. :)

Susan H.


> Susan,
>
> ADO always seems a little more complex than it ought to be!
>
> Charlotte Foust
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
> Sent: Wednesday, October 22, 2008 5:21 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Identifying Primary Key's
>
> Here's what I ended up with, but seems little more complex than it ought
> to.
>
> Function ListPK(tbl As String)
>  'List primary keys for passed table.
>  'Must reference ADOX library:
>  'Microsoft ADO Ext. 2.8 for DDL and Security.
>  Dim cat As New ADOX.Catalog
>  Dim tblADOX As New ADOX.table
>  Dim idxADOX As New ADOX.Index
>  Dim colADOX As New ADOX.Column
>
>  Dim keyADOX As New ADOX.key
>
>  cat.ActiveConnection = CurrentProject.AccessConnection
>
>  For Each tblADOX In cat.Tables
>    If tblADOX.Name = tbl Then
>      If tblADOX.Indexes.Count <> 0 Then
>        For Each idxADOX In tblADOX.Indexes
>          With idxADOX
>            If .PrimaryKey Then
>              For Each colADOX In .Columns
>                Debug.Print colADOX.Name
>              Next
>            End If
>          End With
>        Next
>      Else
>        Debug.Print "No primary key"
>      End If
>    End If
>  Next
>
> End Function
>
>
>> Here's a little snippet of code that you should be able to figure it
>> out from
>>
>>            For cntFlds = 0 To curTbl.Fields.count - 1
>>                'If Left(curFld.Name, 4) = "bCur" Then Stop
>>                Set curFld = curTbl.Fields(cntFlds)
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com