Updating date fields on SharePoint List from Access Update Query


  • I'm trying to use an update query from Access 2010 to update a Date/Time field on a SharePoint 2010 list. 

    Every time I do, I get the dreaded "You cannot update this field because the value you're trying to apply is not valid or would break a data integrity rule" error.

    I've tried converting the field's format to a date, to a string, to a variant... to no avail.

    Interestingly, when I use a Iif expression (the HCOK column) to compare the fields, it's saying they're different:

    The Expr1 column is a DateDiff comparing the days between the two.  Which works fine.

    Any hints on how I can update this field? 

    Thursday, July 05, 2012 6:06 PM


All replies

  • Hi ,

    I understand that you have update query form Access 2010 to update a Date/Time field in SharePoint list .But you cannot save the changes .I have a test based on your description ,you can update the Date/Time column using an external source .

    1. Create an external source to SharePoint 2010 list .
    2. Check ‘Link to the date source by creating a linked table  ’ .
    3. In this way ,you can update the column as you want .
    4. In Access ,the date/time column will only show dates .When add items to list ,the default time  part will be 12 am .

    You can also add query to update the list item .


    Entan Ming

    Entan Ming

    TechNet Community Support

    Friday, July 06, 2012 11:18 AM
  • That's what I was trying to do.  I have a database with a local table, and then the SharePoint list as a linked table. 

    The date in the linked SharePoint list appears to be a Long Date (even though the table design says it's a Short Date).  I've tried changing the format on my local table to Short Date, Long Date, and General Date, to no avail.

    Friday, July 06, 2012 7:49 PM
  • Hi ,

    Can you describe the detailed steps about creating the access database ?

    In my testing ,I have created a date column and a date and time column in SharePoint list .After adding the list as external data for the database ,the date column are showing as short  and the date and time column are showing as general  (This can be seen under Table Tools>Fields>Format ) .

    You can edit the short and general date column both in Access .The general date time column will only have date part when add a new entry in Access ,You can manually type the time part to have a test whether you can save the changes (I can save the date part alone and when open the list in browser ,the time part is automatically set to 12:00 am ) .


    Entan Ming

    Entan Ming

    TechNet Community Support

    Tuesday, July 10, 2012 9:50 AM
  • I wound up figuring it out on my own.  There were a few individual records that were causing problems.  (I wish the error message had told me that).

    I detailed what I did in this other thread:

    • Marked as answer by Mary Mascari Friday, July 13, 2012 12:59 PM
    Friday, July 13, 2012 12:59 PM
  • Hi,

    When trying to update a sharepoint list (SP2010) with an infopath (2010) developped form by an access (2007) query, I obtained the 3812 error in Access.

    I didn't have any records in my sharepoint list but i found that i have a field deleted in my infopath form design but not in my fields listing of the form.

    I have deleted this fieldname from the list and works

    Hope it would help some people

    Sorry for my bad english I'm French.

    Friday, December 06, 2013 9:41 AM