In the first part, Manage Large Lists in SharePoint with Microsoft Access – Query Large List, we discussed the problem of managing large lists (over 5,000 items) and how you can use Microsoft Access to get over the threshold limit and view a list that has over 5,000 items. In this article, we will take the example one step further and show how to update the list using the SQL View. With just a moderate knowledge of SQL Queries, you will see the true power of using this method and a spike to your productivity while working with SharePoint lists.
As you’ll recall, we were working with a list called “CAML Threshold Limit” that contains 15,003 items. The list has 3 columns: ID (identity that gets created by default), Category (choice of Red, Green, or Blue), and ID1 (a copy of the ID field):
For our example, we will set all entries with a category of “Green” to “Red”. You can imagine that this would be a case where the Category field represents a project status and all of our “Green” projects are now “Red”. To do this, we first need to create a new query.
Create a new Access Query
To create a new Access Query:
Select Query Design from the Create tab.
Select the SharePoint List to perform the query against and click Add. Then click Close.
You’ll notice that the SharePoint List is represented in the new query window.
In the ribbon, click on View and select SQL View.
You’ll notice that the “Select All” query is generated by default.
Change the query to an UPDATE statement
Remember that we are looking to update all rows where Category has the value of “Green” and set it to the value to “Red”. To do this, we use a simple UPDATE statement.
Replace the SQL text with the following:
UPDATE [CAML Threshold Limit]
SET [Category] = “Red”
WHERE [Category] = “Green”
Execute the Query
Click Run to execute the Query
You’ll see the following message stating that you can’t undo the changes. Click Yes.
While the query is running, you’ll see the progress bar in the bottom of the Microsoft Access Application.
Once the update is complete, you’ll receive a notification that you are about to update a number of rows. This message is a bit misleading since, at this point, you have already updated the rows. Click Yes.
Upon re-loading the SharePoint list in Access, you can see that the update was successful.
Using Microsoft Access for SharePoint CRUD operations make your life much easier. In fact, I don’t limit the tool to only large lists. I use Access any time I need to do bulk operations in SharePoint.