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

Andrew Steigmeier and Gregory Leake posted Microsoft Access 2013: A Cool New Rapid Development Tool for the Cloud to the Windows Azure blog on 11/6/2012:

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!

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

Microsoft Visual Studio LightSwitch for Microsoft Access, SQL Server, and Visual Studio .NET Database Developers

Luke Chung (@LukeChung) published a Microsoft Visual Studio LightSwitch for Microsoft Access, SQL Server, and Visual Studio .NET Database Developers to the FMS, Inc. blog:

The Visual Studio team has introduced a development platform called LightSwitch which simplifies the creation of database applications in Visual Studio. This rapid application development environment lets you create solutions that can be easily deployed on Windows or Mac platforms from a public web site or Intranet.

This article provides an overview of the benefits and limitations of the LightSwitch platform for the Microsoft Access community.

Sample Screens

Here are some examples of what can be created in LightSwitch.

Sample LightSwitch User Interface

Dashboards Built in LightSwitch

LightSwitch Integration with Other Programs

Customization Using Visual Studio .NET

While LightSwitch can be used to create database applications with limited coding, as part of the Microsoft Visual Studio .NET family, LightSwitch supports customization using C# and VB.NET programming languages. This offers all the benefits of managed code and the latest programming features.

LightSwitch does not support Office/Access VBA.

Direct Support for Microsoft SQL Server

LightSwitch works directly against SQL Server databases. It understands table structures, referential integrity, one-to-many relationships, and other database architecture so that it can bind directly to your table, fields, and records. It requires your database to be properly designed with primary keys and other basic requirements, so having a good database design helps (and should be done anyway).

It also supports databases hosted on SQL Azure, Microsoft's cloud provider.

Dramatically Reduced Development Time

LightSwitch provides the ability to deliver incredibly rich, intuitive and easy to use applications, all within a Windows, Mac or Browser client. It offers affordable, reliable, and scalable custom solutions with user-friendly views of your data. It dramatically decreases the time it takes to build and deliver the custom application compared to traditional Visual Studio .NET approaches.

LightSwitch allows the use of extensible application shells to provide users with the familiar feel of popular Microsoft software, significantly reducing learning curve and application adoption time.

Built-in authentication models simplify the management of users with varying degrees of access and authorization, especially when integrated with existing Active Directory implementations.

Requires Silverlight on the Client Machine

Visual Studio .NET and LightSwitch are used by the developer and are not installed on the users' machines.

However, LightSwitch applications require installing the free Microsoft’s Silverlight on each user's machine. This is a one time installation similar to installing Adobe Flash to watch videos or Adobe Acrobat Reader to open PDF files. Silverlight allows applications to be easily run on desktops and browsers through a one-click deployment, thereby dramatically reducing distribution and maintenance efforts. The Silverlight requirement makes a LightSwitch application inappropriate for general Internet solutions.

Unlike Microsoft Access database applications, you don't need to worry about what the user has installed on their Windows desktop, the version of Office/Access, and version conflicts on their machine. Unlike installing Office which usually requires physically updating each machine, Silverlight can be installed by the user from their browser.

Platforms Supported by LightSwitch

LightSwitch runs as a Windows or Mac client application, and supports multiple web browsers, including:

• Internet Explorer versions 7, 8, and 9 on Windows Vista and Windows 7
• Google Chrome version 12 or greater
• Mozilla Firefox version 3.6 or higher
• Apple Safari on Macintosh OS 10.5.7 (Intel-based) or higher

These browsers can run on 32 or 64-bit operating systems.

Silverlight Limitations

Does not Support iPad, iPhone, Android and Windows Phones

Silverlight is not supported on mobile platforms such as the iPad/iPhone, Android or Windows phone.

Silverlight Requirement Limits its Use for Public Websites

By requiring the installation of Silverlight, LightSwitch applications are not suited for public web sites where visitors may not have it installed on their machines (sites face the similar issue when using Flash). However, for internal users and close external contacts, this requirement may be perfectly acceptable.

Does not Support 64-bit Browsers

Silverlight is currently a 32-bit program that does not run in 64-bit browsers. This should not be an issue for most users. By default, on 64-bit PCs, the 32-bit version of Internet Explorer is installed and extra steps are required to intentionally install the 64-bit version, which will have problems supporting other common 32-bit components as well.

Additional Limitations

Limited User Interface Options

The LightSwitch architecture limits the user interface to its structure. We find the structure suitable for most database solutions but many Visual Studio .NET developers find the constraints (or potential constraints) too restrictive and uncomfortable in the long-term. There is definitely a tradeoff here, so it's important everyone understands the style of solution LightSwitch offers and are comfortable with it.

No Reports

LightSwitch doesn't offer reporting. You can display data in a list, but you can't get the nice reports with groupings, summaries, sub-reports, etc. that exist in Microsoft Access. With SQL Server, you can use its Reporting Services feature, but integrating it into a .NET application is not the same as Access where you can share the same variable space as the application. There are third party controls that can be added for reporting.

Future Directions

Microsoft has a preview version of an HTML5-based client that replaces Silverlight and offers true browser-based operation of LightSwitch applications (Microsoft announcement). We expect the HTML5 client to be available from Microsoft in 2013 to support mobile clients. However, the features are not the identical to the Silverlight platform which provides a richer end user environment and is simpler to develop.

Summary

With our experience building Microsoft Access and SQL Server solutions, we are very excited by the functionality and productivity LightSwitch offers for database application developers. LightSwitch fills a niche that allows the creation of web deployable SQL Server database solutions with .NET extensibility. It's ideal for solutions where the users are known either inside your organization or over the web. Microsoft Access remains a viable solution for end users, information workers, and applications that work on Windows.

Database Evolution

Visual Studio LightSwitch offers the Microsoft Access community the opportunity to extend their platform beyond the Windows desktop. It is the natural evolution of solutions which start in Excel, evolve to Microsoft Access, grow into SQL Server, and now to the Intranet and web. Compared to traditional Visual Studio .NET applications, the learning curve for LightSwitch is considerably shorter which means solutions that were either too expensive or took too long to build, can now be created profitably.

If you're interested in learning how our Professional Solutions Group can help you with Microsoft Access, LightSwitch, SQL Server, and/or Visual Studio .NET, please visit our LightSwitch Consulting page.

Luke’s whitepaper also appears in Windows Azure and Cloud Computing Posts for 10/22/2012+.

Technorati Tags: Visual Studio LightSwitch 2011,Visual Studio LightSwitch 2012,LightSwitch 2011,LightSwitch 2012,SilverLight,Visual Studio 2012,Microsoft Access 2010,Microsoft Access 2013,Windows Azure,SQL Server Express,Windows Azure SQL Database,SQL Azure

Link Access 2013 Web Apps to SharePoint Lists

Andrew Stegmeier posted Connect your Access 2013 Web Apps to SharePoint Lists by Lois Wang on 8/30/2012:

This post was written by Lois Wang, a Program Manager on the Access team.

Access 2013 web apps are great places to centralize your data. Whether you're tracking people, events, products or something else, storing data in an Access app allows you to easily collaborate with others while keeping things organized.

Sometimes, though, the stuff you care about is already stored somewhere else. Although you could import the data into Access, those external sources may be maintained by other people or processes. In these cases, you want to make sure that as these sources are updated, you're always seeing the latest version in Access. Wouldn't it be great if you could simply link to these data?

Access 2013 makes this easy. The web apps you create with Access 2013 can connect to and display real-time data from SharePoint lists. That way, you can easily supplement or combine external data sources with the things that your app uniquely tracks.

Imagine a scenario where a small business owner named Ryan is trying to manage a party planning company. He and his five employees use Office 365 for sharing information. His accountant manages all the suppliers of his business in a SharePoint list. Ryan has built an Access 2013 web app to manage all the parties that he is in charge of planning. He wants to pull supplier information into his app, but he doesn't want to have to worry about manually keeping his app in sync with the accountants list. How can he do that?

He opens up his Access 2013 web app in the Access designer and clicks the Create Table button in the ribbon. Then, under the heading "Create a table from an existing data source," he chooses "SharePoint List."

