Refresh data in Access from linked Sharepoint 365 List

[AlanC002]

Hi All,

What's the best practice for refreshing a table linked from a list in Sharepoint 365?

I have tried:

CurrentDb.TableDefs([table name]).RefreshLink

Which works; however:
* It is slow and impacts on the performance of Access

* It seems to be only applicable to the the particular instance of CurrentDb (presumably because currentDb is a pointer not an actual object?)

TBH I am thinking I've missed something obvious; is there a setting that makes Access do this automatically in the background as there appears to be for ODBC connections?

Cheers
Alan

 

[NauticalGent]

What are the circumstances that prompt you to refresh? After every record update? If that is the case then of course your performance will take a hit.

I use SP 2007 as my BE and as the records update so does SP. I do notice a delayed write every now and again but they are few and far between.

When my application starts up, I will refresh all my linked tables, both SP and network drives. It probably isn't nessecary but I do it just in case the Access Gnomes were mischievous the night before.

 

[AlanC002]

Hi NauticalGent.

Okay here is the scenario, and please forgive me for going through it step by step but I want to be clear about this in case what I am doing is not valid. I am no expert in Access!

A Sharepoint list is created in Sharepoint 365 via the web interface- create a new site, then new app -> List, create the fields and save.

In access, the list is then added to Access via the Import and Link group of the External Data ribbon: More, Sharepoint List, enter the URL of the site in Sharepoint and then 'Link to the data source by creating a linked table' option. Select the list, and OK and a linked table appears in the Object browser.

In my application, the Sharepoint list is written and read by MS Powerapps; but it doesn't matter- you can use the SP web interface to change it with the same results.

My Access app runs SQL queries in VBA; and here is the problem: an insert query results in the Sharepoint list being updated. But a select query will not find new records added to the list, unless the linked table is first refreshed.

Doing that programmatically as in my post above not only takes a long time; I'm seeing from about a second up to 5 seconds (and increasing) so obviously I'm wondering if there isn't a better way!

Cheers
Alan

 

[NauticalGent]

That is strange and not how my setup is working at all. It could be because you're using SP 365 and I am actually using a server version...I thought it would have been identical performance but I really do not know.

I DO know however that newly added records and updated records are immediately available for select queries without have to refresh the list.

The only explanation I can fathom would be that SP 365 behaves differently...?

 

[AlanC002]

I hope not, but possibly! I'm just researching that now. O365 makes extensive use of Sharepoint, OneDrive for Business is actually just that- as you can see from the path ([username]-my.sharepoint.com/personal/...) that you see when working with it. Anyhoo...

I agree with you that it should work as you say, and thanks for confirming my methodology for linking.

Thanks
Alan