Get Primary key from Access db

[Guest]

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.

 

[Douglas J Steele]

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.

 

[Brendan Reynolds]

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

 

[Guest]

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.

"Douglas J Steele" пишет:

 

[Guest]

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

"Brendan Reynolds" пишет:

 

[Douglas J Steele]

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

 

[Brendan Reynolds]

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