Append Query From Access Into Linked SharePoint List with Lookup Fields in SP List

I am trying to append records in an MS Access table (I am using Access 2010, but can use 2007 if need be) into a linked SharePoint list.

There is an attachments field (when viewed from Access), but I am not concerned with appending these.

The SharePoint list has a few lookup fields, that in design view are of the Number format. Upsizing to SQL Server, I can confirm that 开发者_JS百科they are numbers (and I assume the Id for the related SharePoint list object).

I copied the structure of the SharePoint list and pasted into a MS Access table, and then filled in a few sample records

I used the query wizard to select all of the fields of the Access table (minus the Access table's autonumbered ID and attachments), click "Append", and then selected the SharePoint list.

If I run this query, I get the error that X record(s) were not appended due to validation violation reasons, which is all of them. I've checked all field definitions and none are defined as required or have a validation constraint.

The only thing I can think of is that there is some issue with the Lookup fields, but each number Id I've checked is a valid Id.

I'm hoping for a MS Access solution, but if the only approach is to programmatically do it, could someone send me directions on how to get started with the SharePoint 3.0 SDK?

Thank you for your time.

Lookup columns are not number type even your parent items are of number type reason behind lookup column always attaches the item id of parent item.If you want to see the data in access kindly use the "export to Access" functionality available in datasheet view

I have linked to a SharePoint table, then appended directly to it from a query.

My issue is that this process takes a very long time to push up to SharePoint, then 2 times as long when first I need to delete the records currently in the list before appending.

Is there a better way to blow out and create the table while preserving the link since it is not an Access object?

Anyway I hope this may help you out some and get me some insight as well.

Regards.