Microsoft Access 2013: A Cool New Rapid Development Tool for the Cloud

There are many frameworks for developers that are a good fit for Windows Azure SQL Database, and for that matter on-premises SQL Server 2012.  In addition to the .NET Framework with support for languages such as C# and VB.NET, new JDBC, PHP and Node.JS drivers for SQL Database and SQL Server 2012 offer Java, PHP and Node developers other great language options for building data-driven applications based on Windows Azure SQL Database.  In this post I want to make DBAs and others aware of a cool new way to rapidly build applications on SQL Database (or SQL Server 2012) without writing code.  And this tool is in fact part of the new Microsoft Office 2013 suite:  Microsoft Access 2013

[View:http://www.youtube.com/watch?feature=player_embedded&v=C-fBWarKkuk&noredirect=1]

With SharePoint Online (hosted in the cloud with an Office 365 subscription) or on-premise SharePoint Server 2013 you get the best collaboration for your organization or department. A big part of this is providing a central place (such as an internal Team Site) that’s scalable enough to organize and manage all your information assets, including your business applications such as those created in Microsoft Access.  Remember that in the previous version of Microsoft Access (MS Access 2010), when you created a web application on SharePoint, the tables in your database were stored as SharePoint lists on the site that housed the application. When you use Access 2013 to create a cloud application on SharePoint (via automatic Office 365 hosting), Access Services create either a SQL Server or Windows Azure SQL Database that houses all of your Access objects. This new architecture increases performance and scalability; it also opens up new opportunities for professional SQL developers to extend and work with the data in Access applications using other tools and frameworks.  

How it Works

When you create an application in Access 2013, you’ll choose a SharePoint site where you want it to live. Your application can be accessed, managed, or uninstalled from this site just like any other SharePoint application. In the process of creating your application in SharePoint, we provision a SQL Server database that will house all of the objects and data that your application requires. The tables, queries, macros, and forms are all stored in this database. Whenever anyone visits the app, enters data, or modifies the design, he’ll be interacting with this database behind the scenes. If you create an app in Office 365, the database is created in Windows Azure SQL Database. If you create an app on a SharePoint server that your company hosts, Access will create the database in the SQL Server 2012 installation that was selected by your SharePoint administrator. In either case, the database created is specific to your app and is not shared with other apps.

As you build your app, you can add tables, queries, views, and macros to deliver the functionality you and your users need. Here’s what happens in the database when you create each of these objects:

Tables

When you add table to your Access app, a SQL Server table is created in the database. This table has the same name you gave it in Access, as do the fields you create in the client. The data types that are used in the SQL Server database match the types you would expect: text fields use nvarchar; number fields use decimal, int or float; and image fields are stored as varbinary(MAX).

Consider the following table in Access:

 

The resulting table in SQL Server looks like this:

Queries

When you add a query to your app, Access creates a SQL Server view (or a table-valued function (TVF), if your query takes parameters). The name of the view or TVF matches the name you used in Access. We even use formatting rules when generating the T-SQL, so if you view the definition directly in SQL Server, it will be easy to understand. 

This is a query designed in Access:

 

It is stored as a formatted statement in SQL Server:

CREATEVIEW[Access].[MyQuery]
AS
SELECT
 
      
[MyTable].[ID],
       [MyTable].[String Field],
       [MyTable].[Date Field]
FROM 
      
[Access].[MyTable]
WHERE
       [MyTable].[Date Field]>DATEFROMPARTS(2012, 7, 16)

Data Macros

Data macros come in two flavors: event data macros and standalone macros.

You can create event data macros by opening a table in design view and clicking on any of the Events buttons in the Table ribbon.

Event data macros are implemented on SQL Server as AFTER triggers on the table to which they belong.

You can create a standalone macro from the Home ribbon by clicking the Advanced button in the Create section and choosing Data Macro from the list of items. This type of macro can take parameters and is persisted as a stored procedure in SQL Server.

Views

Views in Access 2013 are the parts of your app that display your data in the browser—database experts might call them forms. They are also stored in the database. Since they are HTML and JavaScript rather than SQL objects, they are stored as text in the Access system tables

SQL Server Schemas

Within the database, Access makes use of three separate SQL Server schemas: Access; AccessSystem; and AccessRuntime.

The AccessSystem schema contains system tables that store the definitions of each object in a format that Access Services understands, as well as bits and pieces of information that are necessary in order for the item to work well in the runtime or design time surface. 

The Access schema contains all of the tables, queries, and macros created by you, the app designer. Everything in this schema is the implementation of the objects you designed in SQL Server.

The AccessRuntime schema contains a number of items that we use in Access Services to optimize the runtime behavior of your application.

So What?

You might be wondering why these details are important. For some users, the only visible effect of the new SQL Server back-end will be increased speed and reliability. They don’t need to worry about the technical details. More advanced users, though, can directly connect to the SQL Server or Windows Azure SQL Database from outside of their Access app, which enables a whole new frontier of possibilities for advanced integration and extensions. This is big! 

To enable external connections, simply click on the File menu to go to the Backstage. Under the Connections section, you’ll find the SQL Server login credentials that you can use to connect to your database in SQL Server Management Studio, ASP.NET, or any other application that supports SQL Server.

 

The Manage connections button contains a number of commands that allow you to manage connections to the SQL Server database. You’ll find that you can generate a read-only login and a read-write login. Use the read-only login when you want to connect to the SQL Server database from a program or app that doesn’t need to modify the data, such as a reporting tool. Use the read-write login when you want to connect to the database and modify or enter new data. For example, you could create a public website in ASP.NET that allowed internet users to submit applications that get stored in your Access database.

SQL Rocks with Access

We are really excited about these changes to Access 2013 and we hope you are as well. Windows Azure SQL Database and SQL Server 2012 give Access 2013 a powerful data engine to house your data. In return, Access 2013 is now a valuable end-user tool to rapidly build native SQL Server and Windows Azure SQL Database applications. Together, they also enable many new scenarios for advanced integration and extension. We can’t wait to hear about the great new apps that you’ll build with Access on Windows Azure SQL Database.

    -  by Andrew Stegmaier and Gregory Leake