PRB: Cannot Retrieve Primary Key Using ADOX Key Object Collection

When you use an ADOX key object collection to retrieve the primary key column information of a Microsoft Access table or SQL Server table, a C++ exception may occur. Microsoft Visual Basic code may return the following error:

To retrieve columns used by the primary key, ADOX uses the IDBSchemaRowset::GetRowset method with DBSCHEMA_KEY_COLUMN_USAGE, which is not supported by the SQL Server OLE DB provider (SQLOLEDB) provider and is supported only by the latest version of the Jet OLE DB Provider which is installed with the latest version of the Jet Service Pack.

For additional information about how to obtain the Jet service pack, click the following article number to view the article in the Microsoft Knowledge Base:

This behavior is by design.

Steps to Reproduce Behavior

1. Paste the following code into a .cpp file and build it as a Win32 console project:
   
   Note You must change the User ID <username> and the password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   #import "c:\Program Files\Common Files\System\ADO\Msadox.dll" no_namespace rename("EOF", "EndOfFile")
   #include <stdio.h>
   
   void dump_com_error(_com_error &e)
   {
   printf("Error\n");
   printf("\a\tCode = %08lx\n", e.Error());
   printf("\a\tCode meaning = %s", e.ErrorMessage());
   _bstr_t bstrSource(e.Source());
   _bstr_t bstrDescription(e.Description());
   printf("\a\tSource = %s\n", (LPCSTR) bstrSource);
   printf("\a\tDescription = %s\n", (LPCSTR) bstrDescription);
   }
   
   void main()
   {
   	HRESULT hr;   
   
   	CoInitialize(NULL);
   
   	try{
   
   		_CatalogPtr cat, cat1;
   	
   		hr= cat.CreateInstance(__uuidof(Catalog));
   
   		//Test Jet.
   		/*
   		hr = cat->put_ActiveConnection(_variant_t("Provider=Microsoft.Jet.OLEDB.4.0;" "Data Source=C:\\Program Files\\Microsoft Visual Studio\\VB98\\nwind.mdb"));
   		*/ 
   		
   		//Test SQLOLEDB.
   		
   		hr = cat->put_ActiveConnection(_variant_t("Provider=SQLOLEDB.1;User ID=<user name>; password=<strong password>;Initial Catalog=Northwind; Data Source=SQLServer1"));
   	
   
   		_TablePtr tblEmp, tblEmp1;
   		tblEmp = cat->Tables->Item["Employees"];
   		
   		//Retrieve the key information.
   		KeysPtr keys;
   		keys = tblEmp->Keys;
   
   		_bstr_t keyName, keyColName;
   		
   		long keyCount = keys->Count;
   		long  keyType;
   		_KeyPtr key;
   
   		for(int i = 0; i< keyCount; i++)
   		{
   			key = keys->Item[(long)i];
   			
   			keyName = key->Name;
   		   	 keyType = key->Type;
   			//adKeyPrimary=1, adKeyForeign=2, adKeyUnique= 3
   			printf("key %d : %s  type = %i \n- columns for this key: \n", i, (LPCSTR)keyName, keyType);
   
   			ColumnsPtr keyCols = key->Columns;
   			long g = keyCols->Count;
   
   			for (int j=0; j<g; j++)
   			{
   				printf("-- Col %d:  %s\n",j,(LPCSTR)keyCols->Item[(long)i]->Name);
   			}	
   		    
   		}
   	}
   
   	 catch (_com_error &e)
      {
         dump_com_error(e);
      }
   
   	CoUninitialize();
   }
   					

2. Set a breakpoint at the call to CoUninitialize().
3. Compile the project and do a debug run (F5). You will see following error returned in the console window:
   Error Code = 800a0cb3 Code meaning = Unknown error 0x800A0CB3 Source = (null) Description = (null)