The next step is to provide the URL of the SharePoint site where the PartySuppliers list lives. Since he wants to link to rather than import his data, Ryan selects "Link to the data source by creating a linked table."

Access will go fetch the names of the lists on that site, and Ryan selects the one he wants—the PartySuppliers list. In order for Ryan set up this link, his account's permission level needs to be "Full Control" for the PartySuppliers list in SharePoint.

Now, the supplier data show up in Ryan's project management app. Access automatically creates a List view and a Datasheet view for displaying the suppliers. It looks and feels just like the rest of his Access web app.

Ryan can further integrate this SharePoint list into his app by adding a lookup field in his Events table to show which PartySupplier is working on which Event. Even though the PartySuppliers "table" is actually stored externally, setting up this relationship works exactly as it would if the data were stored in a local table.

Two things are worth noting about this external data feature in Access 2013 web apps. First, Access currently only supports read-only connections to SharePoint lists. In our example, that would mean that in order to change information about a supplier, Ryan would have to edit the SharePoint list directly.

Second, in order to set up a connection to an external List, your user account has to be allowed to change permissions to the List. This is because when you set up the connection, you need to give the Access web app itself the right to read the data. The right to grant other accounts or apps access to a List is usually included with the "Full Control" or "Owner" SharePoint permission groups. If you have trouble, check with the person who is in charge of your SharePoint site.

Access 2013 web apps can easily integrate with external SharePoint lists. You can try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

Access 2010’s Web Databases linked only to SharePoint lists, which was a drag. Inability to update SharePoint lists with Access is equally disappointing.

Will the real SharePoint logo please raise its hand?

---

Technorati Tags: Microsoft Access 2013,Access 2013,Access Web Apps,SharePoint 2013,SharePoint,SharePoint Lists,Access Linked Databases

Four Ways to Create Business Apps with Access 2013

Andrew Stegmeier posted 4 Ways to Create Business Apps with Access 2013 on 8/20/2012:

There are many ways to build useful apps to run your business with Access 2013. You can download an app via the Office Store, build an app using a Web App Template, or create a Custom Web App. You also have the ability to make a desktop database, just like you always have in Access. Each method offers a fast and easy way to get started. Several members of the Access team explain more in the video below:

1 - Use the Office Store

Getting an app from the Office Store is a quick way to start using a database that's been professionally designed for a specific purpose. To use the store, you'll need to sign up for the Office 365 preview. (Be sure to choose one of the plans for business: Small Business Premium or Enterprise). After you've logged into your account, click the elipsis (...) in the upper-right-hand corner of the screen, then click "Office Store." You can easily search for apps related to your specific business. You'll find a wide array of SharePoint apps available—the ones that include the Access 2013 logo are Access 2013 web apps. After you find the app you want, you can click the Add button to install it directly to your SharePoint site and start using it right away.

If you want, you can customize the app with Access 2013. For example, you could easily add a field to a table to track some information that's specific to your business. You could even add new tables and new relationships to track additional types of things. When you install an Access app from the Office store, you can get up and running fast, but there's still plenty of room to grow.

2 - Use Web App Templates

You can also make web apps directly from Access 2013. The easiest way to do this is by clicking on one of the web app template tiles that you see right after opening Access. You can search for additional templates if you don't see what you're looking for. These templates are fully-functional web databases, but instead of immediately seeing them in action, you're taken straight to the customization step. In our case, you can select "Task management" (outlined in blue).

Once you've selected your template, give it a name and pick a location. If you're logged into your Office 365 preview account (see above), you will see two available locations by default. The first is for personal apps that you don't want to share with others. The second is for your company's default SharePoint team site. This is a great place to put an app that you want other people in your organization to be able to use.

When you click Create, Access makes an app in the location you selected. The template you selected will include tables, relationships, and views that make it useful from the start. You can start adding data immediately by clicking Launch App in the ribbon. Or, you can customize the app in Access to your heart's content.

3 - Build a Custom Web App

You can also build an app by starting from scratch. You can create custom tables and fields for your precise needs or search and add tables from our library of Table Templates. To build a custom web app, open Access 2013 and click on "Custom web app" (highlighted in blue). Just like with Web App Templates, you'll need to give your app a name and a location.

