Client Server application Development

I am developing an application to synchronise data between remote MS Access 2003 databases and SQL Server. I read data into a dataset, and then update SQL from that. The problem is that some of our WAN connections are slow, and doing this from a single program is not effective. I believe that the best solution would be to develop a 'server' application to install at the remote sites that will get the dataset, and then pass this back to the central 'client'. Similarly, with updating the other way, the 'client' getsthe dataset and passes it to the 'server' which updates the local database.
1. Is my thinking correct, or is there a better way of doing this ?
2. I haven't a clue how to write client / server code - are there any samples that I may use as a guide ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you think about doing that without developing? Use only SQL Server features? Like Import data?
philcexpertAuthor Commented:
It will probably require development, unfortunately. Our remote data is a derivitive of the Server Data (4 tables on the server manipulated into 1 on remote), and I am not pulling the entire table. In the app I've written so far, I get key data into a dataset, and then compare it with selected tables on the SQL server, building up a dataset to transfer back to the remote site only of what is new ( not in the original dataset ).

I'm sure it could be done quite successfully by importing the data and then running stored procs against it to achieve the same, but this will also use a lot more network traffic, which is slow.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your solution looks good. To increase speed and performance check if source tables have the indexes that you need.
There's no secret about Client/Server application. Just create a .EXE application and distribuite it to clients machines.

Good luck
philcexpertAuthor Commented:
Thanks. Are you sure a C/S Solution would not be better ? I ran the app from home, where I have a network traffic monitor, and the network was frantically busy while SELECT'ing into my DataSet. Sure, I agree that indexes would reduce this as well as speed it up, but I cannot get it out of my head that getting the dataset locally, and then sending it back to the client would still improve speeds.
Vitor MontalvãoMSSQL Senior EngineerCommented:
No. It's better to let Server do all work.
To reduce traffic you must bring only the data you need. Not more columns and not more rows. Only what you need.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.