Can anyone reccommend a program that will transfer a BE database (Tables &
Relationships only) to MySQL For that matter does MySQL handle relationships.
Also need Autonumbers and indexes (some using several fields as an index)
transferred. I suppose I am asking if I can have a MySQL database identical
to an Access 2010 BE database, if so, what software do I need to "painlessly"
migrate it accross? Thanks
Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:j0r7hp$4ug$1 (AT) speranza (DOT) aioe.org:

Create a DSN to your MySQL database, and you can then export your
data tables to it. It won't do RI (in fact, in order to have any RI
at all, you have to choose the non-native InnoDB table format), but
it will at least get all your tables more or less right. You'll
likely have to make some adjustments to data types, though.

I know of no other upsizing tools, but you might search the MySQL
sites to see if there are tools for this. Certainly Oracle provides
an Access upsizing utility (though I can't say if it's any good).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Thu, 28 Jul 2011 09:44:42 +0100, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

Install SQL Server Express and use the SSMA tool
http://www.microsoft.com/sqlserver/e...ol.aspx#Access
to migrate your Access tables, relationships and data. Then use the
scripting tools to create the DDL necessary to create the tables and
relationships. Now run that DDL into MySQL.

I'm a bit vague on the scripting tools necessary as I suspect the name
and location of them has changed since the last version of SQL Server
I've used.

I see David has some comments on MySQL and RI.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Hi Phil and Dave,

The current primary work tool for MySQL is called Workbench and is
available from links on the MySQL website. I am not sure what the
current version is as I have not had the need to upgrade for some
time. In the version I have there is no upsizing tool included, but I
do remember that in the older administrator tools there was one.

That being said it would not be too hard to write a block of code that
outputs the SQL required to build a databasees table and so on, and
have it in MySQL's dialect. I have never needed to do this as the
Workbench tool makes creating the design so simple that it was
something that I simply never bothered to do. I suppose if oyu had a
few hundred tables and views then that would be a different story, but
my databases tend to have less than 30 tables for the last few years.

As David said there may also be a tool available on the net or through
the community website dedicated to Workbench (it supports add-ons).

If someone already has a block of code that dumps an Access databases
structure to SQL I could probably tweak it to MySQL dialect without
too much work.

The migration of data to a different back end is a different problem,
but happily one with a simple solution: Pentaho Data Integration. It
is free and a great tool to use for moving and transforming data in
almost any context. I remember ripping a nearly two gig MDB file into
MySQL, and the only thing I hade to do was to make sure that the data
types were appropriate in MySQL for what I wanted it to hold (its
always a good idea to check this anyway as different databases can
handle things differently, such as string lengths, binary fields,
etc...). I seem to recall that it was also a pretty fast operation, I
think less than ten minutes if I remember correctly.

Cheers

The Frog