Before writing something that would take some time to do in terms of loading data, I was able to successfully conduct a bulk load of data into SharePoint with MSAccess. Here is my mock up test that I did.
I created a SharePoint list in a sample site with the following schema:
Title Single Line of Text
Sample Column Single Line of Text
Sample Lookup Column Lookup to a Contacts List
I chose these columns because they represent some non-standard column types of mapping in order to do bulk loads with. I then created a new Access 2007 database and linked to the [Sample List] in SharePoint.
I then created a mirror data load table in the access database with the following schema
Sample List Local Source
Note that the source data for inserting into a SharePoint list lookup column via Access is a number (the actual ID of the specified target value of the Lookup list).
I then populated the local list with 100 rows of sample data.
I then created a Append Query that would take the rows from the [Sample List Local Source] and put it into the linked table to the target SharePoint list. The query for the [Sample List Load] is below:
Upon running the query, and refreshing in the browser, you can see all 100 rows in the list. The query took about 10 seconds to run. I would suggest you bulk load in batches to avoid a really long running operation.
Here are the results:
I would also suggest that we make a default view on the list that limits the data to rendering a max of 2000 items so you don’t’ incur big perf hits when viewing the data.
This list will help in creating the source table and its data types to map to the target SharePoint list.