Access, MySQL and data transport among other things.

I have an M$ Access 2K DB that contains all my data but the data is now going to be used on a linux only server.

The new database format looks like this:

CREATE TABLE _links (
lid int(11) unsigned NOT NULL auto_increment,
cid int(5) unsigned NOT NULL default '0',
title varchar(100) NOT NULL default '',
url varchar(250) NOT NULL default '',
logourl varchar(60) NOT NULL default '',
submitter int(11) unsigned NOT NULL default '0',
status tinyint(2) NOT NULL default '0',
date int(10) NOT NULL default '0',
hits int(11) unsigned NOT NULL default '0',
rating double(6,4) NOT NULL default '0.0000',
votes int(11) unsigned NOT NULL default '0',
comments int(11) unsigned NOT NULL default '0',
PRIMARY KEY (lid),
KEY cid (cid),
KEY status (status),
KEY title (title(40))
) TYPE=MyISAM AUTO_INCREMENT=13 ;


CREATE TABLE `_text` (
`lid` int(11) unsigned NOT NULL default '0',
`description` text NOT NULL,
KEY `lid` (`lid`)
) TYPE=MyISAM;

I will be using a query to format my data to be exported and added to the MySQL DB.

I know little about MySQL and a reasonable ammount about Access.

The MySQL is a live DB.

My data naturaly forms a single table and so I will have to break it into two.

How do I set my data to export and then import it without loss of referential integrity.

To further complicate matters some fields will be left null or blank (not given) for the purpose of formatting.

Hi Matt,

I'm not sure what you are asking. Are you normalising the data 'on-the-fly' as you export it? Or are you worried about the auto-increment field, and keeping the child table in synch? If it is the latter, then is it possible to add an extra (temporary) column to you new _links table that could contain the old key value, so you could map the child record to its new parent key? Then once the export/import is complete, you could drop the redundant column.

Does that make any sense - it feels like I'm rambling... :)

Quote:

---

Originally Posted by PilgrimPete

Hi Matt,

I'm not sure what you are asking. ...are you worried about the auto-increment field, and keeping the child table in synch? ...then is it possible to add an extra (temporary) column to you new _links table that could contain the old key value, so you could map the child record to its new parent key? Then once the export/import is complete, you could drop the redundant column.

---

That might help yes.

I'm also asking about transporting the data. I've decided the best bet is to make this a one shot affair and export the data from the M$-Access and then put it in the MySQL.

I need to keep the child parent relationship as you so rightly point out but I also have little idea what to export my data into and then what I would need to read it out again and have it "go into" the MySQL DB.

I have onl PHP+SQL or PHPMyAdmin as methods of import but export can be done via M$-Access own GUI.

I don't know much about PHP or MySQL or their respective Import capabilities, but doesn't MySQL have a bulk import capability - from text file.
You could generate a series of INSERT statements from your Access database, and run the batch into the database.