Access cache formats for SharePoint lists and document libraries

Summary

Both Microsoft Access 2010 and Microsoft Office Access 2007 make significant improvements over earlier versions in how they work together with linked Microsoft SharePoint lists and document libraries to deliver a better user experience.

Beginning in Access 2010, you can choose which caching format best suits your needs. To do this, you can adjust the Caching Web Service and SharePoint tables options in the File > Options > Current Database dialog base. Unless you're experiencing one of the known issues that are listed in the following sections, we recommend that you select the option to Use the cache format that is compatible with Microsoft Access 2010 and later.

Improvements in Access 2007

The following improvements in Access 2007 are major changes that increase SharePoint performance:

• Make asynchronous fetches of data from the server. These allow some scenarios to immediately show data before the complete recordset is returned from the server.

• Fetch incremental data changes to a list within an Access session. After the app fetches data in later requests, only query what has changed in the list.

• Cache lists in the local database as XML.

Asynchronous fetching

When data is loaded into a datasheet or form, the first page of data or a single record is often all that is required to let you begin working or scrolling through records. To speed up these scenarios, control is returned to Access as soon as the first batch of data is retrieved from the server. A background thread continues parsing and fetching the remaining data from the server. It blocks you from interacting with data only when the requested record is not yet available (for example, you press the Move to last record button immediately after you open the datasheet or form). Although this works in some scenarios, queries that have joins, sorts, and filters require that all the data be retrieved before you can access it.

Fetch incremental data changes

Although data must be fetched from the server when a link is first used within a session of Access, changes that were to the web service infrastructure enable the program to fetch incremental data changes. Access 2007 caches the data rowsets in memory after they are first opened. Therefore, later use of the SharePoint links become faster. In cases of large datasets, there is contention for RAM on the client computer. In this case, rowsets are released, and this frees RAM for the rest of the system.

Cache lists in XML

In addition to the in-memory caching, there are options to cache the XML within the database by using either offline mode or cached mode. This helps in cases in which the rowsets are recycled out of memory or when using the links in a new session of Access. This Is because some of the network time and server calls were eliminated.

Improvements in Access 2010

Just as in Access 2007, Access 2010 connects to SharePoint lists through web services over HTTP. When a user opens a query, form, or report that consumes data from a SharePoint link, web service requests are made to the SharePoint server to get the list data. The server returns the data in XML, and this is parsed and cached in local tables. Then, the data is fetched by the Access data engine and displayed to the user.

The following improvements in Access 2010 increase SharePoint performance:

• Cache data in local tables

• Improve bulk query operations

• Reduce web service calls

Cache data in local tables

Access 2010 enhances the connected SharePoint list experience by caching data in local tables that persist across Access sessions. This enables Access to parse the bulk of the SharePoint XML data only one time. Later use of the links fetch only incremental data changes from the server. An additional benefit of using local tables internally instead of the in-memory rowsets is that multi-valued lookups and value list use in a SharePoint list becomes much faster.

The first time that a link is opened, Access must still download all the data from SharePoint and parse it into the local tables. In some cases, all the data may be required immediately. For example, all the data must be read before you can move to the last record in the datasheet or run a query that has a join. In these cases, the time that is required to use the data is about the same as in Access 2007. The real-time savings occur the next time that the link is used. This occurs either after you start a new session of Access or if many SharePoint links are used within a session.

Using Access 2010 Cached mode

By default, the new cached mode is turned on for all new ACCDBs and published applications. You can have existing ACCDBs use the new cached mode by selecting the check box in the File > Options > Current Database dialog box.

Bulk query operations

When you export data to SharePoint, changes are typically sent in batches of 50 records at a time. Access 2010 extends this batching to bulk queries against SharePoint lists.

Reduce web service calls

When you use objects such as forms and subforms that contain multiple SharePoint links, only one web service call is required every time that you open a SharePoint link.

Known issues in Cache mode

Access 2010 Cache mode should not be selected if that database will be shared together with Access 2007 users. However, you can use Cache mode in a copy of the database that will be shared.

Access hangs when updating linked SharePoint list

Access: "Cannot update. Database or object is read-only" error in linked SharePoint list (about a hidden taxonomy list)

Access: "Cannot update. Database or object is read-only" error when executing update query against the linked SharePoint view (about lookup data that is missing)

Access: Troubleshooting errors opening linked SharePoint lists/document libraries

Synchronize a SharePoint 2010 list with Access 2010

Access: "Cannot update. Database or object is read-only" error when executing update query against the linked SharePoint list (because the record exceeds the MaxSize property)

You cannot download data from a SharePoint list to Access 2010

TransferSharePointList / ImportSharePointList Macro Action