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