2

Our company has used Access for its database needs. It wants to stick with the current database frontend, but migrate the tables to some cloud based solution. We do not want to host SharePoint or pay a monthly fee for Office 365. I have used MySql as a backend, but we had to install drivers. We will also be gathering data from Google Forms on a regular basis. Can anyone suggest possible options for this combination? Data from Google Forms, Access frontend, and cloud based backend.

Thanks!

Nathan
  • 1,241
  • 3
  • 12
  • 17

2 Answers2

6

When you say you don't want any monthly fees, are you thus expecting to find some database server system for free and without cost that allows external connections? I simply don't think you going to find such a service for free.

Since office 365 starts at $6 per month, then I not sure why you think this is too high of a cost here? So you could certainly consider to up-size your Access back end tables up to office 365 and continue to use your Access front end. And more amazing is with Access 2010 this means you get a "off line" and disconnected mode. This means that your application will continue to run EVEN WITH NO internet connection. The instant you find a wifi then the data sync process starts again. And this sync is not file based, but record based and is really replication built into the product and this setup requires ZERO extra code on your part.

And since your back end is not an access file, then you can now scale out to millions of users – the only real limit is the size of the 365 server farm (a super huge computer farm).

Keep in mind that in addition to simply linking your Access application to these office 365 tables, you can also publish Access web forms to office 365. So in the following video at the halfway point I switch to running the Access application 100% inn a browser:

http://www.youtube.com/watch?v=AU4mH0jPntI

Note the above resulting browser application does not require any ActiveX or Silverlight. And as noted, again this is again based on that massive server farm.

Another cloud approach is to consider SQL Azure. Access 2010 also has baked into the product the ability to use the cloud based edition of SQL server running on the Azure OS.

So, you could consider using SQL Azure, but that going to be about $10 per month.

I think the office 365 deal at $6 per month is the best bet (and you get lync communication which gives you remote desktop support for your customers or perhaps for supporting this application!). I actually think that Lync makes the $6 worth it alone. Toss in most SharePoint features and document sharing (including free web based Word, and Excel), this is hard to beat.

So it not clear here why you are avoiding office 365, but will have to adopt some kind of server setup here and I not aware of ANY system that going to allow free external connection from your desktop client software such as Access.

I think the best solution is 365 for use with Access.

Another low cost solution I used in the past is to consider some VERY low cost web hosting sites that also allow external connections to their database. In fact I did this for a good number of years (I did not even use the web site hosting!). I simply purchased the monthly web site and used the ability to "connect" external to the database server that was part of the web hosting package. This I did for a good number of years, and at VERY low cost. I thus was deploying Access front ends to multiple places and using this cheap-o web hosting account.

However, I am much now dropping this low cost web setup with the arrival of office 365 being even less cost than that cheap-o $9 web package I was using for this.

So, at the end of the day, I don't think there is any free hosting that allows external database connections, but the most low cost approach is office 365 at this point in time.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Nice post. Could you provide any links to how the front end was able to connect to remote host (external database server - as in the 2nd approach)? Thx. – NoChance Dec 08 '15 at 10:12
  • This is an addendum to my comment above, was the 2nd approach implemented via ODBC? – NoChance Dec 08 '15 at 11:13
  • 1
    Yes, I used standard ODBC to the hosted web site SQL server. Not all providers allow such external connections (and in fact fewer and fewer allow this due to security - opening a web site server to the wild internet can be a security issue and hole). It really depends on the provider, but some do allow ODBC to MySQL or SQL server. You have to check on a provider by provider basis. – Albert D. Kallal Dec 10 '15 at 02:53
  • Thx. for the explanation, – NoChance Dec 10 '15 at 20:36
0

Cloud based does not mean you have to think about your database backend any differently, you could it you wished stick with MS access, however as Access does not natively support remote connections then you would need to setup a VPN to your cloud server in order to connect to the .mdb, .aacdb file.

Dedicated database servers are always a nice option (MySql, Sql Server Express, Postgre SQL to mention the free ones) but you will always need to ensure you have the necessary drivers installed (shouldn't be the end of the world)

As for Google forms, I don't really have much experience with those but I imagine Google would have made every effort to ensure they can be implemented relatively easily with existing database products.

Matt Donnan
  • 4,933
  • 3
  • 20
  • 32
  • 2
    The person makes no mention of remote connections to Access. Access is not the database and is simply a software tool to build a UI. You then pick the database system such as MySql, Oracle, JET etc. The user even points out they have used MySql as the back end for the Access application. So nothing EVER was being stated or suggested or being hinted or asked about connecting to Access. You don't connect to Access and in fact cannot connect to Access. So Access is simply a development tool and system that connects to your database system of choice. – Albert D. Kallal Aug 16 '12 at 00:16
  • @AlbertD.Kallal The question to me reads more as though Nathan's first port of call has been a MySQL backend in relation to the cloud challenge. "Our company has used Access for its database needs. It wants to stick with the current database frontend, but migrate the tables to some cloud based solution." sounds as though the existing non-cloud system uses Access as the backend also. I'm really confused with your obvious experience with Access that you say "Access is not the database", Access is both the UI and also can be used as a database if you wish. – Matt Donnan Aug 16 '12 at 08:14
  • @AlbertD.Kallal Note when I say Access can be used as the database I'm referring to Jet/Ace but that's obvious enough. – Matt Donnan Aug 16 '12 at 11:22
  • Quote:"It wants to stick with the current database frontend". Well, we JUST AGREED the database is MySql or some other system. The data not even being stored in Access so how can this be using an Access "database" without any data stored in Access? Does that make any sense to you? Sure often people interchange the term between the Access and the JET database engine and such a distinction is not important in most cases. However, it does not make sense to state to continue to use the Access database. If you reffering to the front end, then you not reffering to JET as the database system. – Albert D. Kallal Aug 16 '12 at 17:43
  • @AlbertD.Kallal No I understand, but the question does not say MySQL was the original backend. "I have used MySql as a backend, but we had to install drivers" sounds more like a first attempt at the cloud based option? – Matt Donnan Aug 17 '12 at 07:49