Copying tables from MS Access to SQLite

[nacTyx]

Hello everybody,

I need your assistance GURU.
My task: I have database in MS Access and I need copy all tables to SQLite database.
I have used the example from: http://forums.devart.com/viewtopic.php?p=93978#p93978 from AlexP (respect for you great work). All works but very very slowly, one table from my database has 1935319 records and in fact I have waited an hour but I have copied only 512 records . May be someone have a solution for this? May be should use other database as destination? Any examples will help me.

Best Regars,
-- Dmitry.

[AlexP]

Hello,

This problem is due to the SQLite behaviour on loading large amounts of data. To increase loading speed, Commit should be periodically called. For the time being, to solve the problem, you can use UniLoader in the following way

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Uni,
  UniLoader,
  DALoader,
  SQLiteUniProvider,
  AccessUniProvider;

type
  TMyClass = class
  private
    AccessUniConnection: TUniConnection;
    LiteUniConnection: TUniConnection;
    AccessUniTable: TUniTable;
    UniLoader: TUniLoader;
    procedure PutData(Sender: TDALoader);
  public
    procedure Load;
  end;

{ TMyClass }

procedure TMyClass.Load();
begin
  AccessUniConnection := TUniConnection.Create(nil);
  try
    AccessUniConnection.Connect('ProviderName=Access;Database=d:\src.MDB');
    LiteUniConnection := TUniConnection.Create(nil);
    try
      LiteUniConnection.Connect('ProviderName=SQLite;Database=d:\Dst.db3');
      AccessUniTable := TUniTable.Create(nil);
      try
        AccessUniTable.Connection := AccessUniConnection;
        AccessUniTable.TableName := 'SRC';
        AccessUniTable.Open;
        UniLoader := TUniLoader.Create(nil);
        try
          UniLoader.Connection := LiteUniConnection;
          UniLoader.TableName := 'DST';
          UniLoader.OnPutData := PutData;
          UniLoader.Load;
        finally
          UniLoader.Free;
        end;
       finally
        AccessUniTable.Free;
      end;
    finally
      LiteUniConnection.Free;
    end;
  finally
    AccessUniConnection.Free;
  end;
end;

procedure TMyClass.PutData(Sender: TDALoader);
var
   i: Integer;
begin
   i := 1;
   LiteUniConnection.StartTransaction;
   while not AccessUniTable.eof do begin
     Sender.PutColumnData(0, i, AccessUniTable.Fields[0].Value);
     Sender.PutColumnData(.., i, AccessUniTable.Fields[..].Value);
     Sender.PutColumnData(N, i, AccessUniTable.Fields[N].Value);
     AccessUniTable.Next;
     inc(i);
     if (i mod 1000) = 0 then
     begin
      LiteUniConnection.Commit;
      LiteUniConnection.StartTransaction;
     end;
   end;
end;
var
  MyClass: TMyClass;

begin
  MyClass := TMyClass.Create;
  try
    MyClass.Load;
  finally
    MyClass.Free;
  end;
end.

we will add the AutoCommitRowCount proeprty to UniLoader in future

[nacTyx]

Hello,

Hello,

This problem is due to the SQLite behaviour on loading large amounts of data. To increase loading speed, Commit should be periodically called. For the time being, to solve the problem, you can use UniLoader in the following way

I see what you mean. Thanks a lot.

UniLoader := TUniLoader.Create(nil);
try
UniLoader.Connection := LiteUniConnection;
UniLoader.TableName := 'DST';
UniLoader.OnPutData := PutData;
UniLoader.Load;
finally
UniLoader.Free;
end;
we will add the AutoCommitRowCount proeprty to UniLoader in future

As I understood before loading I will need to create a table in SQLite database. Am I right?

[AlexP]

Hello,

Yes. If there is no table, it should be created before data loading. An example of creating a SQLite table with the MS Access table structure can be found at http://forums.devart.com/viewtopic.php?p=93978 . But note, that MS Access data types differ from SQLite types, therefore you should correctly specify SQLite types dependently on MS Access types.

[nacTyx]

I was able successfuly copy data from MDB file to SQLite. But I have some issue with copying data from MS SQL. When I try to get table info about DATA_TYPE

Code: Select all

   SQL := SQL + MSSqlMetaData.FieldByName('COLUMN_NAME').AsString + ' ' +
                       MSSqlMetaData.FieldByName('DATA_TYPE').AsString;
          if Pos('CHAR', MSSqlMetaData.FieldByName('DATA_TYPE').AsString) > 0 then
            SQL := SQL + '(' + MSSqlMetaData.FieldByName('DATA_LENGTH').AsString + ')';

DATA_TYPE always returned number instead of String value as for MDB. In fact my SQL query for creating table failed. For MDB it works fine. Any idea?

[AlexP]

Hello,

You can retrieve the type name by its number, for example, using the following method:

Code: Select all

function GetTypeName(Value: integer): string;
begin
  UniConnection1.ExecSQL('SELECT :name = name from sys.types WHERE system_type_id = :type_id',['', Value]);
  Result := UniConnection1.ParamByName('name').AsString;
end;

We will change the TUniMetaData component behaviour in one of the next versions for retrieving the type name instead of its number.

[nacTyx]

Thanks a lot.

By the way may be you implement method for getting current infromation from SQLlite about encryption. I mean it would be great to have the method which returned status encrypted database or not.

[AlexP]

Hello,

Unfortunately, SQLite has no method for defining DB state (encrypted/decrypted). To check this, you can execute a query to one of the tables (e.g, sqlite_master). Unfortunately, it is impossible to check the DB state when connected to it.

[nacTyx]

Unfortunately, it is impossible to check the DB state when connected to it.

Thanks a lot. I see

[edwards142]

I fetch this “changes you have requested to the table were not successful “error in my Access Database when I tries to insert any new record in the table.

At that time the application throws the following error message:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

This error occurs due to the creation of duplicate values in the index, primary key, or relationship. So you need to change the data in the field that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again. This problem generally arises at the time of saving changes to a report or generating a report.

To get rid of the Access Database table issue, have a look over the fixes:

http://www.accessrepairnrecovery.com/bl ... error-3022

[MaximG]

We are glad that you found an issue solution. Please specify whether you still have any questions about using our product.