You'll be initially given a blank database with no tables or schema. The easiest way to start building your app from here is to use table templates. To search for a table template related to what you'd like to do, simply type something in the search box and hit enter. You'll see a list of search results from the library. For example, if you type "Orders," then click on the Orders table template from the results, you'll find that a bunch of useful stuff related to tracking Orders has been added to your blank database.

Each template includes all the tables, fields and views you'll need to create a working app. Some templates come along with other, related tables. For example, the Orders template also gives you a place to track related Customers, Employees, Products, Suppliers, and Categories.

Access includes a large searchable library of these templates. Whether you want to manage products, employees, customers, projects, tasks, or ideas, it's likely you'll find a template that's tailored to your needs.

4 - Build a Desktop Database

If you don't have Office 365 or SharePoint, you won't be able to create web apps (options 1-3 above). You can, however, create desktop databases that run on your machine. If you've used previous versions of Access, these are the databases you're already familiar with, and you'll find it easy to work with files you've created in the past. Desktop databases have all the powerful features, such as VBA, that has made Access such a popular way to run a business.

To create a new desktop database, open up Access and click on any tile that uses the word "Desktop" such as the "Blank desktop database" or "Desktop asset tracking" (highlighted in blue). Just like with web databases, you can start from scratch or choose from a large library of templates. Once you've selected the template you want, give it a name and click create to start editing.

When Access is finished downloading your template, you'll be presented with the familiar, rich database building experience you've come to expect from Access, complete with reporting, advanced forms, and VBA.

Conclusion

No matter which method you choose—the Office store, Web App Templates, custom Web Apps, or Desktop Databases—Access 2013 will help you get started quickly. Try it out today by signing up for the Office 365 Preview. If you'd like to take advantage of Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

---

Technorati Tags: Microsoft Access 2013,Access 2013,Access 2013 Web Apps,SharePoint 2013 Access Services,SharePoint 2013

Access 2013 Data Entry Made Easy

Andrew Stegmeier posted Data Entry Made Easy on 8/13/2012:

This post was written by Erik Kennedy, a Program Manager on the Access team.

In Access 2013, there are lots of improvements to help you quickly make a great user interface for your web databases. With this interface, the people who use your app will have easy time entering data.

There's two ways we've gone about doing this:

1. Automatically generating two views based on your data.
2. Generating special controls for entering related data—the related items control and the autocomplete control.

Automatically Created Views

The basic unit of an Access app is a table. When you describe an app by what it tracks—"I want to track appointments" or "I want to track payments and invoices"—you're talking about tables. In this case, a table for Appointments, Payments, or Invoices.

Access 2013 does something really handy with all of your tables. Whenever you create a new table (or make certain updates to an existing one), it will automatically create (or update) two views for you. A view is the screen in the browser that you see when you navigate to the app—it's through views that you interact with your data. Access 2013 gives you the power to create entirely customized views, but to get you started quickly, it will automatically generate a List view and a Datasheet view.

This is a List view. It functions a lot like a fill-out form you may see on any other website.

This is the Datasheet view. It looks and works like an Excel spreadsheet.

Both the List view and the Datasheet view allow you to edit the data of your app. Whether you want to add new data, or edit or delete existing data, there's a way to do it in the view.

You can create different types of views besides these two, and you can even create duplicates of the same view, but with different data sources. For instance, you could have a List called "All Employees" and a list of "Current Employees".

But I'm getting ahead of myself. The point is that these views are automatically generated for you whenever you create a new table. And if you update the table in the Table Designer, as long as you haven't edited the view in the View Designer, your view will be updated automatically.

For instance, if you want to add a Birthday field to your Customers table to track the birthday of each customer, you can make the change in the Table Designer...

...and your View will be updated with the field you added when you click "Refresh" in the browser!

Hotkeys and the Action Bar

You'll notice at the top of the List view is a row of buttons. This is called the Action Bar, and it includes a number of commonly used commands that are useful in interacting with the data in your app.

For the List View, these actions are provided automatically:

