CODEWITHSUNDEEP
sqldelphims-accessado
Carl
Carl

Reputation: 23

Record not insert in the end of access file with delphi

Im reading one Access database with ADO and copying the data to another with the same structure. My problem is that sometime, it dont insert at the end of the file.

  DM1.ADOConnectionReadAccess.ConnectionString :=
      'Provider=Microsoft.Jet.OLEDB.4.0;' + 'User ID=Admin;' +
      'Data Source=' + Directory + 'Courbe\Courbe Actuel.mdb' + ';' +
      'Mode=Share Deny None;Extended Properties="";' + 'Jet OLEDB:Engine Type=5;';
  DM1.ADOConnectionReadAccess.Connected := true;

  DM1.ADODataSetReadAccess.CommandText := 'select * from Courbe_Reservoir order by DateHeure';
  DM1.ADODataSetReadAccess.Active := true;

So i read my database and order it by DateTime. Now, i have to do some change on the data when i copy it to the new database. 

  DM1.ADOConnectionCopyExcel.ConnectionString :=
      'Provider=Microsoft.Jet.OLEDB.4.0;' +
      'User ID=Admin;' +
      'Data Source=' + Directory + 'Courbe\Part of curve.mdb' + ';' +
      'Mode=Share Deny None;Extended Properties="";' +
      'Jet OLEDB:Engine Type=5;';
  DM1.ADOConnectionCopyExcel.Connected := true;


  while not DM1.ADODataSetReadAccess.Eof do
    begin
      Date2 := DM1.ADODataSetReadAccess.FieldByName('DateHeure').AsDateTime;   
      DernierDebit := DernierDebit  + DM1.ADODataSetReadAccess.FieldByName('DebitRes').AsFloat / 1000;


      DM1.ADOCommandCopyExcel.CommandText :=
                              'INSERT INTO Courbe_Reservoir' + ' VALUES (:DateHeure1, ' +
                              ':Niveau, ' +        //niveau
                              ':DebitRes, ' +      //débit entrée
                              ':PH, ' +            //ph
                              ':Chlore, ' +        //Chlore
                              ':Turbidite, ' +     //Turbidité
                              ':Temp, ' +          //température
                              '0, ' +              //Consommation l/min
                              '0, ' +              //Log
                              ':DernierDebit, ' +  //Consommation journalière
                              '0, 0, 0, '''', '''', ''''' + ')';
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('DateHeure1').Value := Date1;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('Niveau').Value := DM1.ADODataSetReadAccess.FieldByName('Niveau').AsFloat;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('DebitRes').Value := DM1.ADODataSetReadAccess.FieldByName('DebitRes').AsFloat;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('PH').Value := DM1.ADODataSetReadAccess.FieldByName('PH').AsFloat;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('Chlore').Value := DM1.ADODataSetReadAccess.FieldByName('Chlore').AsFloat;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('Turbidite').Value := DM1.ADODataSetReadAccess.FieldByName('Turbidite').AsFloat;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('Temp').Value := DM1.ADODataSetReadAccess.FieldByName('Temp').AsFloat;
      DM1.ADOCommandCopyExcel.Parameters.ParamByName('DernierDebit').Value := DernierDebit;
      DM1.ADOCommandCopyExcel.Execute;
     end;

When i check the new database, i got the following result in the image : Part of access database

As you can see, i got a gap in time because i got a record for each minute. The missing record in the image are located elsewhere in the database.

In the end, i want to know why its not show in the same order i insert them. The bottom line is i want to export the new access database (.mdb) to an excel file using an export command but the export command seem to take the access file and copy it to excel as is, so no way to order it because the export. I dont want to go throught all the record again with a select and insert it in excel one after the other.

Right now, i got a file with almost 180 000 record and i need to split it per month, so the new database will have around 44 000 record.

Thanks for your help

Upvotes: 1

Views: 668

Answers (2)

Kenneth Hippolite
Kenneth Hippolite

Reputation: 96

I believe the records should appear in the "order" you inserted them due to the nature of .mdb files (in a multi-user db this would be chaos to be anything else!). Try adding an autonumber if you don't already have one -- you can then double check after what the order the autonum's were assigned in.

I put order in quotes because order is implied by some field (eg autonum). If there is no autonum, we cannot expect the results in any particular way

Upvotes: 0

Rob Kennedy
Rob Kennedy

Reputation: 163317

Insertion location is not defined in most databases — the database engine has the liberty to store data wherever it deems most appropriate. It might choose to add records to the end of the table, but it might also choose to reuse space vacated by previously deleted records.

If you want results in a particular order, then indicate that in your selection query, typically by including an ORDER BY clause.

Upvotes: 3

Related Questions