none
Synchronizing with Sharepoint (VBA) after loosing connection RRS feed

  • Question

  • Hi

    I have a SharePoint (2013) database and use Access (2013) as front end. I change the contents of the SharePoint list using SQL from VBA (CurrentDb.Execute txtSQL).

    Very often Access loses connection to SharePoint and I get error 3981. Afterwards the user sees a button in the ribbon, which enables him to manually synchronize the Access with SharePoint.

    My question: how to synchronize Access with SharePoint with VBA, so that the user doesn't have to do this manually and doesn't notice that there was a Connection error?

    I miss some VBA instruction like "synchronize" or something like this... I only found "DoCmd.RunCommand acCmdSynchronize" but it doesn't work...

    Can somebody help me?

    Kind regards,

    Agnieszka

    Monday, October 31, 2016 2:26 PM

All replies

  • Hi,

    When you say acCmdSynchronize does not work, was it when you know there is an active connection to SharePoint? Otherwise, I am not sure how you can synchronize with SharePoint if there is no active connection to the server.

    Just curious...

    Monday, October 31, 2016 3:49 PM
  • Hi Agnieszka,

    >> Afterwards the user sees a button in the ribbon, which enables him to manually synchronize the Access with SharePoint.

    Could you share us a scree shot about this button? How did you update SharePoint list from VBA in Access? Did you link your sharepoint list to Access database? I made a test with update query on linked table which is linked to SharePoint list, it works correctly.

    A detailed steps to reproduce your issue would be much helpful.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 1, 2016 6:40 AM
  • More explanation from the beginning…

    I have a SharePoint list with about 3000 items. The list has about 100 columns with different criteria describing each item. There are SharePoint workflows (2013) that are triggered, when the item is being updated (they send emails to the item owners).

    Me and few other users have to filter the list in a very complicated way (as I said – 100 columns…), so I created a front end in Access, that lets us choose the criteria for filter easily (from a form) and then with one button update the chosen items.

    The click on the button runs a VBA code, which looks like this:

    txtSQL = "UPDATE spList SET spList.SL" & " = " & Me!cbxSL & txtWhere
    CurrentDb.Execute txtSQL

    (txtWhere is a variable, that describes the criteria, it is created in a different part of code)

    The updating works perfectly and it starts the SharePoint workflows as it should.

    But the problem is, that sometimes the Access loses connection to SharePoint. (I have an impression that it happens more often, when I connect to intranet over wifi and not wire.)

    So sometimes I get an error 3981, that says:

    There were errors executing the bulk query or sending data to the server. Reconnect the tables to resolve the conflicts or discard the pending changes.

    After a moment I see this information in the right lower corner of the Access window, that says "All SharePoint tables are disconnected".

    And then the “Synchronize” button appears in the ribbon, the text next to it says: Reconnected to SharePoint. The connection to one or more SharePoint servers has been restored. Click Synchronize to close open objects, send pending changes to server, and synchronize with server data.

    After I click on the “Synchronize” button I get a window, which allows me to Retry all my changes.

    Unfortunately I'm not able to show pictures and links in the body of this message, because my account is not verified :(

    After I synchronize manually, everything works fine again, the items are being updated, the Sharepoint workflow is being triggered. But I don’t want the users to do it manually.

    So basically, what I would like to know is how to write a VBA code that would reproduce clicking on “Synchronize” and “Retry All My Changes” after I get an error 3981.

    I tried those two commands but it didn’t help:

    If Err.Number = 3981 Then          
    CurrentDb.TableDefs("spList").RefreshLink           
    RunCommand acCmdSynchronize

    I just don’t find enough info in the internet on these commands and how to use them. I guess they are either wrong commands or I use them improperly. When I test it while the connection is broken and I have this ribbon info with Synchronize button, I get runtime error 2046 “The command or action “SynchronizeAll” isn’t available now”.

    I hope that now I made the problem clear and somebody can help me :) I was trying to solve it for hours...

    Kind regards,

    Agnieszka


    • Edited by AgnSan Wednesday, November 2, 2016 12:05 PM
    Wednesday, November 2, 2016 11:45 AM
  • Hi,

    Thanks for the clarification. Typically, Access will store a cache of the List, and all changes to the table will automatically sync up with SharePoint once connection is reestablished. You might look around in your app if you need to adjust any setting to allow for offline changes. Otherwise, I am not sure how to make Access do any action once a connection is reestablished without manually clicking on a button.

    Good luck!

    Wednesday, November 2, 2016 3:54 PM
  • To my knowledge **IF** you updated data in off line mode then no VBA commands exist to “force” the connection.

    You “may” be able to get Access to FLIP back from off line mode by interfacing with the wininet.dll.

    So you start out with this:

    And then you can with wininet.dll get to this:


    However, ONCE you get as above, then users will STILL have to click on that notification.

    I can post the code I used in the past (interface to wininet.dll). If you wish. (however, it is not a lot of help).

    Users will STILL require the above final step of clicking on the status bar and then choosing re-connect, so I not sure if that’s going to help you much.

    It is “unfortunate” we can’t control this option since as you well know the off line mode is a great feature.

    The sync feature quite much requires you are connected, and if you have a active internet connection, but Access is in off line mode - you are duck soup to my knowledge. (ie: in trouble since no VBA exists to re-connect EXCEPT using the status bar as you and I have outlined).

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada


    Thursday, November 3, 2016 1:05 AM
  • Hello everybody

    Thank you for your answers, I guess my users will just have to do the synch manually each time...

    THANKS!

    Thursday, December 1, 2016 8:18 AM