Hi All.
I have a FE-BE MS Access database in my firm, on a 100 Mbps LAN, and am planning to switch over to mySql. However, there are certain questions, which I need answers to, before I can be sure that the solution will be worth all the effort.

1. In the MS Access scenario, Access has a tendency to send all the records from back end to front end and from those records, based on filters and queries, the required records are filtered out. But the problem here is that even though certain records are only required, all the records get sent through LAN to client machine where the FE resides. Its ok on a LAN, but what if a WAN is to make use of this system? My question is if I use Access as frontend and mysql as backend and continue to do processing on client side, will mysql send all the records to FE or only selected ones?

2. If I use mySql, will it make sense to have all processing done on client side via the FE or get the processing done on server side by mySql itself? Would mysql be able to cope with the query processing load from several number of pcs on the network?

3. Would using pass-thorugh queries in MS Access be a proper solution?

My main concern is to minimise data traffic over the LAN and WAN so that users would be able to use the database with fair amount of ease. Any suggestions regarding these would be highly appreciated. Thank you all in advance for taking time to answer the queries.

Regards:
Prathmesh

re "1. In the MS Access scenario, Access has a tendency to send all the records from back end to front end and from those records, based on filters and queries, the required records are filtered out. But the problem here is that even though certain records are only required, all the records get sent through LAN to client machine where the FE resides. Its ok on a LAN, but what if a WAN is to make use of this system? My question is if I use Access as frontend and mysql as backend and continue to do processing on client side, will mysql send all the records to FE or only selected ones?"
-> MySQL sends just the results of the SQL query across the network.


re "2. If I use mySql, will it make sense to have all processing done on client side via the FE or get the processing done on server side by mySql itself? Would mysql be able to cope with the query processing load from several number of pcs on the network?"
-> do the processing on the server side. The client should just send SQL queries. Or are you talking about using stored procedures? And yes, MySQL can cope. It can compete with Oracle! Note that you may have to change the default MySQL configuration settings i.e. performance tune it.


re "3. Would using pass-thorugh queries in MS Access be a proper solution?
My main concern is to minimise data traffic over the LAN and WAN so that users would be able to use the database with fair amount of ease. Any suggestions regarding these would be highly appreciated. Thank you all in advance for taking time to answer the queries."
-> I don't have enough knowledge to comment on MS Access pass-thorugh queries. In my opinion, MS Access I guess is an okay/familiar windows front-end. But why not use a web-based front end? I no one in your organisation has the skills via a freelancer e.g. via rentacoder.com