Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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!

Publicado en Access Help, Access Web Database, SharePoint, VBA

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores