Updating a Web Hosted database
I have the following scenario and wish to know what options (and pros and cons) exist for updating a web hosted database from a LAN based database and the pros and cons of using either Access or SQL Server as the database on the web host.
The scenario is:
- We have an Access database on a LAN that is updated via a VB application running on the LAN.
- The database contains client accounts and related data which we would like to make available to the clients via the Internet (about 2000 clients)
- We have created a prototype web site that references an Access database whose schema is a scaled down version of the LAN version's Access database (i.e. it has only those tables that contain data needing to be referenced) and for now only contains a few clients (for test purposes)
- The web application is intended to only allow clients to view their own data (i.e. there is no updating or other info that needs to come back to the LAN database) - at least for the initial iteration of this Web app.
- We would like to establish an efficient means of updating the Web hosted database on a daily basis (likely at the end of each business day)
- Right now for the purposes of our prototype, we have a procedure that extracts the required data from the LAN's Access database and creates a complete copy of the scaled down Access database that must then be uploaded to the web host to replace the copy of the Access database that is held there.
- We realise this "complete replacement" approach is not very efficient especially if the scaled down database is of any size, and so we wonder if there is some easy means to just update the web hosted database.
- We are aware of things like Replication, although we have not ever worked with it, and we don't know how it would work anyway in an environment with a database on a Web host (i.e. how would the two databases "connect").
Any help that can be offered would be appreciated even if it is to direct me to a resource where we could read up on such an issue.
I did not read your post word for word, but: mssql server can be updated remotely from asp or asp.net pages if the host is accepting fromote requests. It may also be updates through a vb application using ado.net. I would say ditch the access data base all together. It is easiest to do remote updates of a remote sql server data base from asp or asp.net though from my experience, you use the same connection string, the only difference is that the url in it happens to not be on the same machine. This will open you up to some lag of course if you have a lot of users. You might consider download msde or getting a copy of mysql, as those are free and installing them. Any server data base will be loads better then access which is abysmal for large amounts of data and users.
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center