As strange as it seems for some IT professionals, days when VBA was often used to Office application are not dead. In some environments, VBA programming remains the easiest way Microsoft Office users and developers can create solutions. And, as one of the strongest integration is between SharePoint 2010 and Microsoft Access, I have decided to approach one the main, which is how to import SharePoint into the local database from code.

Without more explanations, I thing code below is easy.

On Error GoTo error

' Variables definition here
Dim tbl As DAO.TableDef
Dim fld As Field

'Delete table if exists
For Each tbl In CurrentDb.TableDefs
    If tbl.Name = "Table name" Then
        DoCmd.DeleteObject acTable, "Table name"
        Exit For
    End If

'Import SharePoint list
DoCmd.TransferSharePointList acImportSharePointList, "http://urlofthesite", "{A92FF376-FC2E-4390-BB03-CC47C756EB08}", "{F1AA137A-7C97-4C89-AB79-31447B77545C}", "Table name", True

'Create column a column to work with
CurrentDb.TableDefs("Table name").Fields.Append CurrentDb.TableDefs("Table name").CreateField("My local text field", dbText, 255)

    Exit Sub

    MsgBox Err.Description
    'In case of error, delete imported table to empty the memory
    DoCmd.DeleteObject acTable, "Table name"
    Resume exitOnError

End Sub

The code is deleting the table if already exists in the local database from the previous operation, import the list based on specified view and list id and create a local field to work with. Typically, developers create a local table, often containing more columns to work with than SharePoint list, import the list, append the list data into local table and update the data into local table, for the main reason they can have there as many field as they want without affecting the SharePoint list. But no need for this. Everything can be done in less operations. Once the SharePoint list is imported, developer can perform as many operations he wants without affecting SharePoint data, which in most of the cases is business critical.

The key is DoCmd.TransferSharePointList, which is very well documented here.