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:
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!
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.
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.
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