Link Access 2010 Query to Sharepoint List

[kevinw]

Hi,

I'm hoping that someone may be able to help with this topic as it's driving me up the wall.

I have a database in Access 2010 which has the tables as a back end on server and everything else on front end on local drive.

I would like to link one of the Queries to Sharepoint so that people may be able to view up to date status of work being performed. I.e a wider audience can view when a piece of work has been logged via a Form that updates a Query and then flows into the data sheet view in Sharepoint.

I have looked at so many examples where there is a list in Sharepoint that is used to update a linked table in Access, but can't seem to find out how to essentially have a live update via the Query linking to Sharepoint.

Apologies if I have missed off any details which may help, and of course I massively appreciate any assistance on this subject.

Kevin

 

[GohDiamond]

Is your SharePoint list linked to your FE database? You'll have to have permission on the SharePoint site and Read/Write permission on the List if you are going to interact with it.
If you haven't already, go to the "External Data" tab in the menu of your FE and open the ribbon, in the "Import and Link" section select the MORE[v] dropdown menu. The first selection in the dropdown is "SharePoint List". Click on it and follow the prompts to LINK to the desired SITE and click NEXT then select the list(s) you want to connect with. Click OK after you've made the LISTs selection and you should see the live lists in your Navigation Pane under TABLES.

You can then write queries as usual, treating the list as a linked table.

I hope that helps,

CHeers!
Goh

 

[NauticalGent]

kevinw,

I don't profess to know everything, but I do know that although I have looked far and wide, I have not found a way to keep a list updated dynamically with a query.

Linked tables are one thing, querys are another. What I have had to do is run update querys periodically so that users can have updated information.

Hopefully, if there is a way, someone will come to your (our) rescue!

 

[kevinw]

Thank you thank for your replies. I will certainly try the settings in SharePoint as I should have all the access writes.

I have found a bit of a workaround...

I export the query to Sharepoint and then delete the data from the Sharepoint list basically just to give me my Sharepoint list. Then create a linked table from access to the list in Sharepoint. I then modify the query to an append query to append the newly created linked table. And finally use some via code (sorry can't remember it off hand it's literally 2 very basic lines) to delete all records in the linked table and to then run the append query to refresh the linked table which in turn then refreshes the Sharepoint list.

I hope that makes sense? I'm looking to put it in to action, having had it work on a test database.

Thanks again for your help both and apologies for not remembering the vba to delete the contents of a table and refresh a query. I'll try and remember to add it

Kevin

Vba code is:
DoCmd.runsql "delete * from LINKEDTABLE"
DoCmd.Openquery "APPENDQUERY"