Update a local/client Microsoft Access Database from a server (MS SQL Server2005)

Question

I've got a website that runs on a shared hosting environment, using ASP.net 2.0 (C#) and MS SQL Server 2005. I've recently been asked if I can integrate my website with a piece of third party desktop software that uses the Access runtime as its database (transparent to the end user).

Primarily I want to be able to offer users of my website the option of exporting their data into the Access database on their local machine. The data schema's match sufficiently, the question is how to actually do this, and in the simplest way possible for the user.

Simply having a webpage update the local Access database isn't possible due to the obvious security restrictions. I've considered asking them to upload the Access database to the server, so I can migrate the data then allow them to download it again, however the competency of the users of this software is such that even locating the Access database, let alone uploading and downloading it from the website might be too complicated.

I've also considered if Adobe Air or Silverlight could help here, but don't know them well enough to know for sure. Similarly I'm assuming another exe could be written to perform this task that the user could simply download and run, however my experience is in web development, not program development, so this isn't a 100% certainty for me, or an ideal development option for me.

So, can this be done, and if so what technique can achieve this, with the stated aims being ease of use for the end user, followed by ease of development by someone with web development as their main skill. Many thanks!

Answer 1

Instead of trying to do this in a web page you might just expose some views from your sql server to some client specific logins.

Then within the Access application, allow them to tie to your sql server. You might even provide an access application for getting the data from your site and stuffing it in their local access database.

Answer 2

You may find this answer of interest: http://stackoverflow.com/questions/608480/best-way-to-stream-files-in-asp-net/609151#609151

It is about transferring a file from the server. You could save Excel or CSV and use that to update Access.

Answer 3

In my work we have done something similar that is transparent to the user by creating an ActiveX control. The problem is that you are limiting the users to use only Internet Explorer.

I think that the best way to achieve what you are trying to do is by installing a service in the client's computer. If creating a service is beyond your experience you can post a project in a place like oDesk and find somebody that can help you with the development for the money that you are willing to pay to complete your project.

Good Luck.