Last modified: July 18, 2014

In this article 
Prerequisites 
Create an app for Access based on the Visual Studio template 
Examine the Home.js file 
Add global variables to contain data that is used across all functions 
Create or detect the binding to the target data 
Using the Bing map control 
Additional resources 

This article contains preliminary information, and will be updated in MSDN when the documentation is finalized. A link will be added here when that documentation is available.

This article is based on the apps for Office and SharePoint sample BingRoute, which uses the Bing map REST service to calculate the distance between two geographical points. You can find the sample on the Apps for Office and SharePoint samples page, or at the download location:, http://code.msdn.microsoft.com/Office-365-Bind-and-4876274e. The idea for this app came from an original Access app called Mile Tracker that was included with SharePoint Online. This app enabled a user to record miles driven for billing or tax reasons. In the app the user would record odometer readings for the start and end of a trip, and store that information and a calculated distance traveled in an associated database. In practice the app was not very usable - it was unreasonable to expect the user to remember those specific numbers after the trip. This sample simplifies the process by letting the user enter addresses or landmarks, and uses Bing to calculate a route and a driving distance.

This sample only uses the Bing service to calculate route - it would be an interesting exercise to add code to display the calculated route on a map. Most of the code used to communicate with the Bing control comes with little modification from the Bing map REST service documentation page, and you can look there for more information about the service.

Note

This article does not go into the mechanics of creating an app for Office, or using it in a SharePoint Access app. The topic How to: Create a content app for Access has detailed information on this and this article assumes that you understand that information and are now ready to add code to communicate with an Access database.

Prerequisites

To write an app for Office targeting Access you need:

Note

This app for Office requires SharePoint Online. The Microsoft Access 2013 desktop client doesn't support apps for Office.

Apps for Office targeting Access are supported in version 1.1 of Office.js.

Create an app for Access based on the Visual Studio template

When you install the Microsoft Office Developer Tools for Visual Studio 2013 (March 2014 update) and Microsoft Visual Studio 2013, you can use Visual Studio to create an app for Office targeting Access. This procedure is described in How to: Create a content app for Access. Follow the steps in that topic to create and run an Access content app based on the Basic template, and then return to this topic to learn what steps to take to make your app talk to an Access database. The following steps add features to the basic app template to bind to data in the Access app, and communicate with the Bing REST Service.

Examine the Home.js file

to create the app you will modify the Home.js file to add functionality. Open the project in Microsoft Visual Studio 2013, and examine the file as it comes in the basic template project.

   
/// <reference path="../App.js" />

(function () {
    "use strict";

    // The initialize function must be run each time a new page is loaded
    Office.initialize = function (reason) {
        $(document).ready(function () {
            app.initialize();

            $('#get-data-from-selection').click(getDataFromSelection);
        });
    };

    // Reads data from current document selection and displays a notification
    function getDataFromSelection() {
        if (Office.context.document.getSelectedDataAsync) {
            Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
                function (result) {
                    if (result.status === Office.AsyncResultStatus.Succeeded) {
                        app.showNotification('The selected text is:', '"' + result.value + '"');
                    } else {
                        app.showNotification('Error:', result.error.message);
                    }
                }
            );
        } else {
            app.showNotification('Error:', 'Reading selection data is not supported by this host application.');
        }
    }
})();
What do the functions in Home.js do?

Function name

Function behavior

Office.initialize

The initialize function is run on initialization, on start, on page refresh and on insertion.

getDataFromSelection

This function is called when the button with the id "get-data-from-selection" is clicked. It gets data from the selection in the SharePoint OnlineAccess app that hosts the app for Office, and displays that data in the app for Office. This function is not used in the bingroute app, and will be replaced with new functions as you progress through this article.

In general terms, you will add code to do the following:

  • Connect (bind) to the data in the Access app that will host the app for Office.

  • Listen for events from the binding when the data in the Access app changes, and react to those changes.

  • Write data back to the users form when the user selects the appropriate button.

 

Add global variables to contain data that is used across all functions

At the very top if the Home.js file add the following global variables. These values pass information between functions and between the application code and the UI. All the variables are pretty self-explanatory, other than the BindingName. This is a value that is used to identify the specific binding when it is saved as part of the Access app.

   
//Variable initialization
var BindingName = "RouteBinding";
var ToAddress = "";
var FromAddress = "";
var map = null;
var PostField = "";
var MilesTraveled = 0;

