update sharepoint list using MS Access

Question

I have created earlier sharepoint list through excel.

Users generally give data in excel which need to upload at sharepoint list. Now I got new excel with additional columns and records & want to update already created sharepoint list with this data.

I have sharepoint linked table created in MS access database which has been used for reporting purpose.

Using MS 2010 version for excel/access database.

Please suggest how i can update existing sharepoint list with new data which has additional columns/records which is provided in excel. I am not looking for third part tools.

Anyway with MS access database/sharepoint?

Thanks

Answer 1

You should be able to write VBA code in MS Access that loops through your cells (rows and columns) in your Excel spreadsheet, and inserts the data into your Sharepoint linked tables. You can access Sharepoint linked tables the same way you access any other linked or non-linked table in Access. No third party tools or controls or ActiveX controls will be needed.

To update data in an Access table you can use the CurrentDb.Excecute method together with an SQL INSERT or UPDATE statement. You can also use DAO to open a Recordset Object and perform the additions or edits there.

You haven't made it clear in your question whether you will be needing to edit existing rows in the Sharepoint table or if you will need to be adding new rows in the Sharepoint table. You also haven't made it clear if you know how to get the data from your Excel spreadsheet using VBA or not. Please consider updating your question with a little more information.