Skip to main content
Microsoft 365
Subscribe
4 min read

Access 2013 and SQL Server

By

This post was written by Russell Sinclair, a Program Manager on the Access Team.

Access 2013 web apps feature a new, deep integration with SQL Server and SQL Azure. In 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 web app on SharePoint, Access Services will create a SQL Server or SQL Azure database that houses all of your Access objects. This new architecture increases performance and scalability; it also opens up new opportunities for SQL developers to extend and work with the data in Access apps.

How it Works

When you create a web app in Access 2013, you’ll choose a SharePoint site where you want it to live. Your app can be accessed, managed, or uninstalled from this site just like any other SharePoint app. In the process of creating your app 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 SQL Azure. 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:

A table in Access 2013.

The resulting table in SQL Server looks like this:

The same table in SQL Server.

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:

A query in Access 2013.

It is stored as a formatted statement in SQL Server:

CREATE VIEW [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.

Events in Access 2013 get translated into AFTER triggers in SQL.

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 SQL Azure 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 backstage view of a database in Access will give you the information you need to connect to the back-end SQL database.

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.

Open (or close) connections to the back-end SQL database.

Please note, however, that this functionality is not currently available in the Office 365 Preview.

SQL Server Rocks

We are really excited about these changes to Access 2013 and we hope you are as well. SQL Azure and SQL Server give Access 2013 a powerful data engine to house your data. 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.