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:

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

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.

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:

DoCmd.RunCommand acCmdSyncWebApplication

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.

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.

 

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!

Option Compare Database
Option Explicit

'IMPORTANT: The table and field must exist or you may rename
'           to one of your tables designated for sole purpose
'           of prevent automatic reconnection by dirtying a
'           record
Const strMyTable As String = "tblDummy"
Const strMyField As String = "Dummy"

Private Const INTERNET_OPTION_CONNECTED_STATE = &H32
Private Const INTERNET_STATE_CONNECTED = &H1
Private Const INTERNET_STATE_DISCONNECTED = &H2
Private Const INTERNET_STATE_DISCONNECTED_BY_USER = &H10
'Private Const INTERNET_STATE_IDLE = &H100
'Private Const INTERNET_STATE_BUSY = &H200
Private Const ISO_FORCE_DISCONNECTED = &H1

Private Type INTERNET_CONNECTED_INFO
    dwConnectedState As Long
    dwFlags As Long
End Type

'NOTE: Using VBA7 declaration (incompatible with older Access)
Private Declare PtrSafe Function InternetSetOption _
Lib "wininet.dll" _
Alias "InternetSetOptionA" ( _
    ByVal hInternet As LongPtr, _
    ByVal dwOption As Long, _
    lpBuffer As INTERNET_CONNECTED_INFO, _
    ByVal dwBufferLength As Long _
) As Boolean

'NOTE: Using VBA7 declaration (incompatible with older Access)
Private Declare PtrSafe Function InternetQueryOptions _
Lib "wininet.dll" _
Alias "InternetQueryOptionA" ( _
    ByVal hInternet As LongPtr, _
    ByVal dwOption As Long, _
    lpBuffer As Any, _
    lpdwBufferLength As LongPtr _
) As Boolean

Public Property Get IsOffline() As Boolean
On Error GoTo ErrHandler
    Dim l As Long

    If InternetQueryOptions( _
        &H0, INTERNET_OPTION_CONNECTED_STATE, l, 4 _
    ) Then
        IsOffline = ( _
            INTERNET_STATE_DISCONNECTED = ( _
                l And INTERNET_STATE_DISCONNECTED _
            ) _
        )
    End If

ExitProc:
    On Error Resume Next
    Exit Property
ErrHandler:
    Select Case Err.Number
        Case Else
            MsgBox _
                "Error " & Err.Number & ": " & Err.Description, _
                vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Property

Private Function SetOfflineMode( _
    ByVal Offline As Boolean _
) As Boolean
On Error GoTo ErrHandler
    Dim CI As INTERNET_CONNECTED_INFO

    If Offline Then
        CI.dwConnectedState = INTERNET_STATE_DISCONNECTED_BY_USER
        CI.dwFlags = ISO_FORCE_DISCONNECTED
    Else
        CI.dwConnectedState = INTERNET_STATE_CONNECTED
    End If
    SetOfflineMode = InternetSetOption( _
        0&, INTERNET_OPTION_CONNECTED_STATE, CI, LenB(CI) _
    )

ExitProc:
    On Error Resume Next
    Exit Function
ErrHandler:
    Select Case Err.Number
        Case Else
            MsgBox _
                "Error " & Err.Number & ": " & Err.Description, _
                vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Function

Public Sub ToggleOfflineMode()
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    Set db = CurrentDb

    If IsOffline Then
        SetOfflineMode False
    End If
    With db.OpenRecordset( _
        "SELECT * FROM [" & strMyTable & "]", _
        dbOpenDynaset _
    )
        'The table is cached
        SetOfflineMode True
        .Edit
        .Fields(strMyField).Value = .Fields(strMyField).Value
        .Update
    End With

ExitProc:
    On Error Resume Next
        'ensures internet connection is always restored when leaving
        SetOfflineMode False
        Set tdf = Nothing
        Set db = Nothing
    Exit Sub
ErrHandler:
    Select Case Err.Number
        Case Else
            MsgBox _
                "Error " & Err.Number & ": " & Err.Description, _
                vbCritical, "Unexpected error"
    End Select
    Resume ExitProc
    Resume
End Sub