10/14/2014 9:28:45 AM
Putting Access Databases on the Web
By Richard Rost
I've been getting this question a lot lately: "How can I put my Access database online so other people can use it?" There are several different things you can do to put your database online, depending on who your users are, what kind of security you need, how much work you want to put into development, and what you want the end-result to look like.
Option 1: Access Front-End, Hosted SQL Server Backend. If all of your users are working on desktop PCs (or laptops) with Windows and copies of Access, then I recommend using an Access database front-end file (a database that contains your forms, reports, code, etc.) attached to a SharePoint or Microsoft SQL Server to get the data. I cover how to set this up in Access Expert Level 24. The benefit of this method is that you get to use your actual Access database as it is now, and you can just move your tables up to SharePoint or SQL Server. Relatively easy. The down-side is that distributing updates to your front-end can be a bit of a pain. You have to send them new Access ACCDB files when you make changes. Not terribly hard. Your end users need a copy of Access installed, but you don't have to BUY Access for each of them. You can just get the FREE Access Runtime Edition. I find that this is the BEST solution available for teams of users (employees of a company, for example), but it doesn't make a good public-facing option. Best of all, SQL Server hosting through companies like GoDaddy is real cheap. You can get set up for less than $10/month.
Option 2: Access Web App. If you want your team of users to be able to access your data from a variety of devices (phones, tablets, Macs, etc.) then you can create an Access Web App. This is a database that resides completely on the SharePoint / SQL Server and provides you with an interface that you design that runs completely in a Web browser. This is OK for REAL SIMPLE databases. The technology is still in its infancy, so I wouldn't plan anything too elaborate as a Web App, but if you just need a simple customer/order/contact lookup system, this works fine. Yes, I'll be doing a seminar on this in the near future. Yes, you can make an Access Web App public-facing, but it's not very useful as the data is READ-ONLY to the public. You can use this, for example, to browse a product list.
Option 3: ASP Web Site. The third option is to create your own web-based database using ASP (Active Server Pages) and a back-end database (which can be in Access or SQL Server). I teach how to do this in my Access Web Database Seminar. This is the ONLY OPTION that I recommend if you have a database that needs to be viable for PUBLIC use. Every other option on this list is only good for trusted groups where your users have usernames and passwords to log in. If you need to be able to collect public data on your web site, or let users to your site view data, then THIS is the only option I recommend. My web sites are all built using ASP and either an Access database or SQL Server for the backend storage.
Then, of course, there are a million different types of remote access solutions. If you only have one user at a time who needs to work remotely you could set up Windows Remote Desktop or VNC. If you have a few users and a Windows Server you could use Terminal Services which is like Remote Desktop for multiple people (you'll need server licenses, of course). And there are a million different third-party "remotely access my server" programs out there.
My Recommendation: if it's a group who can use PCs with Access, and you want the FULL capabilities of the Access desktop application you've spent a lot of time building, then go with option 1. If people need to use phones/tablets, and your data collection needs are REAL simple, go with option 2. If your database needs full-scale PUBLIC access, with user account creation, logons, shopping carts, and the works, you pretty much need option 3. Personally, I use option 3 for just about everything that I do because I love to program in ASP. It's fast, easy (for me), and secure. But that's just my opinion.
UPDATE: I've also heard from a lot of people who just want to be able to access their Access databases remotely while they're on the road. If this is the case, all you really need is some kind of remote-access software. I personally use TightVNC. It's easy to use, relatively simple to set up (you need to open a port in your router firewall, but if you are building Access databases, you should be able to follow their instructions to do that) and there are client applications for PC and Android, so you can even access your database on your phone or tablet. I use this solution myself when I'm on the road if I need to get into my Access database to check customer service or orders.
Keywords: access web database