Convert Exisitng Access Database to Access Frontend MySQL backend (1 Viewer)

DBnub11 · Mar 4, 2011

Hi all

I am stuck in a quagmire. I need to move the database off to a external server which has Mysql on it. The database is built up with a lot of forms and queries on it.

I have not seen an easy way to export my tables to mysql. The more worriesome issue is that will everything break if I were to try and link and run my existing queires forms and reports ?

access_developer · Mar 4, 2011

Hello

I had the conversion using SSMA tool and had no issues and its very easy to operate on

You can export your tables or choose which tables to be exported

Thanks

Galaxiom · Mar 5, 2011

access_developer said:
I had the conversion using SSMA tool and had no issues and its very easy to operate on

SSMA (SQL Server Migration Assistant) is a Microsoft product designed to migrate databases to MS SQL Server. It includes functionality to migrate MySQL to MSSQL.

However I very much doubt that Microsoft would offer a feature that allowed a user to convert to other databases and a quick search has not revealed any evidence of such a feature in SSMA.

How about this free tool?
http://www.bullzip.com/products/a2m/info.php

Otherwise have you considered using SQL Server Express instead. The 2008 version supports databases to 8GB. It is free and very easy to interface with Access.

Banana · Mar 5, 2011

I personally don't like using migration tools, at least not until one has done it by hand. The reason is that using such tool opens up an opportunity to hide and complicate the migration process if you are not aware of pitfalls. This remains true regardless of where the destination is and SQL Server does not offer that much extra compatibility with Access than any other ODBC-compliant RDBMS such as MySQL, PostgreSQL or Oracle and so forth.

I built a MySQL database from scratch (note that since then, they've done very nice job with Workbench, which is a GUI tool that allows you to design tables and build relationship. Once you've done that, you can then link the tables and import the data from Access tables to the linked MySQL tables. The process will take more time but hopefully in the end, you'll have learned more about how well MySQL works with Access, and what you need to consider in comparing data types.

FWIW, there's a guide for beginners to ODBC which also contains several links.

For information specific to MySQL <-> Access; here's some notes on the driver as well instructions for setting up Access as front-end to MySQL.

If it's any help, you may want to have a look at sample in this thread.

Best of luck!

ozinm · Mar 10, 2011

Folloiwng on from Bannana's post, I had a few gotchas when linking MSAccess to MySQL via ODBC.
I'd recommend turning the following on in your ODBC connection:

return matched rows instead of affected rows
allow big result sets
treat BIGINT columns as INT columns

Also create your boolean columns as as tinyint's with length of 1 instead of bit. Also make sure they're required (i.e. no null values).

As well as Workbench, I love HeidiSQL (http://www.heidisql.com/). It's a brilliant OpenSource editor for MySQL.

You'll definitely want to read up on creating pass-through queries in MSAccess too.

gemma-the-husky · Apr 1, 2011

Banana said:
I personally don't like using migration tools, at least not until one has done it by hand. The reason is that using such tool opens up an opportunity to hide and complicate the migration process if you are not aware of pitfalls. This remains true regardless of where the destination is and SQL Server does not offer that much extra compatibility with Access than any other ODBC-compliant RDBMS such as MySQL, PostgreSQL or Oracle and so forth.

I built a MySQL database from scratch (note that since then, they've done very nice job with Workbench, which is a GUI tool that allows you to design tables and build relationship. Once you've done that, you can then link the tables and import the data from Access tables to the linked MySQL tables. The process will take more time but hopefully in the end, you'll have learned more about how well MySQL works with Access, and what you need to consider in comparing data types.

FWIW, there's a guide for beginners to ODBC which also contains several links.

For information specific to MySQL <-> Access; here's some notes on the driver as well instructions for setting up Access as front-end to MySQL.

If it's any help, you may want to have a look at sample in this thread.

Best of luck!

Regarding the UA link - I take it that this explains that merely upsizing to SQL, of some flavour does not of itself guarantee a performance improvement. You may still need to look carefully at query definitions etc, especially in key bottleneck areas. Is that correct?

Banana · Apr 1, 2011

Yes, that's correct.

Some people think that if they change backend to SQL Server, Oracle or whatever, it'll magically increase speed. That seldom happens and if they've developed it without considering the server-client architecture, there may be some re-engineering required to optimize the applications.

Two simple examples of this:

1) If you have a form bound to a table name and not a query, it will have to pull all key to support navigation. Typically this is not a big deal with small enough table and because it's only the keys and not the records, it's not a issue. But when we talk about million rows or so, pulling all those keys can be expensive, so it may be necessary to filter to a smaller set first.

2) I suspect this is the most common mistake - using VBA functions in where functions. With Access, some has used VBA functions to build custom filtering function but if we threw it at the server, Access is forced to pull down all data and do the filtering locally. You don't want that. Even worse, this can happen when we do something simple like "IsNull(aColumn) = True" because that is not SQL standard - we have to use "aColumn IS NULL" but not many people realize this distinction.

gemma-the-husky · Apr 1, 2011

major job for me - i tend to use functions to read variables, rather than use form references across the board!

Banana · Apr 1, 2011

Well, if the function does something like this:

Code:

WHERE myFunc() = True;

or

Code:

WHERE myCol = myFunc();

Then Jet can optimize it away. The real trouble come in this:

Code:

WHERE myFunc(myCol)=True;

This require re-evaluation of function for every row, and thus cannot be optimized away so Jet has no choice but to pull down all rows and do the evaluation locally. A shortcut you can do is to do:

Code:

WHERE someCol BETWEEN X AND Y AND myFunc(myCol) = True;

Then Jet will be able to defer the evaluation by sending in only "BETWEEN X AND Y" to let server evaluate this one then send all records and filter it out with myFunc(myCol). Not the most optimal route (probably may want to use stored procedure) but that is an alternative to remember if change is simple enough and the processing time isn't too long.

gemma-the-husky · Apr 1, 2011

Ah, I see

generally it's where mycol = somefunc
occasionally its where mycol = forms!someform!somecontrol

but very occasionally, with complex filters its

where myfunc(var1,var2,var3....) = true

and these don't perform very well in access either!

Banana · Apr 1, 2011

Right. With first two cases, Jet is smart enough to first evaluate them and translate them into a hard constant and send the query as such. With last case, nothing Jet can do about it but just suck it up and evaluate each row one at a time.

BTW, just to be explicit - this isn't just true for user-defined functions but also for built-in function. This may suck:

Code:

Year(myDate) = 2009

In this case, though, there is a ODBC SQL year function so Jet is able to defer the Year() function back to the database but this still sucks because no RDBMS (at least I don't know of any) can optimize on this. Hence, many SQL experts would say you want to do this:

Code:

myDate BETWEEN '2009-01-01 00:00:00' AND '2009 12-31 12:59:59'

This will then perform much better because index on myDate can be then used.

gemma-the-husky · Apr 1, 2011

I take it, that thoroughly optimising a big dbs for SQL is a major project then.

Banana · Apr 1, 2011

Yes, if the project wasn't written for SQL backend in mind from start.

Convert Exisitng Access Database to Access Frontend MySQL backend (1 Viewer)

DBnub11

Registered User.

access_developer

Registered User.

Galaxiom

Super Moderator

Banana

split with a cherry atop.

ozinm

Human Coffee Siphon

gemma-the-husky

Super Moderator

Banana

split with a cherry atop.

gemma-the-husky

Super Moderator

Banana

split with a cherry atop.

gemma-the-husky

Super Moderator

Banana

split with a cherry atop.

gemma-the-husky

Super Moderator

Banana

split with a cherry atop.

Similar threads

Users who are viewing this thread