Shortly after posting, it occurred to me that this is much better suited for a community Wiki page where others can expand upon it. As such, please go here for an updated version: UtterAccess Wiki: Web Enabled Access
So you want a web enabled Access App? Here’s your options:
- Access Web App (2013)
- Access Web Database (2010)
- Access Desktop Application (any version) with Cloud-Based BE (Azure, MySQL, Postgre, SQL Server)
- Access Desktop Application (any version) on Terminal Server (RDP, Remote Desktop)
- Access Web App/Desktop Hybrid (Web App Base, 2013)
- Access Web App/Desktop (OnPremise Server Based, 2013)
- Access Desktop, OnPremise Server w/ SharePoint Web UI Collection (any version)
- Access Desktop, Cloud Server w/ SharePoint Web UI Collection (any version)
(please note the distinction between “Web App (2013)” and “Web Database (2010)”. This is official terminology and will be used throughout)
There’s more, but that’ll be plenty to give an overview of what we can do (in fact, I’ll leave some of the latter ones out entirely for the rest of the article). This is essentially a matter of determining what it is you need from your application, and choosing a model that best fits those needs. As you can see, there’s quite a number of different ways we can go about this, each with their pros and cons. Here’s a quick overview of the main ones listed above, taking a closer look at the pros and cons, as well as some example scenarios.
Access Web App (2013): GUI through Web Browser, no client installation required, anyone with a browser can access it (provided they have user login rights). Data stored in a semi-locked down Azure BE (cloud hosted, taken care of for you). Ability to make a public (don’t require login), ability to embed in SharePoint/Office 365 sites.
- Azure BE can be accessed by other systems for basic CRUD operations
- Mobile Ready: anyone with a web browser can access it
- Can be created entirely within Access once an Office365/SharePoint site is chosen for it to live on. Relative ease of creation
- Tons and tons of concurrent users supported (like, 1000 or more)
- Has “Apps for Office” integration: essentially, HTML5/jQuery applets that can be embedded into the Access App (albeit with some considerable restrictions on data access and cross domain scenarios)
- Azure BE has no object management capabilities, can’t create tables, views, procedures, etc
- Mobile Layout support sucks – they’re just not designed to be responsive on a phone (in terms of GUI layout, functionally, everything remains the same)
- Lack of robust lifecycle management
- Lack of robust programming model (Macros are used, and are much improved, allowing for a great amount to be done, but it doesn’t match traditional desktop VBA projects)
- No Offline/Sync mode.
- Porting an existing Desktop application will require a rewrite/redesign
Access Web Database (2010): GUI through Web Browser, no client installation required, anyone with a browser can access it (provided they have user login rights). Data stored in SharePoint Lists. This entire model isn’t officially deprecated (that I’m aware of), but is certainly a dead end.
- Offline/Sync modes. Can work offline and sync data back to cloud (SharePoint) when reconnected
- May be more easily integrated with other SharePoint features than a Web App (2013), as the data is stored in native SP Lists, instead of the Web App (2013) where it’s stored in Azure, thus requiring an extra data access layer between Azure and SP
- A decent amount of concurrent users is supported
- Internal structure of SharePoint lists holding the Web Database data is not very robust: large sets of records are quite slow due to this
- No further development from Microsoft on the entire model.
- Porting an existing Desktop app will require a rewrite/redesign
Access Desktop Application with Cloud Based BE: By using a cloud base BE and a Desktop application, we can have the data itself shared globally while retaining the robustness of Desktop applications (including VBA projects). Generally speaking, we can use whatever BE we want in this scenario: Azure, SQL Server, MySQL, Postgre, etc, so long as it’s hosted on the cloud somewhere (there’s many hosting companies available). A measure of concurrent users is regulated by the BE of choice, but generally speaking any of those mentioned should be able to handle a much larger amount of users than Jet/ACE (eg, much more than a local Access BE)
- Can retain all the robustness of VBA projects
- Relatively easy upgrades from legacy, local/LAN Access applications (as compared to Web Apps/Web Databases, which require a complete rewrite)
- Typically inexpensive, the only cost aside from the usual Desktop application stuff is for hosting the database server, which is usually a trivial monthly/yearly fee.
- Backend can easily be picked up by any other FE major platform (ASP.NET, WPF, WinForms, PHP etc., etc.) and as such has a wide and clear path towards further extensibility.
- Data security implementations are usually good enough for most industry standards
- Users Users Users! You can have lots… most major RDMS Engines handle user concurrency quite well (provided you plan ahead anyway)
- Data Backups are more involved
- Application logic may require a significant overhaul due to the WAN nature of data access. At times, this can be enough to possibly consider a rewrite of major portions of code, though the overall design of the application can at least remain the same.
- Typically requires some use of a fat database to get performance to reasonable levels (e.g., a bit of a learning curve if unfamiliar with database server related scripting)
- Performance!!! Many, many people mistakenly assume that a cloud based BE will have terrible performance. While this *can* be true, there’s also no reason that we can’t have a perfectly adequate performance with a cloud based BE (see note above about overhauling to work well over a WAN). Done properly, an Access FE should be able to have the same speed/feel as a typical web based application (eg, your bank’s website, or other such data-driven websites). A friend recently told me that their company has a 3-second limit on form openings: all forms must open in 3 seconds, and I concur, it is doable. 3 second loading on a cloud BE is pretty good, I think. Quite reasonable.
Access Desktop Application on Terminal Server/RDP: If you have an existing desktop application and want make it available to a few people without location restrictions, this is often the easiest way. Essentially, you just give the required people remote access to a computer that has the application installed. This can be done by opening the existing computer to accept RDP connections, or by moving the application to a dedicated computer (or by putting the application on a hosted Virtual Machine).
- Very easy, very little (if any) application changes required.
- Limited Users and increased complexity of setting up users via RDP. A separate FE file should be maintained for each user
- UX over RDP is typically a little less “nice” than a local or web driven interface (usually this comes in the form of visual styles being downplayed)
- If hosting a VM online for the purposes, costs will be a bit higher than hosting just a database server.
Hybrid Desktop/Web App Database: Here’s an interesting solution. Keep your existing desktop/LAN database in place, add a Web App separately, and as a feature extension of the original LAN setup, you can connect to this to the Web App’s underlying Azure database to interact with that data as well. This could mean a push/pull setup (where the local DB takes information entered via the web app, and possibly pushes a bit of core data to it as well), or – less likely – could have the local database integrate the Web App’s Azure database so the core data from the desktop application is actually hosted on the could as part of the Web App.
This model can actually encompass the rest of the stuff in the initial list in the article. The same idea can be used with 2010 Web Databases, where the data is stored in SharePoint Lists. Access can connect to these lists, or really to any other data source. In fact, it’s a fairly safe bet that many organizations with a central database system implement some sort of external data store.
- Can maintain the core application without much change requirement while taking advantage of web capabilities for certain needs.
- Relatively easy to set up: Web Apps can be targeted to specific cases and not require a strong underlying framework to support them, and would be later integrated into the core database
- This setup usually involves data syncing at some point. Data syncing can be an ugly proposition. If you need to pull in new data from the Web App source on a nightly basis, and possibly push some supporting data back out from the local database to the Web App databases on a nightly basis, this probably doesn’t present too much of a problem. However, if the two sources would need to be kept fully in sync at any given point of the day, then a cloud based core BE is likely a much better solution.
An excellent use case for this type of scenario would be for a travelling salesman. His company’s core database would be set up on a LAN back at the office, but they’ve also provided a nice little web app so he can use his phone or tablet to jot down a few notes and check-in/out times while he’s out there making calls. Maybe even track expenses and miles. Meanwhile, as part of the nightly automation routine back in the office, the data he entered throughout the day gets imported into the main database and is removed from the Web App.
A not so excellent use case for this would be an office that has many people working remotely, at the level where these people require a “real” application to get work done, rather than a relatively simple web tool for smaller tasks. They’d need realtime information from the main database, extensive sets of features from the application, etc. Such cases would present two major problems with this approach: 1) robust applications with Access Web Apps are difficult, and 2) the data syncing between the office and the remote apps would be a nightmare. In this case, a cloud based BE or remote logic to the company network via RDP or VPN would be a much better solution.
When it comes down to it, there’s a number of ways to make your database web enabled – some more “enabled” than others, and each tends to have their strengths and weaknesses. By understanding what’s available, what the implications are in terms of functionality, cost, ease of implementation and the how well of a path forward it leaves, we can then make a good decision on where to invest our time and resources to get the best return for what’s required.
With any luck at all, this article has helped break down what to expect and provide some sort of general direction.