Microsoft Access, LightSwitch and Visual Studio .NET Platform Comparison for Database Development

Comparison of Microsoft Access, LightSwitch and Visual Studio Platforms for Database Developers

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.

Matrix Comparison

Click for the detailed, complete matrix comparison of Microsoft Access, LightSwitch, and Visual Studio .NET
From simple to more sophisticated, Microsoft offers a wide range of options:

Microsoft Access

Desktop
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.

Access 2010 Web
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.

Access 2013 Web
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.

Some of the limitations include the reliance on the macro language which offers far fewer capabilities than VBA or .NET. Though JavaScript code is automatically generated, it is not possible to modify it. Because it's a web solution, it also cannot interact with desktop files or perform Office automation, though it can work with SharePoint lists. There also isn't a report generator, and the user interface is limited to what's offered.

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.

Visual Studio LightSwitch 2012

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.

Silverlight Dependency and HTML5

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.

The direction of LightSwitch is towards HTML5 which is supported by the latest browsers such as Windows 8. JavaScript is used to customize HTML5 applications.

LightSwitch 2012 Limitations

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.

Visual Studio .NET

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.

No Support for Native Applications on Mobile Devices

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.

Additional Papers