up vote 2 down vote favorite
share [g+] share [fb]

My situation:

Earlier, I asked:

What is the easiest way to edit an Access database online?

[...] What is the easiest way to allow people to make changes to that database without needing to resort to the methods above?

There, one answer suggested to "consider a switch from using Access to using MS SQL. Access (2007 at least) has Wizards to help you convert pretty easily.".

Therefore I am thinking about migrating our existing Access databases (.mdb) files to a server with MySQL, so that users can access the database over Access/ODBC again.

What tools I currently have:

I have the following software:

  • Windows 7 with Microsoft Access 2007 clients
  • A Debian server with mysql, Version 14.12

While searching for possible solutions I've found a lot of guides, but most of them for Access versions 2000 or below (like this one). They also suggest some shareware, or other free-to-trial tools that I don't even want to install. These include:

However, I am not that experienced with Windows or Microsoft's products at all to know what still works and where I should take extra special care.

What I need:

Ideally, I would like a easy solution to migrate the Access database to SQL data, which I could then access again using ODBC. I am more interested in the Access ⇒ MySQL part and how to use the SQL data in Access again. I know how to administer MySQL databases, that's not the issue.

  • If there's a wizard, is it easy to use? How does it work?
  • If there's a software tool, which one does the job best (for free)?
  • If I can do it manually, where should I take care?

On a side note: Is that even possible with MySQL or do I need Microsoft's SQL Server? (This is suggested in: How to: Run the SQL Server Import and Export Wizard)

link|improve this question
85% accept rate
Hey @slhck, do you just want the tables (structure and data)? – slotishtype Jul 21 '11 at 12:35
Anything that makes the Database work the same as before :P – slhck Jul 21 '11 at 12:44
Based on this and the other question, I'd look at moving the database to Sql Server Express edition. It should offer all the performance you need, is more configurable in a Windows environment, good management tools, good integration with Access as a front end, etc. – Joe Internet Jul 21 '11 at 13:32
The answer you received about the wizard was for Microsoft SQL Server. You seem to have ignored that fact and moved on to MySQL, for which MS provides no migration tools at all (for MS SQL Server, the SQL Server Migration Assistant for Access is the superior tool, much better than the wizards within Access). – David W. Fenton Jul 22 '11 at 21:30
@David I honestly don't know anything about databases, therefore I didn'T know there would be so much of a difference. – slhck Jul 23 '11 at 7:48
feedback

2 Answers

up vote 2 down vote

When I had to pull a lot (20 veterinary clinics-worth) of data from Access 97 (eek!) to MySQL, I used the free Linux mdbtools package to generate CSV files directly from Access.

In my case, the App using Access for its data was still in use and so I was doing daily and weekly dumps and imports. Using mdbtools allowed me to setup some crafty, scheduled scripts to help do this. Not sure if this approach would suit your requirements and you may have to export your data in an older Access mdb format first.:

http://mdbtools.sourceforge.net/

link|improve this answer
I'll give it a shot! So, do you happen to know how I'd then use the SQL data in the Access database again? – slhck Jul 21 '11 at 12:52
I never worked that way round so can't really comment. – Linker3000 Jul 21 '11 at 13:04
feedback
up vote 1 down vote

I'm surprised MySQL would have no migration tools for Access/Jet/ACE data. Have you checked?

In any case, one way to export data to a MySQL database is to set up a DSN for the MySQL database, and then in the database with your Access tables, choose EXPORT from the File menu, and choose your DSN. This will create the tables on MySQL and populate them with the data. The caveat is that the data types may not be exactly what you want, so you may have to do some adjustments after the fact, and even re-export the data (if any of it got exported inaccurately).

link|improve this answer
So basically you mean something like that? – slhck Jul 23 '11 at 8:21
Not "something like that" but THAT -- you set up your DSN with the Windows ODBC administrator and then you can use it in Access to export your data to that DSN. – David W. Fenton Jul 24 '11 at 20:29
Fair enough, I'll try and see how far I'll get. – slhck Jul 24 '11 at 20:34
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.