How do I retrieve primary key information from my Microsoft Access database using Database Toolbox 3.2 (R2006b)?

I am using the PRIMARYKEYS function in Database Toolbox to retrieve primary key information for one of the tables in my Microsoft Access database. The result comes back empty, although I know I have specified a primary key for my table:

pk = primarykeys(dbmeta, catalogname, schemaname, tablename)

where "dbmeta" is the database meta-data object.

Answer 1

[MathWorks Support Team]

The metadata-specific methods, such as PRIMARYKEYS, work differently with different databases and we have seen that they have been unreliable with Microsoft Access. The ODBC drivers provided by Microsoft Access may not support querying of primary key information.

To work around this, you can query Microsoft Access for index information. Microsoft Access builds an index on the primary key column for each table. You can use the INDEXINFO function provided by Database Toolbox to retrieve index information for your table. For more information on INDEXINFO, type

doc indexinfo

at the MATLAB command prompt. The following snippet of code illustrates how to retrieve primary key information using this function:

indexes = indexinfo(dbmeta, catalogname, schemaname, tablename);

% loop through the indexes

for i=1:size(indexes,1)   

      % get current index

      index = {indexes{i,:}};  

      % column 7 contains index type, we want primary keys

      if(strcmp(index{7}, 'PrimaryKey'))  

         % column 9 contains column name associated with this index

          strcat('Attribute ', index{9}, ' of table ', tab(1), ' is a primary key')

      end

end