MS Access Linked Table - Working Offline

Posted on 2012-09-04
Last Modified: 2013-03-19

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.
Question by:Russellbrown
    LVL 84
    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.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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.

    Author Comment

    MS Access 2010 / Share Point 2010.

    Thank you for the heads up. I will give it a try and hope to be successful.
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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).
    LVL 47

    Accepted Solution

    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.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now