locked
Using Access to update an existing Sharepoint list RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have opened my Sharepoint list in Access, and I need to update one field from another table I imported into Access. I make my query, and when it ran, it said I was violating integrity because a different field from the one I was updating would contain invalid data. . Perhaps I don't understand how the link to Access works, but I thought I could only update the fields I choose. I can't use Access to update and import if it always tries to update every field!

    Here's the query from Access in SQL View:

     

    UPDATE [SharepointList]
    INNER JOIN tblFromAccess ON [SharepointList].[Account Number] = tblFromAccess.Account_Number
    SET [SharepointList].[AssocationCode] = [tblFromAccess]![AssnCode]

     

    I am not even sure I am putting this in the right forum. Please help.

     


    • Edited by Mike Walsh FIN Wednesday, July 27, 2011 5:34 PM Bad language removed
    Wednesday, July 27, 2011 5:20 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ,

     

    I understand that you want to update your SharePoint list column AssociationCode to the value of another table column AssnCode .Please make sure that the two columns are of the same type .I notice that this line   ‘SET [SharepointList].[AssocationCode] = [tblFromAccess]![AssnCode]’ .The ‘!’ should be changed to ‘.’ .I have a test using your uery and the query turns out to be  in right form .

     

    If you have any more questions ,please feel free to ask.

    Thanks

    Entan Ming
    • Marked as answer by Emir Liu Friday, August 5, 2011 6:30 AM
    Monday, August 1, 2011 6:28 AM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Which SharePoint and which Access ?
    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    Wednesday, July 27, 2011 5:35 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Oops, duh. Sharepoint 2007 & Access 2007.
    Wednesday, July 27, 2011 5:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ,

     

    I understand that you want to update your SharePoint list column AssociationCode to the value of another table column AssnCode .Please make sure that the two columns are of the same type .I notice that this line   ‘SET [SharepointList].[AssocationCode] = [tblFromAccess]![AssnCode]’ .The ‘!’ should be changed to ‘.’ .I have a test using your uery and the query turns out to be  in right form .

     

    If you have any more questions ,please feel free to ask.

    Thanks

    Entan Ming
    • Marked as answer by Emir Liu Friday, August 5, 2011 6:30 AM
    Monday, August 1, 2011 6:28 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    > Oops, duh. Sharepoint 2007 & Access 2007.

     

    So I have just deleted a post from someone who felt that a reply about Access 2010 and SharePoint 2010 was suitable in this thread.

     

    Guys:

    1. Read the previous messages in a thread

    2. NEVER post SP 2010 information in a thread in a pre-SP 2010 forum.

     

    Moderator pre-SP 2010 forums

    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    Wednesday, August 3, 2011 7:04 AM