Create List Example Data in Access

A SharePoint Online tutorial by Peter Kalmström

When you are trying different solutions and scenarios for SharePoint lists, it is helpful to use example data. In the demo below Peter Kalmström, CEO and Systems Designer of kalmstrom.com Business Solutions, shows how to open a SharePoint list in Microsoft Access and create such example data.

When you add users to a SharePoint site, a hidden users list is created. This list can be seen when you open the list in Access.

Create SharePoint list example data in Access

Peter uses a modified Issue Tracking list in the demo.

Under the LIST tab in the ribbon of the SharePoint list, click on the button to open the list in Access. Select the default option, to link the list.

If the Open in Access button is not active you can follow these steps instead:
1. Start Access.
2. In Access, create a blank desktop database.
3. Delete or close the default table.
4. Click on the More button under the EXTERNAL DATA tab in the ribbon and select SharePoint List.
5. In the dialog that opens, paste or write in the path to the site that has the list you want to open in Access.
6. Select the linked table option and click Next.
7. Now all lists f the site are shown, also the hidden users list. Select the ones you want to use.
Create a new table for Priorities. Copy the priority values from the SharePoint list column and paste them into the Access table.
Create another table for Titles. Peter has created some these titles in Excel and copies them to the table.
Click on the Query Wizard button under the CREATE tab in the ribbon. Keep the default Simple Query Wizard option. Click OK.
Select the fields that you want to include in the query. Click OK to have no Relationships and close the Relationship tab.
Click on the Query Design button under the CREATE tab in the ribbon and add the tables you want to use to the query.
Click on the Run button under the DESIGN tab in the ribbon to get all possible combinations of the fields.
If needed, adjust the query so that you get reasonable values and run the query again.

Peter adds the parameter 'not null and [the crawler e-mail address]' to the Work e-mail field to get rid of blank rows and the crawler e-mail address.
Click on the Append button under the DESIGN tab in the ribbon and select the SharePoint list where you want to have the example data from the dropdown in the Append dialog. Click OK.
Append the query columns to suitable fields in the SharePoint list.
Click on the Run button under the DESIGN tab in the ribbon.

Now when you refresh the SharePoint list, you will see the new items created by the Access query there.