Microsoft Access started at the beginning of the Windows revolution 20+ years ago and became the most popular database of all time. More recently, additional technologies have become significant, so it behooves the Microsoft Access community to be aware of the trends and options.
Ultimately, it's about being able to create solutions that help you and/or your users accomplish their mission. Sometimes the user's platform is critical, sometimes, it's the data source, and other times it's the permissions you have to deploy a solution. A variety of platforms and options are available with benefits and limitations with each. Meanwhile, Microsoft Access is also evolving with their latest Access 2013 version offering new web based solutions.
Here is a summary of what we're seeing and experiencing:
|Feature||Access 2010 Desktop||Access 2013 Desktop||Access 2013 Web||Silverlight||HTML5||Windows EXE||Web ASPX|
|Solutions can be created for these platforms|
|Native (disconnected) Mobile App|
|Platforms and Permissions Required for Deployment|
|File Server (LAN)|
|Separate versions for development vs. deployment|
|Supports Microsoft Access / Jet (DAO)|
|Supports SQL Server|
|Requires SQL Server||Included in SharePoint|
|Requires Referential Integrity|
|Can use existing SQL Server DB (without database changes)|
|Can Execute SQL Server Stored Procedures from Client|
|Connect to Multiple SQL Server Databases|
|Connect to Tables in Access and SQL Server Databases|
|Connect to Tables in Oracle or other ODBC data sources|
|Local PC Issues|
|Required to be installed||MS Access||MS Access||Browser||Silverlight||Browser with HTML5 support||.NET Framework||Browser|
|Able to run as a local application|
|Rich user interface with grids and data entry|
|Can use local files and/or Office integration|
|User interface options||minimal||minimal||minimal|
|Display records as tiles that resize for mobile devices|
|Use Shared Libraries|
|Create a Shared Library|
|Offers Add-in Programs|
|Call Web Service|
|Call Windows DLLs|
|Ease of Form Design (Screens)|
|Create single table data entry forms without programming|
|Create single screen one-to-many forms without programming|
|Copy an existing form|
|Share form sections(subforms) across multiple forms|
|Programming and Ease of Development|
|Supports Source Code Version Control||pending|
|Usable by non-developers||possible||possible|
|Microsoft Access Issues|
|Supports Access Data Projects (ADPs) for direct connection to SQL Server databases|
From simple to more sophisticated, Microsoft offers a wide range of options:
Microsoft Access (desktop) lets you create database applications that can be run on a PC and easily shared across a network or passed to another person with a file transfer. Each user needs to have the right version of Access installed. Access databases can have data within itself or link to a backend database which can be another Access database on the network or a SQL Server database. With ADO, it supports the ability to execute SQL Server features such as stored procedures. It can work with files on the PC and automate other Windows programs such as Word and Excel.
Microsoft Access 2010 has the ability to create hybrid desktop and web solutions. Hosted on SharePoint 2010, the web solutions support macro driven forms but not VBA code. It stores its data in SharePoint lists. While SharePoint simplifies the deployment process, SharePoint lists do not offer the features of Access Jet databases. For instance, referential integrity and advanced queries are not available. Access 2010 offers basic displaying and editing of data without the need to install a copy of Access on each user's machine.
Microsoft Access 2013 web solutions let you create browser based sites that people with rights to your SharePoint 2013 server can run without having to install anything on their machine. Deployment is very easy and is ideal for information workers who would normally not have admin rights or the knowledge of deploying web applications on an IIS server. These Access web apps support any browser that SharePoint supports including Windows, Mac, and mobile devices. Though running in SharePoint, the data is stored in SQL Server (part of SharePoint) which is not the case for Access 2010. Office365 includes SharePoint 2013 which stores its data in SQL Azure.
A significant concern for this platform is the inability to separate the application from the database. That means modifications are made on the production platform. It also cannot be pointed to an existing SQL Server (or Access) database, so it's not appropriate for adding functionality to an existing database.
LightSwitch is a subset of Visual Studio to create deployable database solutions using SQL Server for Windows, Mac, web, and mobile users. The primary focus is for basic database features (search, add, edit, delete) and export to Excel. These solutions are based on Silverlight or HTML5 technology.
Note that LightSwitch can create web solutions with an OData interface for a web service.
Silverlight solutions are rich Windows like solutions which support Windows and the Mac (but not iPad or mobile). It requires each user to install Silverlight on their machine (similar to installing Flash or Adobe Acrobat), which generally limits its use to internal users or customers willing to add this. However, Silverlight is no longer being enhanced by Microsoft.
While customization can be achieved by adding .NET code in C# and VB.NET, there are limited options for customizing the LightSwitch user interface and extending the database functionality beyond what is offered. The inability of the client tier to execute stored procedures without hacks means it is appropriate for only basic SQL Server solutions (compared to what most Microsoft Access databases linked to SQL Server require).
From our experience, support for multiple developers on a project is weak and corruption prone. LightSwitch offers an EXE distribution for local installation. For web deployment, the developer needs rights to a web server host.
A pure Visual Studio .NET solution offers the ability to create a Windows EXE file that can run on a PC or a web hosted ASP solution. A great deal of customization is available for the user interface and internal code. A professional developer is usually required with experience connecting to databases via MVC, familiarity with JQuery or other database platforms. Multiple developer support via Team Foundation Server (TFS) and Subversion are solid.
None of the platforms listed offers the ability to create an application once and run it on a device like an Apple iPhone, iPad, or Android device, or Windows Phone when it's disconnected. For those devices, native applications still need to be created for those disconnected, non-browser situations though HTML5 offers the future promise of local storage that can reconnect when connectivity is restored.
Note that not all mobile browsers support HTML5. Even Windows Phones before Windows 8 do not support HTML5 solutions.
This paper has not reviewed LightSwitch 2013. Microsoft just announced the release candidate of LightSwitch in Visual Studio 2013. We know some of the limitations we cite are addressed in the new version, so that's promising. We'll update this paper when we gain more real world experience with it. Meanwhile, please let us know what you think.
Discuss this topic on our blog.