Get Primary key from Access db

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pavel - 17 Jan 2006 14:02 GMT

Hi there!

I have an Access database and a C++ application (module developed with
managed extensions for C++ indeed).

I can get all tables from database using SQLTable function (ODBC API). But
SQLPrimaryKeys function doesn`t work.

How can i get Primary key for each table in this database from C++, C#
application (not neccesary using ODBC)?

The same question about foreign keys.

Reply to this Message

Douglas J Steele - 17 Jan 2006 16:44 GMT

You could use DAO and check all the indexes in each TableDef objec's Indexes
collection. Only one of them can have the Primary property set to True. Once
you've found the Primary Key index, you can loop through its FIelds
collection to determine what field(s) are in the index.

For Foreign Keys, you'd have to look at the FieIds collection of the
Relation object. The ForeignTable property of the Relation itself will tell
you the name of the table that contains the FK, and the ForeignName property
of the Field object will tell you the name of the FK field in that table.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi there!
>
[quoted text clipped - 8 lines]
>
> The same question about foreign keys.

Reply to this Message

Pavel - 18 Jan 2006 13:28 GMT

Thanks a lot!

I`ll try to use DAO as you suggested.

Yesterday i found that all information about indexes, primary and foreign
keys is returned by SQLStatistics function. Also there are few system tables
- such as MSysObjects, MSysRelations...

May be i can use this information.

> You could use DAO and check all the indexes in each TableDef objec's Indexes
> collection. Only one of them can have the Primary property set to True. Once
[quoted text clipped - 18 lines]
> >
> > The same question about foreign keys.

Reply to this Message

Douglas J Steele - 18 Jan 2006 15:24 GMT

I don't believe the system tables contain field information, just tables.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks a lot!
>
[quoted text clipped - 30 lines]
> > >
> > > The same question about foreign keys.

Reply to this Message

Brendan Reynolds - 18 Jan 2006 12:27 GMT

There's an article at the following URL that shows how to use the ADO
OpenSchema method to get index information. The example uses SQL Server, but
I successfully tested it against a JET 4 database, all I had to change was
the connection. The example is in VB, but looks as though it would not be
very difficult to translate to C#. (I can't comment on how difficult it
might be to translated it to C++, as that is outside my experience).

http://support.microsoft.com/default.aspx?scid=kb;en-us;185979

Signature

Brendan Reynolds
Access MVP

> Hi there!
>
[quoted text clipped - 8 lines]
>
> The same question about foreign keys.

Reply to this Message

Pavel - 18 Jan 2006 14:54 GMT

Brendan, article you posted shows how to get index information, i can do it
in C#. But i need keys information - especially foreign keys.

I tested my application with MS SQL and SYBASE databases - it works. With
Access databases there is a problem - odbc driver for Access doesn`t support
SQLPrimaryKeys and SQLForeignKeys functions. But keys information can be
retrieved via SQLStatistics function.

For example, I have two tables - Table1(id integer PRIMARY KEY, data
VARCHAR(100)) and Table2(id integer PRIMARY KEY, idTable1 integer, data
VARCHAR(100), CONSTRAINT FK_Table2_Table1 FOREIGN KEY (idTable1) REFERENCES
Table1(id)) - so Table2 references Table1.id by idTable1. Table2.data field
is indexed - the index name is MyIndex, for example (Created with "CREATE
INDEX ..." instruction).

I get primary\foreign keys and indexes information using SQLPrimaryKeys,
SQLForeignKeys and SQLStatistics ODBC API functions. My program returns
following information (system tables are omitted):

Table name: Table1; Table type: TABLE; Description:

Indexes:
Column: ; Index name: ; Qualifier: Table1; TYPE: TABLE_STAT; Cardinality:
0; Filter condition:
Column: id; Index name: Index_7E96D473_269D_453A; Qualifier: Table1; TYPE:
OTHER; Cardinality: 0; Filter condition:
Column: data; Index name: MyIndex; Qualifier: Table1; TYPE: OTHER;
Cardinality: 0; Filter condition:

Primary keys:

Foreign keys:

=======================================================
Table name: Table2; Table type: TABLE; Description:

Indexes:
Column: ; Index name: ; Qualifier: Table2; TYPE: TABLE_STAT; Cardinality:
0; Filter condition:
Column: id; Index name: Index_E8CD2AD3_339F_44CD; Qualifier: Table2; TYPE:
OTHER; Cardinality: 0; Filter condition:
Column: idTable1; Index name: FK_Table2_Table1; Qualifier: Table2; TYPE:
OTHER; Cardinality: 0; Filter condition:

Primary keys:

Foreign keys:

=======================================================

As you can see there are no primary and foreign keys (as driver doesn`t
support functions), but they returned as indexes.

The only problem is that I don`t know how to distinguish index and primary
key or primary key and foreign key...

> There's an article at the following URL that shows how to use the ADO
> OpenSchema method to get index information. The example uses SQL Server, but
[quoted text clipped - 17 lines]
> >
> > The same question about foreign keys.

Reply to this Message

Brendan Reynolds - 19 Jan 2006 11:41 GMT

Try the code from the KB article with adSchemaPrimaryKeys or
adSchemaForeignKeys in place of adSchemaIndexes.

Signature

Brendan Reynolds
Access MVP

> Brendan, article you posted shows how to get index information, i can do
> it
[quoted text clipped - 84 lines]
>> >
>> > The same question about foreign keys.

Reply to this Message