MySQL to Access

[grip003]

I am having difficulty writing a very simple program to convert a MySQL database into an Access database (who would ever want to do this, but unfortunately I have to). Everything is working, except that the database is so big that I am having memory problems. It seems like the problem comes from using a string to build insert statements for the access database. So, I tried using a StringBuilder instead, and that seemed to use even more memory. Can anyone help me?

[Arch4ngel]

I don't know how you are handling the data, but I had an memory issue once with database rows.

 

There was a binary object in the row. But the way I would proceed is load all rows in memory and then write them to destination. Even though this seem simple, it work with a few row but when you reach a few thousand rows... you just use too much memory with those binary object.

 

So... to come back to the main subject... you shall avoid what I did. Read data from Original Row then Write this data to Destination Row. Repeat previous steps until you reach the end of the table. And you keep going like that.

 

For your case... I don't know how you are proceeding but I would definitly use the System.Data namespace with the appropriate object for the connection and command.

[Nerseus]

You're not going to show us what code you've got so far?

Are you trying to build one monster big string of all inserts?

 

Why not go for simplicity, if this is a one time conversion where performance won't matter? In that case, have some loops that do the work:

for each table in MySql
   Create Table in Access
   for each row in MySql
       insert row in Access

 

You may want/need to add foreign keys, primary keys, etc. as well. That all depends on your requirements of how close the Access database must match the MySql database.

 

-ner

[grip003]

I have tried almost everything I can think of, loading all the tables at once and then running through them all doing the inserts, loading each table 1 at a time and then creating the insert strings, I even use limits in my table select to limit my table sizes to 10,000 records and do the inserts in batches. I have even tried to create 2 programs, one to create the insert strings and the empty shell access database with the necessary tables, and the other that simply reads the file and executes the insert statements. Unfortunately, the first program still has memory problems. I have narrowed it down to the insert string. If I leave all my code, but don't use the insert string (which means the program doesn't work), then I use less than 350MB. With the inserts, I get to about 1GB and then the memory problems come. I tried changing from using a string to using a StringBuilder, but that seemed to use even more memory. There must be an easier way.

[HJB417]

You should look into mysqldump... try using the --compatible flag.