Access Losing Connection to SharePoint List

[mdex]

Hi,

I have a small database working fine from a fileshare but have been asked to migrate this to SharePoint. I've uploaded my tables to SharePoint lists without many problems.

If I append single records from my dataset they synchronise fine. I have a button to import multiple lines from an excel file and here is where I'm having issues. When running this code I seemingly lose connection to the sharepoint list and the records are input locally showing negative ID's. I get the option to synchronise with the sharepoint list but I don't want the users of the DB to have to do this everytime.

I have a constant network connection and don't have any dropout so not sure why this is happening. Is there a command I can add onto the end to force synchornisation?

DoCmd.SetWarnings (Warningsoff)
If fosusername.Value = "SMoore" Or fosusername.Value = "mdexter" Or fosusername.Value = "SSebastian" Or fosusername.Value = "IKeech" Then
        
On Error GoTo HandleError

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"Exceptions", "https://SharePointFileLocation/Import.xlsx", True
HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit

Else
MsgBox ("You are not authorised to import. Please contact mdex with any issues.")

End If

Me.[subform1].Form.Requery
Me.[subform2].Form.Requery
DoCmd.SetWarnings (Warningson)
Me.Form.Refresh

 

[The_Doc_Man]

I have zero experience with SharePoint but I have seen many articles on this forum regarding use of SharePoint. May I suggest that you search the forum for "SharePoint" and "connect to SharePoint" and similar topics?

The SEARCH function is in the thin blue ribbon near the top of the screen, and is 3rd from the right on that ribbon.

 

[mdex]

I have zero experience with SharePoint but I have seen many articles on this forum regarding use of SharePoint. May I suggest that you search the forum for "SharePoint" and "connect to SharePoint" and similar topics?

The SEARCH function is in the thin blue ribbon near the top of the screen, and is 3rd from the right on that ribbon.

Thanks The_Doc_Man. I have been 'googling' for a few days to try and find a solution. I've seen the same or similar question asked a few times without a real answer.

Was just hoping asking might refresh peoples memory or catch someone as SharePoint lists don't seem to be that popular or well used.

 

[The_Doc_Man]

All I can suggest besides the search is to inspect the timeout settings that are used to detect lost connections. If they are too long, you might have a situation of mismatched timeouts. I.e. one side's "timeout setting" is shorter than the other side's "keep-alive" setting.

 

[MajP]

Not sure if this helps, but here are the commands to toggleOffline or synch

Public Sub toggleOffline()
  On Error GoTo errLbl:
  DoCmd.RunCommand acCmdToggleOffline
  Exit Sub
errLbl:
   Call ErrHandler(Err.Number, Err.Description, "Error in ToggleOffLine")
End Sub

Public Sub synchWithSharepoint()
  On Error GoTo errLbl:
  DoCmd.RunCommand acCmdSynchronize
    Exit Sub
errLbl:
   If Err.Number = 2046 Then
     MsgBox "You are already online, or problem synchronizing."
   Else
     Call ErrHandler(Err.Number, Err.Description, "SynchWithSharepoint")
   End If
End Sub

 

[NauticalGent]

Want version of Access are you using? I had this exact same problem when we upgraded from 2010 to 2013. All we could get from Microsoft was “we are aware of the problem”

 

[Mark_]

As a coding note, rather than

fosusername.Value = "SMoore"

you may be better served doing a lookup in a table of authorized users. This will avoid problems once one (or more) of the hard coded individuals wins the lottery and decides to move to a nicer area. You may also be better served by referencing the control (me. as you use below) in a more explicit manner. The ".value" should not be needed.

For the less fun part, you may need to do a loop through the lines in the spread sheet and individually insert them into your table. I have not used sharepoint, but I do remember reading that it can be very touchy for batched updates. Could you set up your transfer to a local temporary table first to validate the data?

 

[mdex]

Want version of Access are you using? I had this exact same problem when we upgraded from 2010 to 2013. All we could get from Microsoft was “we are aware of the problem”

