MATLAB: How to retrieve primary key information from the Microsoft Access database using Database Toolbox 3.2 (R2006b)

Database Toolbox

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.

Best Answer

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
Related Question