The types of scenario you are asking about are possible in MS Access. In fact I have been doing this for years.
However, your back end as a general rule cannot be a file share in a folder. So using server 2008 or whatever does not really help a lot in this case. For sure the server edition of windows does have some additional options in terms of networking ability (such as VPN and ability to route packets etc). So often one does use a server due to additional options. So no question systems like 2008 R2 even has VPN ability baked right into the product.
However, while the server could allow use of folders over the internet, for the most part you'll have to adopt a server based DATABASE for the backend. When you use a server database then you can most certainly continue and utilize MS Access on the user's desktop.
So it's not practical to use an access backend over the Internet and install the access front end on each computer and then connect to the back end. However, with a server database, you can. I much explain this problem and issue in the following article:http://www.kallal.ca//Wan/Wans.html
You can read the above and you much understand what is going on.
However, it is most certainly possible to install access on each computer, and use a database server for the back end as I been doing for some time now.
While I been hosting sql server for those clients (I am using a cheap low cost web provider), I should point out that the term cloud computing often means a good deal more than just hosted services. In effect cloud computing means large scale shared computers and those resources are delivered much like a utility (water, power, etc).
I would say the main difference between a hosted server and that of cloud computing is the cloud system is a specific operating system that is designed with delivering computing as a utility on an massive scale. With a hosted system your web server or SQL server or whatever computing resource you need is installed on a particular computer.
In cloud computing your cost and resource is spread over MANY computers. This is much like each user purchasing a water pump and running a ground water system compared to the large scale municipal water system.
So I just wanted as a FYI that using some hosted service such some web hosting, the term cloud computing tends to mean large scale resource sharing and large scale management of those computers.
So if need 150 sql servers to run some really cool application then the top level fabric controller will go out to the massive farm of computers and find 150 running sql servers for you. And those resources do not exist, then 150 sql servers will be provisioned for use on the fly. So compared to a hosted system, cloud computing tends to suggest that resources you use are dynamic and not even attached to one of their hosted computers. Typical web hosting means your software and cpu used is on ONE box.
Access 2010 has support for the new Microsoft cloud operating system called Azure. So, Azure is simply a cloud operating system that Microsoft hosts in one of those massive server farms. And then they created Azure versions of software like SQL that runs and is compatible with the cloud operating system. So, we often say the SQL Azure version to distinguish this version of SQL server that runs on this new cloud OS. That cloud OS is highly optimized for this task and thus has no GUI etc.
Keep in mind you do not necessary need Access 2010 to use database servers that are hosted. The way I been doing this is find a web hosting company that has SQL server or MySql support, and ALSO allows odbc (external) connections to the database server. The result is I am paying for Web hosting, but not even using or caring about the web hosting. So I just wanted the database server. Right now, this approach is cheaper then cloud systems, but I suspect it shall not be long before the cloud vendors start offering better rates. (right now a 1 gig SQL server database on Azure is about $10 per month right now).
So Access has joined the cloud party and can use (connect to) the SQLAzure version.
Another new offering for Access and cloud is what we call Office 365. This again is a true cloud OS based hosted option for Office and Access.
Once again, you can install the Access client on each desktop, but data is up in the cloud (in what they call office 365). This option is really SharePoint for the back end. Even more interesting is office 365 will support the new web publishing features in Access 2010, or what we call Access Web services. So, there is a good number of options for Access in terms utilizing cloud based systems.
So, Access is going down this road and Access has joined the cloud computing bandwagon. The options and offerings for Access and the cloud are increasing by the day.
The business pitch as to why small businesses love and use access so much is
due to it being affordable, easy to use, and no need for expensive server,
no maintains etc. So Access is great for small business because is more affordable for the
benefits it offers as compared to other solutions. I mean just one service call of one hour to install some update on the server tends to cost more then what a hosted server for Access will cost.
Cloud systems will not be adopted by everyone, but we are seeing Cities and
municipalities and small business (all of which are tight for money)
adopting these types of systems. They simply are cheaper to run then having your own server.
Business tends towards these lower cost systems much like water tends to
flow down to the lowest spot it can find. As business gets larger, then
often the case for Access often again becomes weaker.
In a funny way the same pitch about cloud computing and what it offers to
small businesses is rather much the same pitch as to why Access been so
popular with those small business.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada