Hello friends.

This is mostly an "academic" question but I am curious if it is possible to have many copies of the access front-end (one for each user), the access backend for these users (or sqlserver or MySql) in a "Cloud Computing Technology", running Windows Server 2008 ?

For instance, when I install my software it uses the folder "c:\programfiles\My_Softaware". How am I supposed to have in the same cloud computer more different copies of my software since it is installed directly to that path (I am asking this because -perhaps- there is a way to avoid changing the installation path to c:\user\ ...)

Anyway, has anyone faced something similar or tried cloud computing with Ms Access ?

Thanks a lot ...

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
kallal@msn.com

Whow !!!

Albert thanks a lot for the answer. You have answered me to a lot of questions I had but I am not sure if I have understood correctly this :

- It is ok with me to use a different Back End (eg like MySQL which is fast and free). That way I can have the backend MySQL hosted on a web server which allows me to have odbc connection. So I can have some how cloud computing of my backend database, hosted to a web server, which is fast and cheap. Up to now everyting is ok and understood.

What I currently want to achieve is to have some kind of remote desktops of my software for my clients, where some of them will be using their own data and others will share data (something like one server database with many clients). I am not sure if I can achieve this with one Cloud Server ...

Thanks again for your excellent answer ...

Yes, you can do this with just one web hosting package. And you have several users share same data, or diffent database for back end on that one plan.

As I noted the idea here is to use some low cost web hosting plan of which all do come with type of database server. In fact, for those web hosted systems, you do not install or setup MySql or SQL server, but the database server is included. As a result, you not find a whole lot of difference in per month pricing. However, at the end of the day, you have the right idea.

The only thing to keep in mind is that not all web hosting providers allow external ODBC connections to the database server as part of the plan. A good many do, but you have to look at the plan options to see if it is allowed.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Thanks a lot Albert

I will contact my Web Hosting provider to ask for ODBC Access.

As you mentioned, the good point with the Service Provider's ODBC is that they have already set up SQL Server or MySQL.

Thanks again for the information ...

Couldn't you use hosted terminal services with Windows server 2008 and use a typical 2 file (front-end and back-end access database). Just have multiple front-ends one for each user and have one back-end.

I'm not sure I understand why you are saying this doesn't work?

mandrews I believe that what you describe works well but I have the following problems:

1) I can't afford having a huge Server loaded with 100 - 200 mde files using some hundrends of backend files as well.
2) I want to low the cost of moving from town to town here in Greece (1.7 Euro / Lt of Gasoline !!!!) for setting up my food traceability software (based in Access ) that is fully customizable (and not easy customizable because of the variation of the production lines that each food industry is implementing).

So I see that cloud computing is a solution to all this ...

There is still as lot of to do ...

I understand that a hosted sql server db and using access front-end does work. I have used it, you just get a little slowness because of data coming across the wire. It's a very good way since SQL hosting is pretty cheap.

However full TS hosting should also work (one front-end per user and one back-end db in access) however expensive since you have to pay for each user.

I was looking at a company http://www.acs-ids.com/
which provides server based software with lower costs that TS licensing.

I know some software that is written in Access that uses this (all Access dbs, no sql server), small amount of software needed on pc or mac client to make the connection (it's similar to remote desktop).

My two cents,
Mark

That's because just sticking your database tables onto a SQL Server doesn't take full advantage of SQL Server. You need to modify a bit of things, generally changing to pass-through queries and replacing large search functionality into a SP. This way you don't end up with the large amounts of data coming across the wire.

I'm only saying it slow when you need large amounts of data (such as showing a big list with lots of fields). Internet vs. Office LAN vs. one person's hard drive. Yes Pass thru queries are good to use (run sps, and sql on the server so less data comes across the wire).

So I agree that pass-thru's are good but your assumption of what I was saying was wrong.
Also pass-thru's cannot always be used (for example subreports linking to main report).
I used to do Access reporting of a data warehouse with about 4 million records in the biggest FACT table and everything was pass-thru with SPs and it worked really well. Of course my app was on a LAN not SQL over the web.

Using everything hosted and doing screen scrapes is sometimes a better solution, than client/server solution.

Mark