Software for upsize & sync to Mysql

[JBRTaylor]

After months of work I have a database which, I am really proud of and is working really well until one day a colleague asked about access to the database.

At the moment the database is used by about 5 people and the database file lives on a shared folder and everything is great but I would like to now give access to small areas of the database to around 500 users. Web access appears to be the only option, purchasing 500 Access licenses is not an option.

I have tested the database by uploading the tables to a Microsoft sql server via the migration tool and although there were a few problems with dates and VBA sql code, it worked well. I have also learnt to create web pages in php to interact with the data so this looks like the better option for me.

During my testing and learning I found there was much more info on the web about mysql than Microsoft sql so I think Mysql would be the better option.

Being a little nervous about this move I would really like to find some software to help me upload the tables and link them to the database. But it would also be useful if it could syncronise the sql tables back to the access database and disconnect the linked tables (disconnect from the sql server after updating the access tables with the new data on the sql server) if I find things are going wrong for me. I want to be able to revert back to where I was but keep all the data added or changed since the migration.

Is there software out there that may be able to help. Any advice would be welcome.

Thanks
Jonathan

 

[Rx_]

SQL Server Migration Assistant for Access
Free tool.
Don't understand why you need to purchase 500 Access license?
Microsoft Access 2010 Runtime (free)
http://www.microsoft.com/en-us/download/details.aspx?id=10910

The Web PHP front-end version will reduce the rich user interface options found in MSAccess. 500 users will have to post, repost, all the time, wait for a refresh. You should expect many less events on the PHP front end. Sometimes that leads to more network traffic, more maintenance over the lifecycle. Would be very surprised that MySQL has more information than SQL Server. But, MySQL has been catching up with SQL recently.
Some associates of mine are converting large MySQL back to SQL Server for large institutions. It all just depends on the requirements. There is not one solution that fits all. MySQL has a free version and a paid support version according to the article.

http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html
This site offers conversion information from SQL Server to MySQL. That might be your shorter path.
This article isn't completely new, but the writer uses both and provides some links that might be useful.
http://www.people.vcu.edu/~agnew/Misc/MySQL-MS-SQL.HTML

Access front-end can easily have 500 concurrent connections to SQL Server backend.

Taking the SQL Server data back to Access might require understanding Object / Collections code. There are many articles about taking SQL directly to MySQL.

For 5 to 500 users, my Access is Cloud enabled with Citrix.
The bandwidth used is way less than a PHP application. It also allows Access to run on each user's PC, Mac (and other platforms) with out an Access license. For Mobile use, it is about 24k of bandwidth.


Good luck with your endeavors