• Add item
• Delete item
• Edit item
• Save item
• Cancel item

These few functions help get an app off the ground quickly, so you can start using it right away. If your scenario requires advanced customization or business logic, you can focus your time designing for what's unique about your situation.

Because these functions are so common, we've also assigned some hotkeys. This way you can quickly move around the app without switching back and forth between the keyboard and the mouse. Here are the hotkeys for each of the actions:

• Add: N
• Delete: <Delete> key
• Edit: E
• Save: Ctrl + S
• Cancel: Esc

You'll also notice that you can tab between links and controls in your app. Keyboard shortcuts will help make it easy and fast to interact with the data in your Access 2013 app!

Automatically Generated Controls for Related Data

Access is particularly powerful in tracking relational data—i.e. where two tables are connected. For instance, if you track Employees and Tasks, you'll likely want to connect those two tables so that each Task has an Employee assigned to it, and each Employee can have multiple tasks.

To relate two tables like that, you create a lookup from one table to another. A lookup is a kind of field in one table that can display data from another, related, table. So for the Tasks/Employees example, you would create a lookup field called "Owner" field in Tasks that displays the appropriate employee's name or alias.

Below, we're using the Table Designer to look at our Tasks table. You can see there's a lookup here to Employees, and that lookup is what makes the two tables related.

Whenever you create a lookup between two tables, Access 2013 will automatically create some special controls for you so that it's easy to enter and view data for those tables.

The two special controls for related tables are:

1. The related items control
2. The autocomplete control

The Related Items Control

In the Tasks/Employees example, we said that each Employee could have multiple tasks assigned to him or her. Wouldn't it be nice if you could see all of an employee's tasks just by looking at a view for that employee? That's what the related items control does.

If you navigate to a record in one table—in this case, Employees—you'll see data from a related table—in this case, all his related Tasks—thanks to the related items control.

Any time you create a relationship between two tables, Access 2013 will try to generate a related items control so you can see the related data easily.

This isn't all, though—if you click any of the items in the related items control, you will see a popup which presents all the details for that item. Want details on a specific task? Simply click on it.

The Autocomplete Control

The autocomplete control is generated in similar cases as the related items control, but it appears on the table in the relationship that doesn't have a related items control.

For the Tasks/Employees example, that means we'll see an autocomplete control on the Tasks table. If you navigate to a Task and look under owner, you'll notice it looks like a link.

When you click the link, a popup launches with all the details for that item.

If you want to change the ownership of a task, the autocomplete control helps you find the right record in the related table. I simply start typing the name of another person into the autocomplete control, and I'm presented with a dropdown of the search results. When I've found the person I was looking for, I just hit enter.

The autocomplete control and the related items control make it easy to deal with related data. Combined with the List view and Datasheet view, Access 2013 makes it easy to view and edit data. Perhaps most importantly, all of this functionality is available automatically as soon as you create the tables that represent the things you want to track. When you're creating an app, you can focus on designing what's specific and unique about your scenario instead of rebuilding basic functionality every time.

Access 2013 is the most powerful tool around for quickly making a web app for your department, organization, or business—in part because it's easy and quick to enter and edit data.

---

Technorati Tags: Microsoft Access 2013,Access 2013,Related Items Control,Autocomplete Control,Automatically Created Views

Access 2013 Web Apps and SQL Server Back Ends

Andrew Stegmeier of the Microsoft Access team posted a described of the relationship between Access 2013 and SQL Server/SQL Azure on 8/8/2012:

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:

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:

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.

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

Please note, however, that this functionality is not currently available in the Office 365 Preview. If you'd like to try it out, though, you can download the Microsoft SharePoint Server 2013 Preview and set it up on your own servers.

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.

What’s not clear from Russ’ article is how do SQL Server and Windows Azure SQL Database (formerly SQL Azure) handle multi-valued list boxes, which require backing by SharePoint lists in Access 2010.

---

Technorati Tags: Microsoft Access 2013,Access 2013,Access 2013 Web Apps,Access Web Apps,Windows Azure SQL Database,WADB,SQL Azure,SharePoint 2013 Access Services,SharePoint 2013,SharePoint