We're using Office 365 which I think is equivalent to 2016?

 

[mdex]

Thanks MajP. I'll have a look into this.

Not sure if this helps, but here are the commands to toggleOffline or synch

Public Sub toggleOffline()
  On Error GoTo errLbl:
  DoCmd.RunCommand acCmdToggleOffline
  Exit Sub
errLbl:
   Call ErrHandler(Err.Number, Err.Description, "Error in ToggleOffLine")
End Sub

Public Sub synchWithSharepoint()
  On Error GoTo errLbl:
  DoCmd.RunCommand acCmdSynchronize
    Exit Sub
errLbl:
   If Err.Number = 2046 Then
     MsgBox "You are already online, or problem synchronizing."
   Else
     Call ErrHandler(Err.Number, Err.Description, "SynchWithSharepoint")
   End If
End Sub

 

[mdex]

For the less fun part, you may need to do a loop through the lines in the spread sheet and individually insert them into your table. I have not used sharepoint, but I do remember reading that it can be very touchy for batched updates. Could you set up your transfer to a local temporary table first to validate the data?

Thanks Mark_. This is what I've done as I couldn't get any VBA commands to reconnect/link tables to work.

Import to a local table. Use an append query to add to sharepoint list and then a delete query to empty the local table.

 

[Gotty]

Hi all,

Apologies for the 4 year thread bump

I also am experiencing something similar with my access database connected to an excel file. Basically when I am connected to excel and uploading the data onto sharepoint online lists, sometimes it works and sometimes it doesnt. Basically, the link from MSaccess to Sharepoint gets disconnected.

The first time or couple of times it works, but then theres a chance of getting disconnecting. My guess is that access is trying to send around 300 rows at once, and sharepoint list on cloud doesnt seem to handle it. I had to switch to MSaccess tables or SQL server. But I would have preferred on a sharepoint online list to provide non-premise functionality.

any ideas how this can be handed? any help would be appreciated

 

[GPGeorge]

Are you connecting via a reliable network, or through WiFi? The key variable here, in other words, is not Access or SharePoint, but the connection between them. If you are losing the connection during a mass upload, that's possibly the failure point. Although 300 records is not small, it's far less than even SharePoint is capable of handling. I'm going to guess it's network related.

But if you switch to a hosted SQL Server, or Azure SQL, you'll get non-premise functionality, won't you?

 

[Gotty]

I have a wired 500 download 50 upload connection. Not running them over wifi. I will check again to make sure user was not over wifi. However even over wifi, speed is close to that.

Yes I can switch to Azure SQL, but i am trying to avoid extra costs by using data sources which are part of Microsoft 365 user plans which include sharepoint online lists.

But correct ideally Azure SQL would be best way forward for database and online functionality

Many thanks for suggesstions.

 

[GPGeorge]

I have a wired 500 download 50 upload connection. Not running them over wifi. I will check again to make sure user was not over wifi. However even over wifi, speed is close to that.

Yes I can switch to Azure SQL, but i am trying to avoid extra costs by using data sources which are part of Mi

Spoiler:

crosoft 365 user plans which include sharepoint online lists.

But correct ideally Azure SQL would be best way forward for database and online functionality

Many thanks for suggesstions.

Good to know that it's not a WiFi connection for you, but if the user experiencing the problem is off-premises and connecting via WiFi, you still may have an issue. The issue is not speed, but reliability. WiFi can be prone to disconnects, and that's where that upload would be impacted. That's why it can be a problem for Access regardless of the Back End.

 

[Gotty]

Good to know that it's not a WiFi connection for you, but if the user experiencing the problem is off-premises and connecting via WiFi, you still may have an issue. The issue is not speed, but reliability. WiFi can be prone to disconnects, and that's where that upload would be impacted. That's why it can be a problem for Access regardless of the Back End.

Ok thanks I will wire the user's laptop on premise just to ensure connection is reliable and do some tests.

Cheers