[Techtalk] MS Access --> mysql
rasjidw at openminddev.net
Sat May 3 01:11:22 EST 2003
On Friday 02 May 2003 22:41, Phil Savoie wrote:
> 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
Canberra, Australia UTC + 10
More information about the Techtalk