Go to Top

How to work offline with SharePoint and Access 2010

One advantage a SharePoint list has over any ODBC data source is that Access can disconnect and re-connect and synchronize the local edits back to the server fairly seamlessly. It also provides a friendly conflict resolution management, all out of the box. This is available whether you’re using a traditional Access database linking a SharePoint list or using a web-enabled database in an Access client.

2010 is much better at handling breakups

There is no built-in method to work offline as there was in 2007. Instead, in 2010, whenever a network connection was dropped, the application will automatically go into a disconnected mode. The user can see all that is going on in the status bar that may appear:

Status Bar indicating that some tables are disconnected

When the network connection is restored, the user will see a message bar with a button to reconnect and thus return to online mode:
Message bar prompting for synchronization

If the user clicks the Synchronize button, the offline edits are immediately reconciled with the server, but the user can also ignore that message and continue working offline, synchronizing at a later time.

This is an improvement over 2007 where a dropped connection would be disastrous to the session. To avoid the issue, you had to explicitly toggle offline mode while you’re connected. Behind the scenes, all tables are opened & cached locally using XML. Clearly, that wasn’t too practical if you couldn’t anticipate when the network would degrade or get dropped, and thankfully it’s been rectified in 2010.

Great! But I still want to disconnect and batch my edits!

Fortunately, there is an approach to doing this. Using Internet Explorer, you can go to File -> Work Offline.
Select Work Offline in Internet Explorer to block SharePoint synchronization

That blocks all network activity, including a running Access instance that has a link to a SharePoint list. Once you’ve dirtied a single record, you can then restore network activity. If there were no edits during the disconnection and network is restored, Access will automatically reconnect without any prompt. However, if there are any edits, Access will inform the user that the network is restored and the table can be reconnected. Thus, the network only needs to be disconnected long enough for you to dirty a record, then get back online immediately afterward. Obviously, that would not work very well if you had some kind of open network session such as downloads, FTP, remote desktop connection and so forth. If that is a concern, you may need to plan and train your users to work with this consideration.

Easily work offline

With the caveats in mind, it is possible to automate the “Work Offline” functionality and interrupt the network only briefly, long enough to dirty a single record which is all you need to be able to work offline. Even though when you’re back online and you’re editing different tables, those edits in all tables are all batched and won’t be committed until you choose to synchronize.

Whenever you want to enter the offline mode, you only need to call ToggleOfflineMode. It’ll then interrupt the network by making a API call that’s equivalent to selecting “Work Offline” in the Internet Explorer, dirty a record, then call the API again to restore the internet connectivity. To make this possible you need two things; a linked SharePoint list that you can use as a target to dirty. It doesn’t have to be one of your actual data tables and you could even create a stand alone table with one column (besides the required ID column) and one row. The second thing is of course the code to call the API. Simply copy the code at end of this post & insert into a new blank module.

Errors to watch out for

Because it may take some time between the actual disconnection and Access to detect that network is lost, there may be errors if user immediately moves on to other tasks after disconnecting. You can trap for those errors in your usual error handling and typically re-attempt the same operation. You may get either error number 3907 or 3918. 3918 typically can be resolved by waiting a bit and trying again. In my experimentations, if you try to handle the error in a VBA error handler, it will get stuck in an infinite loop and DoEvents doesn’t allow Access to detect the state change. Thus, best thing to do is to simply show a message box telling user to try again and have the user re-initiate the action. This way, Access will be able to notice the state change and respond accordingly. The 3907 can be treated like 3918 with a caveat – if you decide you want to be offline for an extended period of time, the 3907 error can not be resolved until connection is restored. This should not be an issue with the code given which only disconnects for a brief moment.

Reconnecting

When you’re ready to reconnect and therefore synchronize the data changes, it can be as simple as training the user to click the “Synchronize” button on the Message bar or via backstage (File -> Reconnect All Tables). If you’re using a web database and you have it opened in Access, you can also use this VBA command to force a synchronization:

Keeping in mind that this has the side effect of synchronizing any design changes in addition to data changes. If you need to call the above VBA from a web object, you can use IsClient() to conditionally access the VBA or disable the functionality when it’s opened in a web browser where it’s not relevant.

But if you are using a regular Access database with linked SharePoint lists, there is currently no programmatic method to replicate the “Reconnect Tables” functionality, and therefore training users is the only way. If users have dismissed the Message Bar, they can still synchronize by either clicking on the status bar or going to Backstage.
Reconnect Button in the Backstage

Two more considerations

If you allow users to edit a large amount of records offline, you may have to deal with record conflicts with other connected users. Also, if it takes too long to synchronize, you may get timed out. Thus, I encourage you to test your specific environment and determine whether it will work well and how many records you can reliably synchronize at a time.

One more alternative

Though Access 2010 caching is significantly improved, it comes with less control. If that is not acceptable, an option is to turn off 2010 caching and therefore use the caching that was used in Access 2007. You can set this via Access’ Options shown here.

Disabling 2010 format caching and reverting to 2007 style caching

 

The difference is that you’ll be using XML to store the cached data rather than a Jet table, which has some performance ramifications as Access has to wade through the XML to satisfy your data requests. One upside: you get programmatical control, typically via RunCommand constants such as acCmdSynchronize and acCmdToggleOffline.

If you’ve found this code helpful or run into issues, please leave a comment and let us know about it!

7 Responses to "How to work offline with SharePoint and Access 2010"

  • Patrick
    October 29, 2013 - 1:06 am Reply

    I backed up my SharePoint site using Access. Upon completion of this backup, I deactivated my SharePoint site. Now when I attempt to access the data on my SharePoint site, I am unable to view any of the data in the tables because Access is trying to connect to SharePoint. How can I “access” the data store in the tables now that the SharePoint site has been decommissioned?

  • web hosting
    May 16, 2013 - 5:43 pm Reply

    Excellent, what a webpage it’s! This website presents valuable facts to us, keep it up.

  • bob alston
    April 18, 2013 - 8:36 am Reply

    I am just starting to run into a user who has apparently gotten disconnected and keeps on processing with the local/cached local copy, totally unaware that they are disconnected. I have given instructions on how to select File and then click on reconnect all tables. Sure be nice if there were a way to automate that. Also a sure fire way to identify that someone is working offline and giving then a heads up each time they log in.

    bob

    • Laszlo
      September 12, 2014 - 2:25 am Reply

      Hi Bob, we use a xmlhttp function to check the SP URL on a timer to make sure there is a constant connection. If offline, it changes a picture/icon on the dashboard of the app to show its disconnected and informing the user that they are working offline. You could also do a msgbox to warn them as well with this process.

  • kurtinco
    December 19, 2012 - 1:38 am Reply

    Why isn’t there a way to reconnect programmatically in a normal 2010 Access database? Haven’t you all realized that users, no matter how much training you give them, will never all be able to reconnect their own linked SharePoint lists.

  • Billy
    November 22, 2012 - 8:05 pm Reply

    Hi, This is good info. I didn’t know about this new cache functionality, yhanks for the blog.
    Is there a command or method to force Access 2010 to cache all the SP lists in the DB?
    Before i distibute the Access front end to users i would like to cache all the lists. I’m trying avoid the situation where a user attempts to use a particular SP list for the 1st time when they are offline and gets the “Linked table is unavailable” message.
    The only method i can see is to systematically open each list in turn before saving the front end.

    • Irfan
      July 12, 2013 - 2:32 pm Reply

      Just let user know to delete old database and download new database from SP server it will automatically cache all the table into local DB. but remember to
      1-checked (use the cache format that is compatible with Microsoft access 2010 and later)
      2- unchecked (clear cache on close)
      3- unchecked (Never cache)

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.