Link to or import data from Microsoft Graph

You can import or link to data from Microsoft Graph, which is a data source that taps into a wealth of Office 365 information and relationships to gain valuable inferences and insights about individuals, groups, and organizations. With this data, you can increase the intelligence and productivity of your Access solutions. For more information, see Microsoft Graph.

  • When you link to data, Access creates a two-way connection that synchronizes changes to data in Access and Microsoft Graph.

  • When you import data, Access creates a one-time, copy of the data, and so changes to data in either Access or Microsoft Graph are not synchronized.

Overview of Access connecting to Microsoft Graph

This is a subscriber-only feature The ability to link to or import data from Microsoft Graph is only available to Office 365 Subscribers.

What is Microsoft Graph?

Microsoft Graph connects the dots between many Office 365 apps and services by collecting and organizing disparate data (users, groups, messages, contacts, conversations, tasks, files, and so on) into a set of relationships. Examples of relationships include: a user is a member of a group, the user's current manager, the user is mentioned in several conversations, or that user’s document was collaborated upon by several colleagues in the last few hours.

The word “graph” is a term used to denote a social network of information. Microsoft Graph is a network of social and data connections in Office 365. The value of any network grows exponentially with the number of connections. In Microsoft Graph, these connections are based on what you're working on, who you work with, and what’s important to you.

You can quickly get up-to-date machine learning insights from Microsoft Graph tables, perform further actions on them, and increase the smartness and productivity of your Access database solution. For example, you can find the most relevant people to a user and add them as email links to a report or send a report in email to a group the user is a member of.

Before you begin

Want things to go smoother? Then make the following preparations before you link or import:

  • Microsoft Graph data is based on your work or school account.The first time you use Microsoft Graph, you may need to sign in to Office 365.

  • Consider the number of columns in each table or view. Access does not support more than 255 fields in a table, so Access links or imports only the first 255 columns.

  • Determine the total amount of data being imported. The maximum size of an Access database is two gigabytes, minus the space needed for system objects. Microsoft Graph contains large tables, which may take a long time to load, and you might not be able to import them all into a single Access database. Use Microsoft Graph query parameters to minimize the amount of data linked.

  • Secure your Access database and the connection information it contains by using a trusted location and an Access database password. For more information, see Decide whether to trust a database and Encrypt a database by using a database password.

  • Plan for making additional relationships. Access links to or imports selected tables and filtered subsets in the Microsoft Graph data model. You may want to manually create additional relationships between new and existing tables by using the Relationships window. For more information, see What is the Relationships window? and Create, edit or delete a relationship.

Get started

  1. Select External Data > New Data Source > From Online Services > From Microsoft Graph.

  2. If prompted, sign in to your Office 365 work or school account.

  3. Do one of the following:

    • To import, select Import the source data into a new table in the current database.

    • To link, select Link the data source by creating a linked table.

  4. Select OK.

  5. In the Link Tables or Import Objects dialog box, under Tables, select each table that you want to link to or import from, and then click OK.

    A list of predefined filters

Filter link results by using a Microsoft Graph query parameter

Linking to and importing from Microsoft Graph may return a large amount of data, especially with Groups and Users in a large organization. When you link, consider using the following Microsoft Graph query parameters to filter the data on the server before retrieving the data:

  • $top    Finds a limited set of records based on a number, such as the first 25 rows.

    Note    We recommend as a best practice to first use the $top=n query parameter to return a small amount of data and then become familiar with the fields and values.

  • $filter    Filters the number of rows based on an expression.

  • $select   Returns only those columns that you specify. However, Access always returns the ID field.

Be aware that Access Microsoft Graph only supports a subset of expressions, syntax, and columns. For more information on what is supported and for the latest updates, see Use query parameters to customize responses, user resource type, and group resource type.

  1. In the Filter Results dialog box, enter a Microsoft Graph query parameter. The following examples show Groups and Users, but you can create similar filters for the other tables.

    $top=10
    $filter=securityEnabled eq false
    $filter=startswith(displayName, 'M')
    $filter=startswith(mailNickname, 'S')
    $filter=startswith(mail,'S') or startswith(mail,'T')
    $filter=startswith(mail, 'B') & $select=mail
    $select=mail,mailEnabled,mailNickname
    $select=classification,displayName
    $top=50
    $filter=startswith(givenName,'J') 
    $filter=startswith(givenName, 'B') & $top=3
    $filter=accountEnabled eq false
    $filter=startswith(jobTitle,'SENIOR')
    $filter=usageLocation eq 'GB' or usageLocation eq 'RU'
    $filter=userType eq 'Member'
    $select=displayName,mail,surname,postalCode
    $select=givenName,jobTitle,mobilePhone
  2. Select OK.

Results

When a link or import operation completes, the tables appear in the Navigation Pane with the same name as the Microsoft Graph table. During an import operation, if that name is already in use, Access appends "1" to the new table name. During a link operation, if that name is already in use, Access appends "1" to the new table name, but only if a different filter is applied. You can always rename the tables to something more meaningful.

The tables with a "My" prefix return data based on who is the current signed in user.

You can update My Outlook Contacts, but all other Microsoft Graph tables are read-only.

In an import operation, Access never overwrites a table in the database. Although you cannot directly append Microsoft Graph to an existing table, you can create an append query to append data after you have imported data from similar tables.

In a link operation, if columns are read-only in the Microsoft Graph table, they are also read-only in Access.

Tip    To see the Microsoft Graph URL including any query parameter, hover over the table in the Access navigation pane.

Modify an existing Microsoft Graph query parameter

If you have an existing linked table with a query parameter, you can change it.

  1. In the Navigation Pane, right-click the linked table, and then select Linked Table Manager.

  2. In the Linked Table Manager dialog box, click the plus sign (+) to expand the Microsoft Graph Data Source Name, and then select the check box next to the specific Data Source Name that you want to modify.

  3. Select Relink.

  4. Update the query parameter string in the Relink <Linked table name> dialog box.

    For example, change $top=100 to $filter=userType eq 'Member'.

    Make sure there is a question mark (?) character between the table name and the query parameter.

  5. Select OK.

  6. Select the check box next to the specific Data Source Name you modified.

  7. Select Refresh.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×