Create List Example Data in Access
A SharePoint Online tutorial by Peter Kalmström
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
uses a modified Issue Tracking list in the demo.
Now when you refresh the SharePoint list, you will see the new items created
by the Access query there.
- 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
If the Open in Access button is not active you can follow these steps instead:
- Start Access.
- In Access, create a blank desktop database.
- Delete or close the default table.
- Click on the More button under the EXTERNAL DATA tab in the ribbon and
select SharePoint List.
- In the dialog that opens, paste or write in the path to the site that
has the list you want to open in Access.
- Select the linked table option and click Next.
- 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.