Open source tool for converting Microsoft Access (MDB) to MySQL

Hello all,

I've a large MDB file created by a bespoke application. I'd like to run
a cron job to pull the data from this and put it in a MySQL database
through the night.

Are there any open source tools that'll achieve this or get me close?

Michael Vilain wrote:
>>
>> I've a large MDB file created by a bespoke application. I'd like to run
>> a cron job to pull the data from this and put it in a MySQL database
>> through the night.
>>
>> Are there any open source tools that'll achieve this or get me close?
>
> Off the top of my head, you could install perl and ODBC drivers for
> Access. Will Access run on anything other than a Windows box? If so,
> how were you going to "run a cron job" on a Windows box to pull the data
> out of Access?

It was planned to FTP the file to the Linux server at 1am. I wanted cron
to run shortly after to get the data out of that.

> I'm sure there are open source job scheduling tools, maybe even a crond
> that runs on XP.

There's scheduled tasks (AT) for that :-)

> Just curious as you seem to be mixing environments.
> In any case, there is a perl that runs on XP. You may also need a
> compiler and development environment to build the database components,
> however.

The best solution for us would be getting the MDB file onto the Linux
box then having Linux put the data into MySQL tables. The servers aren't
on the same LAN so opening the ODBC can of worms is something I'd want
to avoid if at all possible.

Jerry Stuckle wrote:
>>
>> The best solution for us would be getting the MDB file onto the Linux
>> box then having Linux put the data into MySQL tables. The servers
>> aren't on the same LAN so opening the ODBC can of worms is something
>> I'd want to avoid if at all possible.
>
> The question remains - can you run your access database on Linux? AFAIK
> you can't. You can get to it with ODBC from a Linux box, but the
> database itself must run on a Windows box.

Maybe my original post is misleading. I said I wanted to 'pull' the data
from the Microsoft database, which suggests the MDB database being
online and 'live'. I don't want to 'run' the access database, I just
want to get the data out of it.

My question is simply this.

I have an MDB file on a Linux box. Do open-source tools exist to read
the data from this and put it into a MySQL database?

Simon Dobson wrote:
> Michael Vilain wrote:
>>>
>>> I've a large MDB file created by a bespoke application. I'd like to
>>> run a cron job to pull the data from this and put it in a MySQL
>>> database through the night.
>>>
>>> Are there any open source tools that'll achieve this or get me close?
>>
>> Off the top of my head, you could install perl and ODBC drivers for
>> Access. Will Access run on anything other than a Windows box? If so,
>> how were you going to "run a cron job" on a Windows box to pull the
>> data out of Access?
>
> It was planned to FTP the file to the Linux server at 1am. I wanted cron
> to run shortly after to get the data out of that.
>
>> I'm sure there are open source job scheduling tools, maybe even a
>> crond that runs on XP.
>
> There's scheduled tasks (AT) for that :-)
>
>> Just curious as you seem to be mixing environments. In any case,
>> there is a perl that runs on XP. You may also need a compiler and
>> development environment to build the database components, however.
>
> The best solution for us would be getting the MDB file onto the Linux
> box then having Linux put the data into MySQL tables. The servers aren't
> on the same LAN so opening the ODBC can of worms is something I'd want
> to avoid if at all possible.

The question remains - can you run your access database on Linux? AFAIK
you can't. You can get to it with ODBC from a Linux box, but the
database itself must run on a Windows box.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Simon Dobson wrote:
> Jerry Stuckle wrote:
>>>
>>> The best solution for us would be getting the MDB file onto the Linux
>>> box then having Linux put the data into MySQL tables. The servers
>>> aren't on the same LAN so opening the ODBC can of worms is something
>>> I'd want to avoid if at all possible.
>>
>> The question remains - can you run your access database on Linux?
>> AFAIK you can't. You can get to it with ODBC from a Linux box, but
>> the database itself must run on a Windows box.
>
> Maybe my original post is misleading. I said I wanted to 'pull' the data
> from the Microsoft database, which suggests the MDB database being
> online and 'live'. I don't want to 'run' the access database, I just
> want to get the data out of it.
>
> My question is simply this.
>
> I have an MDB file on a Linux box. Do open-source tools exist to read
> the data from this and put it into a MySQL database?

That's the point. NO!

You need the MS JET engine to access a .mdb file. And that only runs on
Windows. I don't know of anything which allows you to get the data when
the .mdb file is on a Unix system.

Have them export the data and import it into MySQL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
>
> That's the point. NO!
>
> You need the MS JET engine to access a .mdb file. And that only runs on
> Windows. I don't know of anything which allows you to get the data when
> the .mdb file is on a Unix system.
>
> Have them export the data and import it into MySQL.

Okay, shame, I'll look into doing the conversion before it arrives at
the Linux box.

Simon,

Via ODBC, you can create "linked tables" in Access that point to tables in
your MySql database. With some exceptions, the linked tables act as though
the tables were native Access tables. You can move the data via SQL commands
within the Access application by setting up a query for each table:

insert MyLinkedTable ...
select ....
from MyAccessTable

If you have to account for duplicate records, etc. the MyLinkedTable table
could be a staging table in MySQL.

By updating directly from Access, you can eliminate the export and import
files. Its pretty straight forward.

-- Bill

"Simon Dobson" <replytogroup@nospam.domain.invalid> wrote in message
news:55vj2eF26fcu2U1@mid.individual.net...
> Jerry Stuckle wrote:
>>
>> That's the point. NO!
>>
>> You need the MS JET engine to access a .mdb file. And that only runs on
>> Windows. I don't know of anything which allows you to get the data when
>> the .mdb file is on a Unix system.
>>
>> Have them export the data and import it into MySQL.
>
> Okay, shame, I'll look into doing the conversion before it arrives at the
> Linux box.

Simon Dobson (replytogroup@nospam.domain.invalid) wrote:
: Hello all,

: I've a large MDB file created by a bespoke application. I'd like to run
: a cron job to pull the data from this and put it in a MySQL database
: through the night.

: Are there any open source tools that'll achieve this or get me close?


google speaks, it tells me to try

http://sourceforge.net/projects/mdbtools

never used it myself.

Malcolm Dew-Jones wrote:
> Simon Dobson (replytogroup@nospam.domain.invalid) wrote:
> : Hello all,
>
> : I've a large MDB file created by a bespoke application. I'd like to run
> : a cron job to pull the data from this and put it in a MySQL database
> : through the night.
>
> : Are there any open source tools that'll achieve this or get me close?
>
>
> google speaks, it tells me to try
>
> http://sourceforge.net/projects/mdbtools

Hi Malcolm,

Had a quick peek at that and it looks very interesting. Don't know about
a 0.6 release from 1994 but it could be just the thing.

Thanks for the pointer, I'll investigate that more tomorrow when it's
not 1am!

S.