MS Access Linked Table - Working Offline


I have a linked table in MS Access ( at local drive and the version is 2010  ) that is linked to a Share Point list at a remote site. I would like to be able to work offline to update the linked table in Access.

How could I programatically ( using vba ) update the linked table offline?

The code that I am using to update the linked table ( e.g. ABC ) is:
Db.Execute strSQL, dbFailOnError

When I programatically set  "SetOnlineState" to  False, when I execute the above line of code, a error occurs and the error message reads:
1. err.Number =  3907
2. err.Description
Linked table 'ABC' is unavailable. Microsoft Access cannot contact the server. Check your network connection or contact the server administrator.
Would appreciate it very much your help to write a vba routine to handle offline / online mode for working with linked tables.

Thank you.
Who is Participating?
Dale FyeConnect With a Mentor Commented:
Personally, I resorted to creating local temporary tables and importing data from SharePoint to those tables during the application startup process.  I then wrote a series of routines that replicate the data back to SharePoint when the user is connected to the network.

This was painful, and required a lot of update and append queries.  I never did like the Access replication model, but unless 2010 Access/SharePoint are more tightly integrated than 2007, this is what you will have to resort to if you want to work disconnected from your Sharepoint data source.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You cannot update a remote linked table if you cannot connect to it. If you need to work both on and offline, you'll need to store the changes locally, and then upload those changes when the user connects.

I'm not sure how you can determine "when the user connects", however. Perhaps you could check each time the app is opened. If you determine that the connection exists, you could then write the changes in your local table up to the remote ones.
Dale FyeCommented:
What version of Access and Sharepoint are you using?

Access/Sharepoint are supposed to be integrated and allow you to take Sharepoint lists "offline" then resynch them with the server.  See my earlier post on this at:

Unfortunately, I was never able to figure out how to take the tables back "on-line" and resynch them.  Nothing I tried seemed to work.

Have not tried this with Access/Sharepoint 2010, so the integration between the two may be better now.
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

RussellbrownAuthor Commented:
MS Access 2010 / Share Point 2010.

Thank you for the heads up. I will give it a try and hope to be successful.
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Some of the MVP crosstalk has been about this recently. There are mixed sentiments (to say the least) about whether the technology is workable and reliable. The general consensus is that if you need offline replication/sync, then move the data to a platform that supports this (like SQL Server, Sybase, etc).
RussellbrownAuthor Commented:
Many thanks for the feedback. I have mulled over the issues and noted that some trade-offs will be necessary, principally on the issue of synchronisation of the recordsets. Will create a set of rules for the users as a sort of a workaround.

I will try out the route of creating a local holding area to park the recordsets downloaded from Share Point and to allow users to work offline. When connected back to Share point, users can update the linked tables using SQL / query. I will need to incorporate into the VBA  a test whether  a connection exists  before the update routine can be executed.

I agree that  it will be quite painful to codify these work steps but nonetheless necessary for my case.

Thank you.
All Courses

From novice to tech pro — start learning today.