Microsoft Access 2013, 2010, 2007, 2003, 2002, 2000, and 97Microsoft Access, LightSwitch and Visual Studio Platform Comparison 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.


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

Microsoft Access Visual Studio LightSwitch
2012 Version
Visual Studio .NET
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          

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

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.

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.

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 Resources

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.

Blog 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!

Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS