this demo Peter Kalmström, CEO and Systems Designer of kalmstrom.com
Business Solutions, shows how to input data from an SQL
Server database into SharePoint Online. It cannot be done
directly. Instead Peter opens both the SharePoint list and
the SQL Server database table in Access. Then he creates
a query that copies the data from the SQL Server to SharePoint.
Create a new SharePoint list
Go to the Settings gear and select ‘Add an App’.
Click on the Contacts icon to create a new SharePoint
list. A pop-up window will open where you can enter
your Contact list’s name and click on Create.
Open the new list. Add or remove list columns so
that you get the list you prefer.
Open the SharePoint list in Access
To open the list with Access, open the LIST tab
and select ‘Open with Access’.
In the window that opens, select the radio button
for ‘Link to data on the SharePoint Site’ and click
OK. Now list will open in Access.
Connect the SQL Server to Access
In Access, open the EXTERNAL DATA tab in the ribbon
and click on the ODBC Database button.
In the window that opens, check the radio button
‘Link to the data source by creating a linked table’
and click OK.
In the Select Data Source dialog, click on New to
create a new data source and use the SQL driver.
Save the new data source.
Change the default database to pick up the new data
When you are back in the Select Data Source dialog
again, select the new data source and link it to the
Select BusinessEntityID as the Unique Record.
Now Peter has two connections in Access, with SQL and with
SharePoint, and we can connect the SQL contacts database
to the SharePoint contacts list. For that he creates two
queries: one that selects data from the SQL database table
and another one that appends it to the SharePoint list.
Create a query that selects data from an SQL database
table and appends it to a SharePoint list
Click on the Query Design button under the CREATE
tab and select the SQL database table.
Click on the Append button under the ribbon DESIGN
tab to append data to the SharePoint list.
Select how the data from the SQL database table
should be added to the SharePoint list.
Click on the View button under the ribbon DESIGN
tab to view the result.
If everything looks good, click on the Save icon.
Run the query by clicking on the Run button under
the ribbon DESIGN tab. Now the SQL Server data will
be imported to the SharePoint list.