Need way to create a one way data feed from SharePoint 2010 to Access 2010

[MauricioP]

So I have a SharePoint List and an Access DB... I'm trying to create a one way link from SharePoint to my DB so my SharePoint will update the DB but not the other way around... The purpose of the database is for reporting only. In Access there's only 2 options:
(1) To import the data into a new table - the problem is that this is a one time import and does not refresh so will not see changes that take place in SharePoint
(2) To create a linked table in Access - the problem is that this is a two way link so any changes in access will feed to SharePoint (don't want this to happen).

How do I get around this?? Thanks!!!

[jforbes]

I haven't as of yet played around with SharePoint, so I'm not sure what a SharePoint list is, but I'm assuming it's a table, probably in SQL Server.

Based on that, if you have development access to SQL, you could create a View to the table with all permissions (Insert, Update, Delete) removed.

If that’s not an option, you could try hiding the Table Link in Access (Right click the table/Properties/Check Hide).

You could then also provide a Query based on the table with the Recordset Type set to Snapshot which would be Visible but Read-Only.

[zmbd]

J has part of this.

J - In the sharepoint environment, most users do not have any access to the underlying structures, only the the share point lists; thus, your SQL solution is not viable.

The snap shot is a good idea; however, is limited in scope and will not easily update.

MauricioP:
One possible solution and it builds on J's
Link your table.

Either name it USYS_and-your-table-name or set the table property to hidden

For ease of form development:
Temporarily Create a standard select query.
Use this Query to design your form. Once happy with the form set the properties of the form to allow edits/additions/deletions to "No"
Now switch your query to SQL view... copy and paste that SQL into the recordsource property of your form.
delete the query.

Here's the rub, any changes to the Sharepoint side of the sharepoint list are not real time to the queries nor forms...

[twinnyfo]

All,

I also believe it is possible to set the permission of a SharePoint List to Read Only. Thus, those who access the SharePoint List via the DB would have Read Only Access, but others, who update the list via SharePoint, would have read-write. However, if the users are the same, this will also cause problems.

Even Z's solution, although effective as a preventative measure, DB users could still access the linked list directly.

SharePoint is an interesting nut to crack. I have been fortunate enough to not have to use it often.

[Rabbit]

The problem with this one way update is what happens when someone updates the values in the Access database and then someone else updates the values in the Sharepoint list? Which update takes precedence? If someone can update the Access data separately from the Sharepoint data, eventually you're going to have conflicting updates.

Alas, that's a question only you can answer as you know best how you want to use the data. To accomplish this "one way data feed", you run VBA code that imports the data before anything that would present the data to the user.