Access Losing Connection to SharePoint List (1 Viewer)

M

mdex

Registered User.
Local time
Today, 00:28
Joined
Jul 31, 2013
Messages
28
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?

Code: 
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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:28
Joined
Feb 28, 2001
Messages
27,140
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.
 
M

mdex

Registered User.
Local time
Today, 00:28
Joined
Jul 31, 2013
Messages
28
The_Doc_Man said:
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.
Click to expand...

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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:28
Joined
Feb 28, 2001
Messages
27,140
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

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:28
Joined
May 21, 2018
Messages
8,525
Not sure if this helps, but here are the commands to toggleOffline or synch

Code: 
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

NauticalGent

Ignore List Poster Boy
Local time
Today, 03:28
Joined
Apr 27, 2015
Messages
6,321
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”
 
Last edited:
M

Mark_

Longboard on the internet
Local time
Today, 00:28
Joined
Sep 12, 2017
Messages
2,111
As a coding note, rather than
Code: 
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?
 
M

mdex

Registered User.
Local time
Today, 00:28
Joined
Jul 31, 2013
Messages
28
NauticalGent said:
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”
Click to expand...

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

mdex

Registered User.
Local time
Today, 00:28
Joined
Jul 31, 2013
Messages
28
Thanks MajP. I'll have a look into this.

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

Code: 
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
Click to expand...
 
M

mdex

Registered User.
Local time
Today, 00:28
Joined
Jul 31, 2013
Messages
28
Mark_ said:
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?
Click to expand...

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.
 
You must log in or register to reply here.

Similar threads

X
How to have a front/back split on Sharepoint (not Lists)
Replies
6
Views
231
tvanstiphout
T
A
MS Access link to a Sharepoint Excel File via Connection String
Replies
2
Views
855
andatabase
A
5
Keeping sharepoint list/table readable (no need to update) but adding more fields to same table?
Replies
3
Views
325
NauticalGent
NauticalGent
tmaccabe
SharePoint List- Data loss using bound Access Form
Replies
14
Views
720
GPGeorge
GPGeorge
A
how to insert attachment in sharepoint list using ado access vba
Replies
3
Views
194
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom