Know any good scripts to convert msaccess to mysql

[laredotornado@zipmail.com]

Hello,

I am tasked with converting an MsAccess db to a MySQL 4 db in a Linux
environment. Can anyone recommend any good freeware/scripts to help me
do this?

Thanks, - Dave

[Tim Roberts]

Re: Know any good scripts to convert msaccess to mysql

laredotornado@z ipmail.com wrote:[color=blue]
>
>I am tasked with converting an MsAccess db to a MySQL 4 db in a Linux
>environment. Can anyone recommend any good freeware/scripts to help me
>do this?[/color]

Googling for "access export sql" resulted in a MySQL hit first thing.

The conversion is rarely perfect. Be prepared for some manual
intervention. Standard practices in Access don't always line up with
standard practices in MySQL. In particular, you will want to think very
hard about any special characters in your table and field names. It's
common to use spaces and dashes in Access field names, and it is possible
to use them in MySQL, but it's inconvenient.
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

[laredotornado@zipmail.com]

Re: Know any good scripts to convert msaccess to mysql

Thanks but nothing I've been able to find is hosted on a Unix/Linux
environment. What tool / script did you use? - Dave

[Tim Roberts]

Re: Know any good scripts to convert msaccess to mysql

laredotornado@z ipmail.com wrote:[color=blue]
>
>Thanks but nothing I've been able to find is hosted on a Unix/Linux
>environment. What tool / script did you use? - Dave[/color]

I doubt you will find anything hosted on Linux. It is not trivial to
manipulate an Access database outside of Windows.

I used Windows.
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

[Jim Carlock]

Re: Know any good scripts to convert msaccess to mysql

"Tim Roberts" <timr@probo.com > posted:[color=blue]
> I doubt you will find anything hosted on Linux. It is not trivial to
> manipulate an Access database outside of Windows.[/color]

What about putting the access file onto a Windows machine, then
exporting the data to XML, CSV or whatnot? Once placed in the
new format, the data can be moved back to the linux machine
and "accessed" without Access.

Or, could the file be placed upon a Unix machine, then connected to
via a network connection (network share) from a Windows machine?

Jim Carlock
Post replies to the newsgroup.

[Tim Roberts]

Re: Know any good scripts to convert msaccess to mysql

"Jim Carlock" <anonymous@127. 0.0.1> wrote:
[color=blue]
>"Tim Roberts" <timr@probo.com > posted:[color=green]
>> I doubt you will find anything hosted on Linux. It is not trivial to
>> manipulate an Access database outside of Windows.[/color]
>
>What about putting the access file onto a Windows machine, then
>exporting the data to XML, CSV or whatnot? Once placed in the
>new format, the data can be moved back to the linux machine
>and "accessed" without Access.[/color]

Absolutely, but as I recall, that is exactly the problem you were trying to
solve in the first place!
[color=blue]
>Or, could the file be placed upon a Unix machine, then connected to
>via a network connection (network share) from a Windows machine?[/color]

Do you mean something like Samba? That will work, but there are two
issues. First, Access is not very smart about the way it reads the
database file, and most Access programmers are even worse. Talking to an
Access database over a network share can be a painful exercise in patience.
Second, this doesn't really solve the problem. You're still running the
Access application on Windows, and the original problem specification was
to eliminate that.

<soapbox>
Access is a flexible, powerful, and easy to use program, but in my view,
one of the most serious shortcomings in all 11 versions of Access has been
the complete lack of a simple "export to SQL" command.
</soapbox>

When I take a backup of a database, I would much rather have a file that I
can read (and manipulate) than a copy of an opaque binary file. Having an
export/import option would eliminate the need for the "repair database"
option in Access.

By the way, here are a couple of web pages that describe how to move from
Access and SQL Server to MySQL:

http://dev.mysql.com/tech-resources/...microsoft.html

http://www.kitebird.com/articles/access-migrate.html

--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

[Jim Michaels]

Re: Know any good scripts to convert msaccess to mysql

I thought MySQL had the MySQL Migration Toolkit for free.

<laredotornado@ zipmail.com> wrote in message
news:1138682420 .787527.98840@g 44g2000cwa.goog legroups.com...[color=blue]
> Hello,
>
> I am tasked with converting an MsAccess db to a MySQL 4 db in a Linux
> environment. Can anyone recommend any good freeware/scripts to help me
> do this?
>
> Thanks, - Dave
>[/color]

[Jim Michaels]

Re: Know any good scripts to convert msaccess to mysql

The Migration toolkit is quick to convert and really slick at data mapping
and the like. It will tell you in detail about errors and let you fix the
SQL, but I never had any. it was a straight shoot and it took maybe 5-10
minutes of clicking mostly "next" and checking for errors. The previous
version recreated a lot of the MS hidden System tables. the new one doesn't.
It also converts other databases like Oracle. DB2 is in the works. So it's
not a toy tool.
It generates one SQL script for your data and one for your schema if I
remember right.

http://dev.mysql.com/downloads/migra...olkit/1.0.html

and yes, it runs on Linux. And like I said - it's free.

"Jim Michaels" <jmichae3@nospa m.yahoo.com> wrote in message
news:qPKdnU9iCO Q_-nHenZ2dnUVZ_vyd nZ2d@comcast.co m...[color=blue]
>I thought MySQL had the MySQL Migration Toolkit for free.
>
> <laredotornado@ zipmail.com> wrote in message
> news:1138682420 .787527.98840@g 44g2000cwa.goog legroups.com...[color=green]
>> Hello,
>>
>> I am tasked with converting an MsAccess db to a MySQL 4 db in a Linux
>> environment. Can anyone recommend any good freeware/scripts to help me
>> do this?
>>
>> Thanks, - Dave
>>[/color]
>
>[/color]

[Jim Carlock]

Re: Know any good scripts to convert msaccess to mysql


"Jim Carlock" <anonymous@127. 0.0.1> wrote:[color=blue]
>Or, could the file be placed upon a Unix machine, then connected to
>via a network connection (network share) from a Windows machine?[/color]

"Tim Roberts" <timr@probo.com > replied:[color=blue]
> Do you mean something like Samba? That will work, but there are
> two issues. First, Access is not very smart about the way it reads
> the database file, and most Access programmers are even worse.[/color]

Well, I'm thinking along the lines of Access connecting to Access,
and Access connecting to other. I'm lacking in experience in
connecting from other to Access. There are two ways I can think
of whereby Access can connect to other...

(1) The database being connected to is recognized,
(2) The database is unrecognized, in which case some sort of
ODBC or SQL serving connection must be used. So a third
party connector existed and Access hooked up to it okay. I
was thinking about going in the reverse direction though,
whereby perhaps some third party tool already exists that
can connect to an Access data file and thereby send Access
SQL statements to it.
[color=blue]
> Talking to an Access database over a network share can be a
> painful exercise in patience.[/color]

Well, perhaps, but only if you insist. :-) The ADO methods seem
alot slower than the DAO methods. I won't say that I'm familiar
with accessing Access in anything but a Microsoft environment
though.
[color=blue]
> <soapbox>
> Access is a flexible, powerful, and easy to use program, but
> in my view, one of the most serious shortcomings in all 11
> versions of Access has been the complete lack of a simple
> "export to SQL" command.
> </soapbox>[/color]

Access provides a visual way to create queries. Right clicking inside
the query created then offers a drop down menu whereby you can
select SQL View to get the "Access SQL" version of the query. I've
used this in the past to pop an SQL query into a external application,
use links to external data on "other servers" to create tables inside
Access. Of course every dialect of SQL differs somewhat.

Furthermore, SQL is just a scripting language for collecting a group
of data. The presentation of the data gets handled outside of SQL.
SQL doesn't provide the file format, it's just a scripting language,
and when dealing with databases, I tend to think along the lines of
recordsets and queries and SQL provides the syntax for acquiring
the recordset (or creating or editing a recordset).
[color=blue]
> When I take a backup of a database, I would much rather have a
> file that I can read (and manipulate) than a copy of an opaque binary
> file.[/color]

Okay, I think I see what you're saying, that going along the lines
of ISAM files. An Access "link" provides a way to connect to external
data file types. Once "linked", the normal gambit of "SQL" operations
could commence. Microsoft provides some limited ways to "link" to
some well know data types (ie, text files/csv, txt, asc, tab, Access
mdb/mde/mda, dbase III/dbf, dbase V/dbf, dbase IV/dbf, paradox/db,
html/htm, and a couple Microsoft types/Outlook+Exchang e).

A third party ODBC connection might be of use.
[color=blue]
> By the way, here are a couple of web pages that describe how
> to move from Access and SQL Server to MySQL:
>
> http://dev.mysql.com/tech-resources/...microsoft.html
> http://www.kitebird.com/articles/access-migrate.html[/color]

Thanks for the links, Tim. I think my original thoughts fell along
the lines of linking to an Access .mdb and then extracting the
data from such. I lack experience in moving data out of Access
to another format though. I kind of hoped that there was an
easy way to connect to an Access .mdb and still am not sure
that an easy way exists from a non-Microsoft client. IIS and
other scripting languages provides ways to easily extract data
from an Access .mdb and present it in an XML format...

Just throwing some thoughts out.

Jim Carlock
Post replies to the group.

[Jim Michaels]

Re: Know any good scripts to convert msaccess to mysql


I've had perfect conversions with the MySQL Migration Toolkit (not tried it
with odd features of Access), available from the MySQL web site.
http://www.mysql.com/products/tools/migration-toolkit/ info
http://dev.mysql.com/downloads/migra...olkit/1.0.html download

if you get any errors along the conversion, you can view the error detail &
edit the SQL and fix things. 10 minute process. not a toy tool. also
migrates Oracle and some others.
Can work directly from the database file - no need for odbc, unless you want
to. Generates a schema script and a data script.
had a database that used system tables. the toolkit decided to include the
needed system tables.

"Tim Roberts" <timr@probo.com > wrote in message
news:cmp0u193kv nsfnqf35nm1meom sg7dvl3v9@4ax.c om...[color=blue]
> laredotornado@z ipmail.com wrote:[color=green]
>>
>>I am tasked with converting an MsAccess db to a MySQL 4 db in a Linux
>>environment . Can anyone recommend any good freeware/scripts to help me
>>do this?[/color]
>
> Googling for "access export sql" resulted in a MySQL hit first thing.
>
> The conversion is rarely perfect. Be prepared for some manual
> intervention. Standard practices in Access don't always line up with
> standard practices in MySQL. In particular, you will want to think very
> hard about any special characters in your table and field names. It's
> common to use spaces and dashes in Access field names, and it is possible
> to use them in MySQL, but it's inconvenient.
> --
> - Tim Roberts, timr@probo.com
> Providenza & Boekelheide, Inc.[/color]