Page 1 of 1
Copying tables from MS Access to SQLite
Posted: Tue 13 Aug 2013 16:50
by 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.
Re: Copying tables from MS Access to SQLite
Posted: Wed 14 Aug 2013 09:10
by 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
Re: Copying tables from MS Access to SQLite
Posted: Wed 14 Aug 2013 18:45
by nacTyx
Hello,
AlexP wrote: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.
AlexP wrote:
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?
Re: Copying tables from MS Access to SQLite
Posted: Thu 15 Aug 2013 08:52
by 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.
Re: Copying tables from MS Access to SQLite
Posted: Thu 15 Aug 2013 12:00
by 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?
Re: Copying tables from MS Access to SQLite
Posted: Thu 15 Aug 2013 13:15
by 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.
Re: Copying tables from MS Access to SQLite
Posted: Thu 15 Aug 2013 13:28
by 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.
Re: Copying tables from MS Access to SQLite
Posted: Mon 19 Aug 2013 08:44
by 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.
Re: Copying tables from MS Access to SQLite
Posted: Mon 19 Aug 2013 08:54
by nacTyx
Unfortunately, it is impossible to check the DB state when connected to it.
Thanks a lot. I see
Fix Access "The Changes You Requested To The Table Were Not Successful" error..!
Posted: Tue 29 May 2018 10:58
by 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:
Re: Copying tables from MS Access to SQLite
Posted: Thu 31 May 2018 12:14
by MaximG
We are glad that you found an issue solution. Please specify whether you still have any questions about using our product.