Inserting data into SharePoint list from Excel using Access

I have seen there is a very common requirment in many SharePoint Applicaiton where we need to get the data in SharePoint which is available external data sources such as SQL Server DB or Oracle DB. We need to take this data in SharePoint from these sources and then process it. It can be done easily using Excel and then creating a linked table from Access. I am putting a scenario here where I have the data avaialble in the Excel (copied from SQL Server Table) and I will use Access to insert the data available in Excel into SharePoint list.

Here are the steps to accomplish this:

1. Create a table in Access with the same columns as there in the Excel sheet. So for our example lets say that our excel has a column of data with the Column name as "Title" and we will keep the workbook name as Data in the Excel. So In Access we need to create a table having a column with the name as "Title". Lets have the name of the table created in Access as "AccessEmployeeTitle".

2. Now we will add a button to the Access tool, On click of which the Data in the excel sheet will be loaded into the table created in the step 1.

3. Add onClick eventhandler to the button and add the below code

On Error GoTo UploadDataErr
Dim Status As Integer
Status = ImportFile("AccessEmployeeTitle", "Data")
If (Status) Then
MsgBox "Successfully upload the data"
End If
Exit Sub
UploadDataErr:
MsgBox "Error in Upload Data" & vbNewLine & "Error ID: " & Err & vbNewLine & "Error Desc: " & Error$

In this code the AccessEmployeeTitle is name of the table in Access and Data is the name of the workbook in Excel sheet. This code will put the data from Excel sheet to the local table created in Access.

4. Create a Sharepoint linked list table on Access. Here are the steps:

a. Goto Create Menu->Sharepoint lists -> Existing sharepoint list



b. Enter the SharePoint Site URL having list to insert the data:



c. Select the option “Link to the data source by creating a linked table”.

d. This will show all the lists in the SharePoint URL. Select the list in which we need to insert the data. I have a list with the name "SharePointEmployeeTitle".

e. With these steps a linked list in Access will be created and now you can directly edit the data in this list which will be reflected in SharePoint list.

5. Now we will create a stored proc which will copy data from the Access table created in the Step 1 in the SharePoint list:

INSERT INTO SharePointEmployeeTitle ( Title )
SELECT AccessEmployeeTitle.Title
FROM AccessEmployeeTitle
WHERE NOT EXISTS (SELECT ID FROM SharePointEmployeeTitle WHERE SharePointEmployeeTitle.Title = AccessEmployeeTitle.Title);

6.Create another button, and on clicking of this button, Data would be transferred to SharePoint list. So we will use OnClick event of this button and here is the code for transferring the data:

On Error GoTo TransErr
DoCmd.SetWarnings False
DoCmd.OpenQuery "<Query created in Step 5>"
MsgBox "Successfully Transferred data"
DoCmd.SetWarnings True
Exit Sub

TransErr:
MsgBox "Error in Data Transfer" & vbNewLine & "Error ID: " & Err & vbNewLine & "Error Desc: " & Error$

So with these steps this can be accomplished.This solution gives the flexibility to append the data in SharePoint list and do not use any complex Object model code. It uses only OOB features of SharePoint.