Re: Get Primary key from Access db

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" пишет:

> 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
>
> --
> Brendan Reynolds
> Access MVP
>
>
> "Pavel" <Pavel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:000F2EA1-77A3-4D7D-8F08-181A38EBB2A0@xxxxxxxxxxxxxxxx
> > 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.
> >
>
>
>
.

Relevant Pages