list of primary keys

K

kahriboo

Hi everybody ...

Is there a way to know in access the list of the primary keys of a
table T1 with SQL ?

Is there a systeme table like MSysRelationships but with primary keys
??


thanks

kahriboo
 
A

Arvin Meyer

kahriboo said:
Hi everybody ...

Is there a way to know in access the list of the primary keys of a
table T1 with SQL ?

Is there a systeme table like MSysRelationships but with primary keys
??
Click to expand...

If all the primary keys are named "PrimaryKey", you can use the following
code to get their fields:

Sub FindKeys(TableName As String)
' Arvin Meyer

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
Set idx = tdf.Indexes("PrimaryKey")

For Each fld In idx.Fields
Debug.Print fld.Name
Next

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
U

UpRider

Gee, Arvin. A routine to tell me that the Primary Key name is
"PrimaryKey", but only if I already know that..
:=) UpRider
 
A

Arvin Meyer

Wrong. The routine, had you looked a bit harder, returns the name of the
field(s) which comprise the primary key. Since the default name for primary
keys is ... stay with me here ... "PrimaryKey", it's a reasonably good bet
that it will return ... are you ready? the name of the primary key fields,
not the name of the primary key index.

I used the code to set a variable = to the primary key field, but it could
be easily added to to iterate through all the tables and build a table of
keys, or with a little more effort, of all indexes.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
U

UpRider

Arvin, you are quite right. The table that I tried it with had a primary key
name set as the field name, so that's where the wheels came off for me. I
have never taken the default name as I always name the key and the field the
same. No particular reason, just the way I do it.

UpRider
 
Top