Need to determine if SharePoint connection is down

[chuckcoleman]

I was observing a user of the db I built and saw something I didn't consider. I'm using Office 365 for Business and the Access front-end connects to SharePoint lists. The user clicks on a command button which starts vba code to do several things and towards the end, runs an append query. The user or I didn't notice the small notification in the bottom right corner of Access that the SharePoint connection was off-line. After clicking the command button she got an error message, which I had never seen in my testing because I hadn't lost my connection, so, she tried the command button again. That ran the append query again which resulted in duplicated records. Note, this table should be able to accept duplicates. It's a table for entering payments and partial payments are typical with a final payment later.

Therefore, I need to use some vba code to determine if the connection to SharePoint is down. If it's down, I can then stop the vba code. Any ideas?

 

[theDBguy]

Hi. What was the error message? I thought appending records to an offline table would be fine because the table is cached anyway and will just sync up when the connection reestablished.

 

[chuckcoleman]

It was 3027 which indicates the db is read only. I think that's because the SharePoint lists are off line.

You're right on the cached but that's the problem. If the user clicks on the command button more than once, it appends the records, (in the cached mode), more than once. Then when SharePoint is back online, the SP lists get the duplicated records.

 

[theDBguy]

It was 3027 which indicates the db is read only. I think that's because the SharePoint lists are off line.

You're right on the cached but that's the problem. If the user clicks on the command button more than once, it appends the records, (in the cached mode), more than once. Then when SharePoint is back online, the SP lists get the duplicated records.

So, if you don’t want duplicate records, then you could try checking the table first if the record you’re about to add already exists. Or, you could try handling the error and show a different message to let the user know not to click the button again.

 

[chuckcoleman]

dbGuy, on another forum someone posted:

Well, if you are in off-line mode, and you have the cache turned on, then that user should be able to freely add records. The PK will start at -1, then -2 and so on.

You could check for a internet connection, but if they are flipped into off line mode, then Access will often remain in off line mode.

So, you could have a dummy table, and add a record - if the PK is < 0 then you are off line (and delete that dummy record). I am not aware there is some command that will tell you that you are in fact on-line. I suppose you could also "ping" the server with a shell() command, but as I stated, you can actually be off line with a valid connection, so to be really sure the negative PK trick would tell you for sure if Access "thinks" and is "behaving" as if it is in off line mode.

It is not clear why the button fails, but if you have the check box "use SharePoint 2010 or later cache) selected, then users should be free to edit, view and add records. when you re-connect, then access will sync all the changes and updates (bi-directional sync).

Here is my follow-on to his post:

Albert. Thank you, your suggestion was helpful. I did some testing by disconnecting my computer from the Internet and then running the code attached to the command button. Like you said, in the SharePoint table, (not a temporary table), all of the records imported have a PK <0, i.e.-1, -2, -3, etc. I wrote a simple DCount query looking for PK's less than zero. What was interesting is that the query could not count any PK less than zero, only PK's >0. My workabout in the criteria was to place, Like ("-*") and that worked. Wierd that the PK is an autonumber field, a number, but less than zero didn't work. With the DCount criteria I created a MsgBox that explained what was going on and how to get SharePoint back on line if there Internet connection was still up. AND, don't import the same spreadsheet again, (which was part of the code. So, thank you.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[The_Doc_Man]

On the narrow discussion of being unable to compare the PK against a negative number, what you need to look at is what is the data type of a PK for your SharePoint tables. I do not have SharePoint so have no way to look.

For a native Access table, an autonumber is of type LONG INTEGER which can be negative. However, there ARE such things as unsigned integers that can never be interpreted as negative numbers so no comparisons would ever find negatives. If you were curious, you might just check the datatype of the PK in question.