MS Access to MySQL database Backend conversion

[Hap007]

Hi,

I have been asked to convert the MS Access backend to a SQL backend for my client's existing software system.

Currently the Client has an MS Access 2003 Frontend, Linked to and MS Access backend

I recommended converting to MS SQL.

My client wants the backend converted to MySQL.

Has anyone done this before?
Is this possible?
There have been many years of development on the Frontend in MS Access, so the frontend must stay MS Access.

I would like to know if this is possible and any suggestions you might have on handling a conversion like this.

Thanks,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[MichaelRed]

it is certainly 'possible'. Ms. A. can use any dataset for the BE, MySql has been mentioned often in this context. I have (so far at least) successfully avoided the specific use of MySql, but have used other (relational) data base engines.

In maintaining the FE in Ms. Access, there will be a tendance to not convert transient (e.g. temporary tables used to hole intermediate resulte or form / report specific data sources) data to the BE. Doing so will severly limit the performance improvement usually expected from the conversion to the 'better' database engine.

Other than the initial expense for the development package, there should be no reason to exclude SQL Srver from the potential BE data base engine. The run time version may be distribuited without cost!!

Another issue (regardless of the BE engine) is quite troublesome. Most Ms. A. apps have some (often A LOT) of sql code buried directly in the FE (along with those troublesome temp tables). The generic (compiled?) queries will often translate pretty directly into other dialects of sql (MS. A. includes an upsize utility which will do a lot of this automagically to SQL Server). I do not know if MySql includes any such facillity or how well it might work, but even with the (sql server) upgrade wizzard, much of the difficulty in the actual conversion derives from the total lack of conversion of these sql strings.



MichaelRed

 

[Remou]

I have done this. MySQL has a toolkit that works very well.

http://dev.mysql.com/doc/migration-toolkit/en/index.html

More information:

http://wiki.lessthandot.com/index.php/Transfer_Access_Database_or_Table_to_MySQL

http://lessthandot.com

 

[Remou]

As an aside, is your client aware that MySQL has been sold to Oracle, which is likely to cause, at best, a lot of changes or, at worst, the death of MySQL?

http://lessthandot.com