Create or detect the binding to the target data

Apps for Office and SharePoint that target Access are used to manipulate and visualize data in an Access database hosted in SharePoint Online. to connect the app with the data in the Access database, the data must be bound to variables in the app. These are set in the Office.initialize function that is called in response to the Office.initialize event. A stub for this function is provided in the template that you examined. You will want to modify the stub to include the following code. This code checks for an existing binding, and if a binding with the correct name exists the app registers handlers for binding messages and updates the data. If the binding does not exist, the code creates a new binding.

A binding is simply a named pointer to data in the host application. Once created, a binding is saved as part of the host application (form), and is available for all users. In this way end-users do not need to go through the binding process every time. Since the binding may already have been created in a previous session, you must check for it first.

Access supports table binding, which lets you read and write data to the table or query in the database. All you need to do is write data to the binding and the host maps the information to the table.The binding also provides events when the data or the selection in the table changes.

You can use three styles of binding:

  • Bind by name if you know the name of the table

  • Bind by selection

  • Bind by prompt

This sample uses the third option, bind by prompt. This prompts to the user with a dialog which contains some example data, and lists available data found in the database. The user browses the available data fields and selects the data that most closely resembles the example data. When all appropriate fields have been filled in the dialog box is closed and the binding is created. Even if a binding is already present, it is a good idea to add UI that lets the user rebind the data to allow different values to be used.

In either case (creating or re-using a binding) handlers must be registered.

   
Office.initialize = function (reason) {
    // This async method will check to see if a binding already exists. 
    Office.context.document.bindings.getByIdAsync(BindingName, function (callback) {
        //check the callback.status field to see if the call succeeded. If the callback succeeded we know a binding already exists
        if (callback.status == Office.AsyncResultStatus.Succeeded) {
            //We have the binding so call functions to register handlers and update the map
            RegisterHandlers(callback.value);
            RecalculateDistance();
        }
        else {
            //We don't have a binding yet so call functions to create a binding
            BindToData();
        }
    });
    //Register button events
    $("#btnBind").click(BindToData);
    $("#setdistance").click(SetDistance);
};

At the end of the function two button click events are registered to connect the user interface to functions that will be created in the next section. The UI is very simple and will not be described in this article. To save typing, use the UI code from the sample application, as well as a bitmap of the Bing logo that is used in the UI.

There are several functions that are mentioned but not defined in the initialize function:

  • BindToData

  • RegisterHandlers

  • RecalculateDistance

  • SetDistance

BindToData is called if the binding does not exist, or if the user wants to change the binding. This function manages the process of binding by prompt. It creates the example data mentioned before, and callsOffice.context.document.bindings.addFromPromptAsync and creates the new binding when the user has entered the appropriate values. This function also calls the function RegisterHandlers.

   
//Show the binding prompt with sample text and bind to the data the user selects
function BindToData() {
    //TableData to hold the sample data
    var sampleDataTable = new Office.TableData();
    //Sample headers
    sampleDataTable.headers = [["From Address", "To Address", "Distance Field"]];
    //Sample data rows
    sampleDataTable.rows = [["White House", "Seattle", "2716.2"], ["Houston","Dallas","293.3"], ["400 Broad St., Seattle, WA", "1 Microsoft Way, Redmond WA", "13.3"]];

    //Show the binding promp with the sample data and create a binding with id:BindingName
    Office.context.document.bindings.addFromPromptAsync(Office.BindingType.Table, {
        id: BindingName,
        sampleData: sampleDataTable
    }, function (bindingCallback) {
        //Check if the callback succeeded
        if (bindingCallback.status == Office.AsyncResultStatus.Succeeded) {
            //Register handlers for events
            RegisterHandlers(bindingCallback.value)
        }
    });
}

