MySQL DB to MS ACCESS DB

[grandma]

I have a MySQL DB that I would like to convert to an MS Access DB.

Reason being is more efficient in creating reports in MS Access.

Does anyone know of a script/program that can do this?

TIA

Grandma

[^hyd^]

here's how I was able to do it...

if you have myodbc installed, you can create a dsn to the database in mysql that you want

then create a blank access db, and go to File->Get External Data->Import

a new window will open, in the "Files of Type" dropdown, choose the bottom one, "ODBC Databases()" and the "Select Data Source" window opens.

Go to the "Machine Data Source" tab, and select your dsn as created previously.

If you connect fine, you should be able to choose the tables to import... make your choice and click ok!

You'll have to check your tables over in access tho, some properties might not be what you want, since the import most likely isn't perfect...

cheers,
good luck!

[qball]

^hyd^,

Good answer, that would be easiest way. Though this just converts structure and data, if access supports the same structure that mySQL (dependent upon version) supports.

grandma,

send money and those tasty cookies...

Depending upon use, yes Access, easier to make reports and views, blah, blah for simple stuff.

But, access not real DB mySQL is, and you will lose some big benefits:

mySQL free.
access, NOT.

mySQL fast, very fast.
access, er, ah, slow...

mySQL if not fully SQL compliant, very close in what it does.
access has it's own concept of SQL, like TSQL or something...

This last fact, may or may not affect this conversion, as if, if the mySQL db has a table comumn with certain attributes in mySQL, they will be converted (or something) to whatever access supports, this may cause issues that need to be rectified manually, after import. Could also affect data integrity, not a good thing.

If reporting is an issue, what reports do you run now on mySQL DB? and how do you think access will help?

[ctaylor]

Listen to QBALL - excellent observations and comments.

The only thing I would add is that if generating reports is the only issue, you can run MS Access based off of LINKED tables stored in virtually any ODBC compliant database.

By installing MyODBC ODBC drivers on your client workstation, you will be able to access a DSN ('data source name' that you manually create through the 'ODBC DataSources icon' in your Windows control panel) to create linked table access in basically the same manner that ^hyd^ suggested for importing data tables. You would have to do this even if you were utilizing Crystal Reports or some other report generating software that would allow you to generate even more advanced reports than are available through MS Access.

I would also suggest that MySQL is more robust and reliable than MS Access. If database performance is an issue, my understanding is that MySQL is one of the all time fastest database engines out there for simple select queries. When you get to performing more complex querying other backend databases might be of interest. As far as reliability, MySQL's web page used to list NASA and Texas Instruments as some of the organizations who have determined MySQL to suit their industrial needs best despite competing database products from commercial vendors.

You can also create PASS-THROUGH queries to send the query processing away from the client computer and through to your MySQL server to increase the slow performance speeds available through MS Access.


(EDIT: Unelss things have changed, TechIMO runs off of a MySQL backend too.)

[grandma]

Thanks people!

Yep, I have a simple database of listings, and they want a downloadable format. MS Access is my only experience with reports.

I'll report back soon.

Cookies on the way.

Grandma

[grandma]

Thanks guys.

Just installed the MySQL Connector/ODBC on my computer and was able to import the tables and create some reports.

Grandma

[xmun]

if i understand well, u stayed on mysql...
what report generator did u use?

could u help me with some free report generators?

thanx a lot

Quote:

Originally posted by grandma
Thanks guys.

Just installed the MySQL Connector/ODBC on my computer and was able to import the tables and create some reports.

Grandma