So I have had a love/hate relationship with Microsoft Access. Very early in my career, I was tasked with developing a website for a university and, given budget constraints, we decided to use Access as the backend database. For any of you that have made this novice mistake, you undoubtedly know that this doesn’t scale past 1 or 2 users and the website would be doomed to fail with even a modest amount of traffic. So, for the longest time I was an evangelist against using Access for anything other than general Information Worker tasks.
That was, until, I saw the power of using this tool with querying and managing large lists in SharePoint.
The Large List Dilemma
Without going into the technical database details behind the behavior, just know that if you have more than 5,000 items in a list, behavior starts to change. List views may not return, you won’t be able to filter column data, you cannot delete this large list, you cannot index a column, and on and on. This is because, SharePoint puts in a list view threshold of 5,000 items that prevents these actions once the threshold is exceeded. One common reaction is to raise the list view threshold in Central Administration to prevent this behavior, but that has harmful effects as well. In another post, we’ll discuss the value of having an admin window where this threshold is raised, thus allowing certain administration operations to be executed. For the purpose of this article, let’s put that aside.
So how can we view and manage these lists? That’s where Microsoft Access comes to the rescue.
Querying Large Lists with Microsoft Access
Let’s take a simple example where we want to query for a specific data set in a large list that has over 15,000 items. We have defined a column “Category” which is a selection of a color (Red, Green, or Blue). I would like to return all of the items of category “Blue”.
My first approach would be to create a new view in SharePoint and filter on category.
In the View Settings for the list, I would do something like this:
Then, of course you are greeted with the following error message, which shows us that we won’t be able to accomplish our task through the SharePoint UI alone:
This is where Microsoft Access comes in. For our example, here are the steps to query through Access:
- Open the Access application and create a new “Blank Desktop Database”.
In the top menu, click on “External Data” > “More” (in the Import & Link Section) > “SharePoint List”.
In the “Get External Data – SharePoint Site” screen, put in the URL of the SharePoint site. Make sure “Link to the data source by creating a linked table” radio button is selected. Click “Next”.
- In the next screen, select the SharePoint list you want to query from and click “OK”.
You will now see the List as a Table in the left-hand pane.
Double-clicking on this list opens the tab in the main view.
In order to accomplish our task, we now have a few options:
- Use the UI to simply filter “Category” column by the value “Blue”
- Use the Access Query Builder to create a GUI-based query
- For those that feel more comfortable writing SQL, you can create a query in the SQL View as well
For our purposes, we will simply choose the first option and filter through the UI.
Click on the downward-arrow icon on the Category column and filter for only the value “Blue”.
You can now see that we have the set of 3,751 items in our list that are categorized as “Blue”.
Once you get comfortable with using Access to perform simple queries on large lists, it’s a natural extension to perform more advanced queries, updates, and delete operations.
The follow-up article Manage Large Lists in SharePoint with Microsoft Access – Update Large List Using the SQL View covers CRUD operations.