The RegisterHandlers function does two things - first it adds handlers for two messages that are generated by the binding: BindingSelectionChanged, and BindingDataChanged. In both cases the handler will callRecalculateDistance. The second thing the function does is to get the appropriate table header value to post data to, and stores that in the string global variable PostField. This value can then be used to update the distance value every time that the route changes.

   
//Register handlers for events
function RegisterHandlers(binding) {
    //Map Binding Selection Changed event to the HandleRecordChange function
    binding.addHandlerAsync(Office.EventType.BindingSelectionChanged, RecalculateDistance);
    //Map Binding Data Changed event to the HandleRecordChange function
    binding.addHandlerAsync(Office.EventType.BindingDataChanged, RecalculateDistance);

    //Get the name of the field to set data into. This is the third field in the binding
    Office.select("bindings#" + BindingName).getDataAsync({
        coercionType: Office.CoercionType.Table,
        //Get the data for the current row
        rows: "thisRow"
    }, function (callback) {
        //Check if the call succeeded
        if (callback.status == Office.AsyncResultStatus.Succeeded) {
            //Store the name(header) of the field
            PostField = callback.value.headers[0][2];
        }
    });
}

 

Using the Bing map control

RecalculateDistance is the heart of the sample. It requests and stores the two addresses that the Bing service needs to calculate the route and the route distance. The actual call to the Bing service is delegated to the CallBingMapsService function at the bottom of this function.

   
//Recalulate distance with current row's data. To be called on app initial load, selectionchanged or datachanged
function RecalculateDistance() {
    //Get the To and From address from the current row
    Office.select("bindings#" + BindingName).getDataAsync({
        coercionType: Office.CoercionType.Table,
        //Specify to get the data from the currently selected data row
        rows: "thisRow"
    }, function (callback) {
        // Check to see if the function was successful
        if (callback.status == Office.AsyncResultStatus.Succeeded) {
            //Store the from address
            FromAddress = callback.value.rows[0][0];
            //Store the to address
            ToAddress = callback.value.rows[0][1];
            //Update the UI to reflect the new data
            $("#fromaddress").text(FromAddress);
            $("#toaddress").text(ToAddress);
            //Call the Bing service
            CallBingMapsService();
        }
    });
}

The CallBingMapsService and the MakeRouteRequest functions encapsulate the specific code needed to talk to the Bing service. Note the 'X's in the credentials parameter. This is where you must insert your own credentials, as described in the Prerequisites section earlier in this article.

   
function CallBingMapsService() {
    //Create new Microsoft Maps object
    map = new Microsoft.Maps.Map(document.getElementById("mapDiv"), { credentials: "XXXXXXXXXXXXXX-XXXXXXXXXXXXX_XXXXXXXXXX_XXXXXXX", mapTypeId: Microsoft.Maps.MapTypeId.r });
    map.getCredentials(MakeRouteRequest);
}

Send the REST request to the Bing service by using the helper function CallRestService.

   
function MakeRouteRequest(credentials) {
    //Send request to bing. Specify the to and from Address and the callback function
    var routeRequest = "https://dev.virtualearth.net/REST/v1/Routes?wp.0=" + FromAddress + "&wp.1=" + ToAddress + "&routePathOutput=Points&output=json&jsonp=RouteCallback&key=" + credentials;

    CallRestService(routeRequest);
}

This function is called by the Bing service and handles the return values from the service request. Note that the travel distance is returned in kilometers and is converted to miles using the constant 6.2137119.

   
//To called by Bing Maps Response
function RouteCallback(result) {
    //Check to see if all the data we need exists
    if (result &&
          result.resourceSets &&
          result.resourceSets.length > 0 &&
          result.resourceSets[0].resources &&
          result.resourceSets[0].resources.length > 0) {

        //Store the distance between addresses
        MilesTraveled = Math.round(result.resourceSets[0].resources[0].travelDistance * 6.2137119) / 10;
        //Update the Apps for Office UI
        $("#distance").text(MilesTraveled);
    }
}

And finally the distance value is posted to the appropriate location in the table in the Access app usingPostField.

   
function SetDistance(eventArgs) {
    if (MilesTraveled !== 0) {
        //Set data in the host
        Office.select("bindings#" + BindingName).setDataAsync([[MilesTraveled]], {
            //On the current row
            rows: "thisRow",
            //In the PostField
            columns: [PostField]
        }, function (callback) {
            //Set Data Callback
        });
    }
}

A convenient helper function for making REST calls.

   
//Function to call rest service by injecting script tag
function CallRestService(request) {
    var script = document.createElement("script");
    script.setAttribute("type", "text/javascript");
    script.setAttribute("src", request);
    document.body.appendChild(script);
}

All apps for Office work in similar ways, and you can find additional information in the links at the end of this section.

Additional resources