Hello to all, sorry if my english is not so good, I will try to explain the problem.
I need to copy all data from a Table of a Acces mdb (connected with OleDB) to a Table of a MySql DB (connected with ODBC)
I made a working solution, but it's very slow, and so I want to try other solutions to check if they can give me more performance.
The solution connecting to the mdb, using a DataReader, then for each row in Datareader I make an INSERT into the Mysql Table ( before copy I truncate the table to get it empty)
The records are more than 10K and this operation is very slow, and do be onest I need to do the same thing on other 2 tables also very big as this one.
I cannot make a direct sql insert ( as "INSERT INTO A in ..... SELECT * FROM B) becouse the 1 DB has a OleDB conn and the other has a ODBC conn
So I thought to try to make this operation using TableAdapters and DataSet, but I'm not able to make it working.
The problem is that the Dataset for VB HAS.CHANGES is false
If you need some code I can post but what I do is this.
- Connection to MDb
- Create OleDbTableAdapter
- Create DataSet
- Fill DataSet with TableAdapter
- Connection to MySqlDB
- Create ODBCTableAdapter
- Using Update command of ODBCTableAdapter with the first Dataset.
But DS has no changes commited so he don't write anything to DB, so I thought to use another Dataset and copy data from DS1 to DS2 to add rows, see if has.changes was true and the making Update command of ODBCTableadapter using DS2.
I tryed to copy data between datasets doing:
ds2 = ds1.copy
o I tryed also to use dataset import function, looping DS1 datarows and Importing all rows from DS1 to DS2.
In both cases the rows are added to DS2, but still HAS.CHANGES is false, what can I do?
Just to clarify possible questions I didn't use DS.Acceptchanges, PrimaryKey is defined , TA Update command is defined, DS has data (I populate 2 DataGrid to check it).
No errors given, just no data written on DB.
Any suggestion? Thanks in advice