Re: importing Access databases
Robert Morgan wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. There is 'mdbtools' on sourceforge, which allows you to view data in an MDB file and export it. I would not go about it this way though. I would create the tables in MySQL - paying close attention to field types, and then link the MySQL tables to Access via the MyODBC driver, and then use queries to dump the data from the Access tables into the MySQL tables. Alternatively, you could export the data to a CSV file and use 'load data infile' on the MySQL server to import the data. But linking the tables in Access is easier... Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: importing Access databases
there is an app called sqlyog it imports access directly into mysql -Original Message- From: SpamBox [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:13 AM To: Robert Morgan; mysqllist Subject: Re: importing Access databases Robert Morgan wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. There is 'mdbtools' on sourceforge, which allows you to view data in an MDB file and export it. I would not go about it this way though. I would create the tables in MySQL - paying close attention to field types, and then link the MySQL tables to Access via the MyODBC driver, and then use queries to dump the data from the Access tables into the MySQL tables. Alternatively, you could export the data to a CSV file and use 'load data infile' on the MySQL server to import the data. But linking the tables in Access is easier... Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
Robert Morgan wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. Depends how big the whole thing is as well as how good the current design is. Since it is running in a hospital I hope the Access approach was propperly done as if Access were a real RDBMS. ;) (My own project is a mess that grew (in the sense of cancer) out of a single Excel table and now sucks to maintain any further.) If possible I'd go with spambox's approach and write those table designs myself. The data-migration isn't that hard afterwards. The big issue is the table and index design. Generally you could use one of those Windows thingies that you can download at mysql.com but I don't know how they cope with foreign-keys and that you'll HAVE TO HAVE a TIMESTAMP collumn in every mysql-table with content that will be edited later, even when there is none in the access-table. I'm pretty sure a click-and-fly migration from Access to MySQL where you just start some magical tool and everything is done, is wishful dreaming. SpamBox wrote: I would not go about it this way though. I would create the tables in MySQL - paying close attention to field types, RIGHT And perhaps there are some NOT NULLs or FOREIGN KEYs and INNDEX/UNIQUEs to be seeded in the new design. Automatic migration would move shortcomings of the old design to mysql without bringing you to notice them. and then link the MySQL tables to Access via the MyODBC driver, and then use queries to dump the data from the Access tables into the MySQL tables. That gives a head ache if AUTO_INCREMENTS were used as foreign-keys. Which of course they shouldn't have done in the first place. Which of course *I* shouldn't have done, too ! ... live and learn. Alternatively, you could export the data to a CSV file and use 'load data infile' on the MySQL server to import the data. Though ... there might lurk a number of fields in those CSVs that break consistency in the new design. At least my Access-DB has a lot of collumns that should have been set to NOT NULL but I didn't bother to switch it on in Access table-design. And then there wer dangling foreign-keys that lost their parent or became NULL sometime while Access looked away or crashed or where I skrewed up. Who uses transactions in Access anyway ? =8-O The new handwritten CREATE TABLES have a lot more consistency checks in them and so CSV import runs on a landmine now and then. Take your new design scrips and check the consistency while the data sits still in Access. But linking the tables in Access is easier And a lot slower. OK, that doesn't bother in a one time data migration move but while I was at it skribbling away on those table scripts I had to keep up with the live data so regular reimports where neccessary. One can create dynamically block-INSERT commands. Those are way faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
I saw a macro for access that will produce a script that will recreate your tables and the data in them. But it only worked in older versions of access. Sorry I can't be more help. bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
--- Robert Morgan [EMAIL PROTECTED] wrote: Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing Access databases
Why does the client doing the importing need to run on Linux? Don't you have access to a Windows machine anywhere on the network? It shouldn't matter where the target MySQL server is or what OS it's running on. - Original Message - From: Robert Morgan [EMAIL PROTECTED] To: mysqllist [EMAIL PROTECTED] Sent: Sunday, July 27, 2003 1:14 PM Subject: importing Access databases Hi, I'm doing a project for a local hospital, they want to migrate their access databases to mysql on a Linux box. The hospital runs a Windows network and I have MYsql running on a linux box connected to the network. I need to import the Access .mdb data and structure from the windows server to MYsql, all this has to be done from the Linux box. I have heard of some programs that can do this but they seem to be for mysql on windows or for windows clients (Mysqlyog, dbtools ) I am a newbie when it comes to linux-MYsql. Any help appreciated. Running Redhat 9 and the MYsql version that comes bundled with it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
