[Techtalk] MS Access --> mysql

[Rasjid Wilcox]

On Friday 02 May 2003 22:41, Phil Savoie wrote:
> All,
>
> My boss had asked if there was a way to convert an ms access db to mysql. 
> Not having dealt with databases before I couldn't give a qualified answer. 
> So I am calling on all those db experts out there to ask a few questions.
>
> 1.	Is it as simple as exporting in some sort of delimitied file and
> importing into mysql?

Another way to do the conversion is to use the ODBC drivers for MySQL and link 
the new MySQL database into MS Access.  The MySQL tables then appear as 
tables in MS Access, and you can just select, copy and paste the data from 
one the original table(s) into the new table(s).  The avoids both the 
delimiter problem and the date format problem, as these are effectively dealt 
with by the ODBC driver.

> 2. 	Has anyone ever tried to do this?

Do a google search on "convert ms access mysql".

> 3.	Were there any gotchas to look out for?

I think these have been dealt with fairly well in previous posts.  Some of 
these are avoided by using the ODBC import method, but you will still have to 
create the tables in MySQL before importing, and there is not a 1-to-1 
correspondance between MySQL data types and MS Access data types.

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

As others have indicated, the short answer is 'No' and 'Yes and No'.

More fully, there are two ways in which one can use MS Access.

1.  As a full database IDE.

I think this is the more common way of using MS Access.  Here the entire front 
end (forms and reports) are done in Access, as well as the 'middleware' code 
(queries and any VBA code) and the actual database 'backend' (tables).

If this is the way in which you are using MS Access, then the answer is 
definitely no.  MySQL does not provide any way of creating forms, reports or 
code.  You would need to write a new frontend in some new system, such as 
Visual Basic, Python, Perl, Java, C, C++ etc, and you may need a report 
writing system such as Crystal Reports etc.  MySQL is *just* a database and 
nothing else.  MS Access tries to be all things, and thus is not very good at 
any of them.

2.  As a 'backend' database only.

You can (and in fact there is one application at my workplace written this 
way) use the mdb database format purely as a backend (stores the tables 
only), and write a frontend in Visual Basic etc.

In this case it may be relatively easy to move from MS Access to MySQL.  
However, there are almost certainly a number of differences between the SQL 
supported by MySQL and MS Access, and these would need to be addressed in the 
porting process.

Cheers,

Rasjid.

-- 

Rasjid Wilcox
Canberra, Australia  UTC + 10
http://www.openminddev.net