Programmatically find the MS-Access 2007 Field Index table that should be present? TField.IsIndexedField not working

Question

My table Clients have a UserID field that is indexed.

Now, when I remove this field from delphi, I get EOleExecption as its indexed field. I tried with the following code:

ObjCustomers := TADOTable.Create(nil); ObjCustomers.Connection := Connection; ObjCustomers.TableName := 'Customers'; ObjCustomers.Open; if (ObjCustomers.FindField('UserID').IsIndexField) then begin ExecuteSQLStatements(['DROP INDEX UserID ON Customers']); end; 

But this Tfield.IsIndexField is suitable for this case False. Next, I do not want to do something like this:

 try ExecuteSQLStatements(['DROP INDEX UserID ON Customers']); except on E: exception do end; 

Is there a way so that I can check if the field is indexed before executing the SQL query?

Thanks in advance!

Answer 1

GetIsIndexField not implemented by TADODataSet , and the result will be False .

Use TADOConnection.OpenSchema to get table indexes:

 var DataSet: TADODataSet; DataSet := TADODataSet.Create(nil); try Connection.OpenSchema(siIndexes, VarArrayOf([Unassigned, Unassigned, Unassigned, Unassigned, 'Customers']), EmptyParam, DataSet); while not DataSet.Eof do begin ShowMessage(DataSet.FieldByName('INDEX_NAME').AsString); DataSet.Next; end; finally DataSet.Free; end; 

---

To complete this answer: As suggested by TLama , you can use the TADODataSet GetIndexNames method.
ADO internally uses Command.ActiveConnection.OpenSchema(adSchemaIndexes...

 function IsIndexField(DataSet: TADODataSet; FieldName: string): Boolean; var SL: TStringList; begin SL := TStringList.Create; try DataSet.GetIndexNames(SL); Result := SL.IndexOf(FieldName) <> -1; finally SL.Free; end; end; procedure TForm1.Button1Click(Sender: TObject); var ObjCustomers: TADOTable; begin ObjCustomers := TADOTable.Create(nil); ObjCustomers.Connection := Connection; ObjCustomers.TableName := 'Customers'; if IsIndexField(TADODataSet(ObjCustomers), 'UserID') then begin Showmessage('Index'); Connection.Execute('DROP INDEX UserID ON Customers'); end else Showmessage('Not Index'); // ObjCustomers.Open; ObjCustomers.Free; end; 

Answer 2

 VAR AdoTbl:TAdoDataset; BEGIN AdoTbl:=TAdoDataset.Create(Self); // use TAdoDataset AdoTbl.Connection :=MyAdoConnection; AdoTbl.CommandType:=cmdTable; //Importent !! AdoTbl.CommandText:='Refx_Ceramics_Hist_PreHist'; //Tablename AdoTbl.GetIndexNames(ListBox1.Items); END; 

This works for me on DelphiXE2