[Techtalk] MS Access --> mysql

[Charlotte Oliver]

On Fri, May 02, 2003 at 08:29:03AM -0500, Glenda R. Snodgrass wrote:
> > 1.	Is it as simple as exporting in some sort of delimitied file and importing 
> > into mysql?
> Moving the data is that simple, yes, after cleanup.

*nod*

When I did it, I exported into a CVS file, opened it in Excel and
painstakingly went through for abnormalities.  

I like Jacinta's idea of scripting it - that makes a lot more sense.

> > 2. 	Has anyone ever tried to do this?
> I recently converted a fairly complex database system with a dozen tables
> from Access to mySQL with few problems.  I wrote the mySQL front end in
> PHP, which gave us many more options than the original Access front end, 
> but it was also quite a bit of work.

Agreed with this - I've done a similiar thing and it took me many weeks
of work, though a large part of that is because of the idiotic way I had
to set up the database to make it autonumber.  (It was an inventory
system and we have something where numbers in the 1000s are desktops,
2000s are monitors, 3000s are printers, etc., etc., so I had to have
separate tables for each one, which got very tedious).

In hindsight, I should have written my scripts to do the autonumbering
for me, rather than the database.

Anyone have other suggestions on that - it's the first database I set
up, so I was guessing blind on a lot of it.

> > 3.	Were there any gotchas to look out for?
> The biggest problems were cleaning up the data coming out of Access, and
> how much trouble you have will depend on how well the original Access
> database was structured.  The one I worked with had few rules for data
> integrity, so there were lots of unmatched records and tons of duplicates.  
> The date and time fields out of Access had to be reformatted with a perl
> script to import into mySQL (dates came out of Access in the format
> 05/02/2003 IIRC whereas mySQL stores them as 2003-05-02).

*nod*  I also had that problem, although I fixed it initially in Excel
(via cell format and a custom field) and forced new data to comply with PHP.

> > 4.	Is msql relatively close to ms access in behaviour?

Not really.  However, I see that as a Good Thing<tm>.  :)

Charlotte