Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: Copy MS Access Table to SQLite



Permlink Replies: 7 - Last Post: Apr 6, 2018 1:13 PM Last Post By: Bob Carson Threads: [ Previous | Next ]
Gernot Baecker

Posts: 9
Registered: 9/28/98
Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2015 8:31 AM
Hello

I try to transfer a database from MS Access to SQLite. I have a working version using CopyDataSet but it is slow (takes about 15min to transfer). Since I need to make the transfer regularly I tried the following code (2 connections):


tbl_SQLITE.TableName := 'Video';
tbl_Access.Open('Video');
tbl_SQLITE.Data := tbl_Access.Data;
tbl_SQLITE.Close;


Unfortunately no data get transferred. Am I doing something wrong or does this simply not work for this combination?

Kind regards
Gernot
Gernot Baecker

Posts: 9
Registered: 9/28/98
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2015 8:57 AM   in response to: Gernot Baecker in response to: Gernot Baecker
I also tried:

  with TFDBatchMoveDataSetReader.Create(FDBatchMove1) do begin
    DataSet := tbl_Access;
    Optimise := False;
  end;
  with TFDBatchMoveDataSetWriter.Create(FDBatchMove1) do begin
    DataSet := tbl_SQLITE;
    Optimise := False;
  end;
  FDBatchMove1.Execute;


But that gives me the following error

First chance exception at $74FD4598. Exception class EMSAccessNativeException with message '[FireDAC][Phys][ODBC][Microsoft][ODBC Microsoft Access Driver] 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.


That is weird as Access is the source and there is nothing written to it.

Any help would be appreciated

Kind regards
gernot
Cristian Peța

Posts: 157
Registered: 8/7/06
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2015 12:44 PM   in response to: Gernot Baecker in response to: Gernot Baecker
Gernot Baecker wrote:
Hello

I try to transfer a database from MS Access to SQLite. I have a working version using CopyDataSet but it is slow (takes about 15min to transfer). Since I need to make the transfer regularly I tried the following code (2 connections):

{code}

tbl_SQLITE.TableName := 'Video';
tbl_Access.Open('Video');
tbl_SQLITE.Data := tbl_Access.Data;
"Data := Data" if it is working will move data only in memory. I you need to write this to database "Delta := Data" then applying this to the database would have more sense. But I doubt it will work.
But if you need a big performance gain to massively writing in SQLite you need to start a transaction before CopyDataSet and commit after. If you haven't done this then try it. If the table is too big you will need to split this in more transactions.

Best regards,
Cristian Peta
Gernot Baecker

Posts: 9
Registered: 9/28/98
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2015 10:02 PM   in response to: Gernot Baecker in response to: Gernot Baecker
Commit certainly helps (is already part of the program). I was wondering about the splitting of transactions? How are you doing this with CopyDataSet?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2015 10:31 PM   in response to: Gernot Baecker in response to: Gernot Baecker
How are you doing this with CopyDataSet?

CopyDataSet does not control transactions. But TFDDataMove controls.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Cristian Peța

Posts: 157
Registered: 8/7/06
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 15, 2015 11:23 PM   in response to: Gernot Baecker in response to: Gernot Baecker
Gernot Baecker wrote:
Commit certainly helps (is already part of the program). I was wondering about the splitting of transactions? How are you doing this with CopyDataSet?
With CopyDataSet only splitting source. But this is only to save some memory if you have for example an 2GB table.
But I wondering how big is your data to take 15 minutes. If I move for example from MySQL to SQLite 110MB it takes 6 seconds. I use other components but in 15 minutes writing with transaction batches in SQLite you can write some GB of data.

Best regards,
Cristian Peta
Fabio Lopes

Posts: 1
Registered: 6/8/14
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Mar 28, 2018 10:21 AM   in response to: Cristian Peța in response to: Cristian Peța
Cristian Peța wrote:
Gernot Baecker wrote:
Commit certainly helps (is already part of the program). I was wondering about the splitting of transactions? How are you doing this with CopyDataSet?
With CopyDataSet only splitting source. But this is only to save some memory if you have for example an 2GB table.
But I wondering how big is your data to take 15 minutes. If I move for example from MySQL to SQLite 110MB it takes 6 seconds. I use other components but in 15 minutes writing with transaction batches in SQLite you can write some GB of data.

Best regards,
Cristian Peta

Cristian Peta is right. In my case, a copydataset took more then 5 minutes. With transactional control, works in 15 seconds! Thank you.

Fabio Lopes
Bob Carson

Posts: 62
Registered: 10/8/04
Re: Copy MS Access Table to SQLite
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 6, 2018 1:13 PM   in response to: Gernot Baecker in response to: Gernot Baecker
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02