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]
