[fjohnson]

Hello All,
I just joined this forum for your expert advice and to use as my support for the task ahead of me.

I currently use MS Access for all my data on the local end, and it is converted for MySQL on the server. I want to migrate to use a SQL server on the local side, and I am wondering the best route to go. I do however want to keep access to use as my front end for the data input as we have put a great amount of time and effort into customizing it.

I guess my question is what would be the fastest and most efficient way to access the data in the MySQL database on the server, and what would provide the best results in our day to day operations. I would like to pull only the data relevant to each query and not have to load complete tables. I aslo do not want to be weighted down with slow ODBC drivers. I need speed.

So, that being said, does anyone have opinions or good advice on how I should go about this and what would be the best SQL tuse on the local end.

Thanks very much!
Fiona

[DavidAtMicrosoft]

That depends on a couple of things, Fiona. You've got options ranging from embedded scale with SQL Server Compact Edition through Express Edition and on up to Workgroup Edition, Standard Edition, etc. If it's an occasionally connected scenario, Sync Services for ADO.NET would be a platform option to keep the edge databases in sync with the primary.

[fjohnson]

Thanks David! I had not even thought about which edition I should look at. I guess I do need something pretty large scale. It would be used and connected to pretty much all day by about 20 or so people. There is a lot of data and I would want to be able to pull just what I need at the moment and not have to load the entire tables, which is very cumbersome.

I'm not exactly sure which way I should go, but an advice is greatly appreciated.
Thanks again!

[healdem]

why not keep your existing SQL.. if you have spent all that time and effort then keep the existing JET SQL that is bundled with Access.

otherwise I think a significant re-write is in order, and cut out all the local tables and talk direct to the remote server.

[fjohnson]

I plan on keeping my existing MySQL on the live production server. My migration will be from Access (all my data on the local end is in Access) to a bigger SQL server on the local end. It has just grown way too large to stay with access. I have customer service reps accessing the data throughout the day. We also input a lot of data locally and then upload it to the MySql server many times throughout the day.

[DavidAtMicrosoft]

If you're looking to keep a local store in sync with another remote DB, you might want to consider using SQLCE on the client and use Sync Framework to keep your local cache up2date: http://msdn2.microsoft.com/en-us/sync/default.aspx. There are some limitations with SQLCE, so you'll want to evaluate carefully before leaping into a development effort; SQL Server Express Edition is more full-featured than SQLCE (and still the same price ;-).

[fjohnson]

Thanks David. I will look into it.