Microsoft Access, LightSwitch and Visual Studio Platform Comparison for Database Developers

Here is a summary of what we're seeing and experiencing:

				2012 Version
Feature	Access 2010 Desktop	Access 2013 Desktop	Access 2013 Web	Silverlight	HTML5	Windows EXE	Web ASPX
Solutions can be created for these platforms
Windows			Browser		Browser		Browser
Mac
iPad
Mobile
Web Service
Native (disconnected) Mobile App
Platforms and Permissions Required for Deployment
File Server (LAN)
SharePoint 2013
Web Server
Separate versions for development vs. deployment
Backend Database
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
Solutions
Rich user interface with grids and data entry
Can use local files and/or Office integration
Report generator
User interface options			minimal	minimal	minimal
Display records as tiles that resize for mobile devices
Extensibility
Use Shared Libraries
Create a Shared Library
Offers Add-in Programs
Call Web Service
Call Windows DLLs
ActiveX/COM Controls
.NET Controls
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
Programming Language	VBA	VBA	Macros	.NET	.NET, JavaScript	.NET	.NET
Programmer Control			minimal	limited	limited
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

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.

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

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.

• Microsoft Access to SQL Server Upsizing Center
• Paper: Microsoft Visual Studio LightSwitch for Microsoft Access, SQL Server, and Visual Studio .NET Database Developers
• Visual Studio LightSwitch Consulting Services

LightSwitch 2013

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.

Have any suggestions or comments? Head to our blog post Comparison of Microsoft Access, LightSwitch and Visual Studio Platforms for Database Developers and leave us your feedback!