delphi with ms-access index not usable in Table.indexName-CodePudding

I use delphi7 and MS Access.

When I try to put an index in the table field IndexName I get this message:

The current provider does not support the interface needed for index functionality

ConnectionString:

rovider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=D:\Informatique\Programmes personnels\MyDataBase.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

I use ADO express.

I just want that my data in the DBGrid be sorted. I get it sorted when I click on the grid title with the function sort in the OnTitleClick event

Table.Field.sort:='MySortedField ASC';

But I want it sorted when I open my form.

It seems I cannot use the created index of my MS Access database. Do I miss something or are the MS Access index not usable with delphi 7?

CodePudding user response：

The following D7 project supports sorting-by-column without encountering the

The current provider does not support the interface needed for index functionality

problem you reported. Note that I have only tested it on an Access table containing exclusively character-type fields such as Char(20)

Create a new D7 project containing:

a TAdoConnection configured to connect to your Access database
a TAdoTable and TAdoCommand which use the TAdoConnection
a TDataSource and TDBGrid to display the contents of the TAdoTable

Add the FormCreate, FormClose and DBGrid1TitleClick event handlers shown below and the DropTable method.

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  AdoTable1.IndexFieldNames := Column.FieldName;
end;

procedure TForm1.DropTable;
begin
  try
    AdoCommand1.CommandText := 'Drop Table ATest;';
    AdoCommand1.Execute;
  except
  end;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  if AdoTable1.Active then
    AdoTable1.Close;
  DropTable;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  //  the following is just to ensure that AdoTable1 is closed
  //  before we attempt to recreate and populate the table

  if AdoTable1.Active then
    AdoTable1.Close;

   try
     AdoCommand1.CommandText := 'Drop table ATest;';
     AdoCommand1.Execute;
   except
   end;

   try
     AdoCommand1.CommandText := 'CREATE TABLE ATest( F1 Char(20) NOT NULL, F2 Char(20) NOT NULL, F3 Char(20) NOT NULL);';
     AdoCommand1.Execute;
   except
   end;
   AdoTable1.Open;
   AdoTable1.InsertRecord(['Row1F1', 'A', 'B']);
   AdoTable1.InsertRecord(['Row3F1', 'B', 'A']);
   AdoTable1.InsertRecord(['Row2F1', 'C', 'C']);
end;

Set the "Stop on Language Exceptions" flag to false in the debugger options then compile and run the project. It deletes and re-creates the ATest table and populares it with three rows. When you click one of the three column titles it sorts the data according to the clicked column. Note the code in

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  AdoTable1.IndexFieldNames := Column.FieldName;
end;

This ignores the actual text in the column title since that may differ from the field's name and uses the clicked column's FieldName property instead.

This example project avoids the necessity to close and re-open the table with a custom Order By clause as suggested in one of my comments (since deleted) earlier.

As mentioned above, I have only tested the code against an Access table containing exclusively character-type fields and you will need to test the code against any other column types you wish to support. Obviously, it would be trivial to avoid trying to sort the grid by a column type for which column-sorting doesn't actually work.

CodePudding user response：

At least I use ADOCommand and build a table from code which kann be useful if acces is not available. What I wanted was by opening the form to have the colonne sorted as I wanted. After looking at your example, I remember that in my shop software I did have that sorted out. After having a look at it I saw what I did 20j ago

in FormActivate I had this code and now when I open the form the colonne are nicely ordered as I wanted.

 ADOTable.Sort:=DBGrid6.Columns[3].FieldName;
 DBGrid6.Columns[3].Title.Color:=clYellow;

Case closed and I learn something more. Thanks and have a good week.