Get Primary Key Column?

[josty]

How can I get the Primary Key column(s) from an MSaccess database-table? I want to make a simple dynamic table-editor to use instead of CFGRID. I found column_list very handy, unfortunately I found no such variable for determining whether or not a field is primary,unique etc.

I tried looking into ADO, but I'm not that much of an expert.

I hope someone here knows how...

 

[RedLion]

When you've got a table myTable with the fields 'Id' and 'Description', with Id as primary key, you can display the data within this table by a query:

<cfquery name=&quot;myQuery&quot; datasource=&quot;myDatabase&quot;>
select Id, description from myTable
</cfquery>

You can display the data with:

<table>
<tr>
<td>Primary key:</td>
<td>Just a field:</td>
</tr>
<cfoutput query=&quot;myQuery&quot;>
<tr>
<td>#Id#</td>
<td>#description#</td>
</tr>
</cfoutput>
</table>

I hope you can do something with it,

Charl

 

[josty]

Thanks for the timely response!
Your answer shows me that my question was not correctly formed. In your answer it is already clear which field is the Primary Key field. What if you only know the table name and dynamically want to determine which of the table columns (maybe by looping over 'queryname.ColumnList') is a Primary Key column?

I cracked my head on this..

 

[philcha]

Might be a good idea to raise this in the MS Access forum.

Most relational DBs hold this information in a special table, usually with a name beginning &quot;sys....&quot;.

The biggest pitfall might be MS Access version changes, as MS keeps changing the internal format of the DB.

Apart from that, watch out for compound keys, e.g. in an Order Lines table the key is usually a combination of Order Number and an order-specific line-number.