locked
Update Existing List in Sharepooint created using Access RRS feed

  • Question

  • I have a list in sharepoint that is created from Access by using the export.

    The data in acess Access is a linked table to sql server.

    Now I have new data in sqlserver which I want to exisiting list in sharepoint.

    How would I do that ?

    Thanks

    • Moved by Mike Walsh FIN Saturday, December 18, 2010 9:36 AM Not programming (From:SharePoint - Development and Programming (pre-SharePoint 2010))
    Friday, December 17, 2010 7:17 PM

Answers

  • I have a couple of chapters on Access with WSS 3.0 (with MOSS 2007 it's the same) in my 2007 book so I've just gone to that for the following info.

    (If you have access via Safari it's pages from page 269).

    In brief there are three options starting from the Access table which are a) create a copy; b) create a new list; c) move an existing list.

    These are misleading terms !

    a) is probably what you have. You use menu item External Data and there to the Export part of the Menu area and click the SharePoint List link.

    Synchronization doesn't work.

    b) Create a WSS 3.0 List when in Access 2007.

    This too is misleading as this is the sole option of the three that start from Access 2007 that provides synchronization.

    When in Access 2007 ->  Create > SharePoint List.

    Synchronization does work

    c) Move an existing table

    This seems to be a clear description. The table is moved ("Move to SharePoint" on the far right of the External Data menu) .

    However the database table is now stored in the *SharePoint server* and so it can be accessed *there* by using Access 2007 and any changes made in Access 2007 can be "published" to the version of the database in the SP doc lib.

    But this won't be suitable for you as the location isn't your original location that gets the updates from SQL Server.

     

    I suggest therefore that you look closely at option b) - the badly named "Create a SP List"

    My Note on page 273 said "Full synchronization is acheived only if you start with an existing WSS 3.0 list and make an Access table from it (or create a new SharePoint list while in Access 2007, which has the same effect)."

     

    Hope this helps.

     

     

     

     


    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
    Saturday, December 18, 2010 9:26 AM

All replies

  • It helps to know which Access and which SharePoint in cases like this.

    There are ways to create a SharePoint List from an Access table which mean that the SharePoint List is synced with the Access Table. I.e. changes made in the Access table are reflected in the SP list. If your Access table is being updated from SQL Server than those changes should also (in that case) be synced to the SP list.

    BUT if you have used one of the methods that just create a SP list from an Access table (i.e. a List that isn't synced with the Access table) then you are out-of-luck and would need to re-do your list creation (using a method that will sync)


    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

    Friday, December 17, 2010 9:51 PM
  • I used MS Access 2007. In that I used "export to sharepoint" feature to create a list in sharepoint.

    Can you point to me an example that uses sync method?

    Thanks

    Saturday, December 18, 2010 5:06 AM
  • I have a couple of chapters on Access with WSS 3.0 (with MOSS 2007 it's the same) in my 2007 book so I've just gone to that for the following info.

    (If you have access via Safari it's pages from page 269).

    In brief there are three options starting from the Access table which are a) create a copy; b) create a new list; c) move an existing list.

    These are misleading terms !

    a) is probably what you have. You use menu item External Data and there to the Export part of the Menu area and click the SharePoint List link.

    Synchronization doesn't work.

    b) Create a WSS 3.0 List when in Access 2007.

    This too is misleading as this is the sole option of the three that start from Access 2007 that provides synchronization.

    When in Access 2007 ->  Create > SharePoint List.

    Synchronization does work

    c) Move an existing table

    This seems to be a clear description. The table is moved ("Move to SharePoint" on the far right of the External Data menu) .

    However the database table is now stored in the *SharePoint server* and so it can be accessed *there* by using Access 2007 and any changes made in Access 2007 can be "published" to the version of the database in the SP doc lib.

    But this won't be suitable for you as the location isn't your original location that gets the updates from SQL Server.

     

    I suggest therefore that you look closely at option b) - the badly named "Create a SP List"

    My Note on page 273 said "Full synchronization is acheived only if you start with an existing WSS 3.0 list and make an Access table from it (or create a new SharePoint list while in Access 2007, which has the same effect)."

     

    Hope this helps.

     

     

     

     


    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
    Saturday, December 18, 2010 9:26 AM
  • I guess I m confused where to create the list and then where to copy to.

    SQL server is my data provider and I m getting data using Access. If I first create list in SP, then how I m bringing the data in?

     

    Monday, December 20, 2010 3:54 PM
  • Are you a new guy or the original poster using a different account ?   (Note to all: if the Display Name is changed it will also change the display name on old posts so he can't just have changed the display name)

    If you are the original poster then I already told you about the one option that starts in Access and creates a sync'd SP List from an existing Access table.

     


    .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
    Monday, December 20, 2010 4:42 PM
  • Hey Mike,

    I m the original poster and not sure what made you think that I m changing names / ids.

    Here is the reason I asked you; your statements contradicts as:

    1- "b) Create a WSS 3.0 List when in Access 2007.

    This too is misleading as this is the sole option of the three that start from Access 2007 that provides synchronization.

    When in Access 2007 ->  Create > SharePoint List."

    2- "My Note on page 273 said "Full synchronization is acheived only if you start with an existing WSS 3.0 list and make an Access table from it (or create a new SharePoint list while in Access 2007, which has the same effect)."

     

    Monday, December 20, 2010 4:57 PM
  • Your first post is from Saqib Mahmood. The rest from Me.Saqib

    There is no contradiction. Read the second one again, it includes the sentence in brackets - (or create a new SharePoint list while in Access 2007, which has the same effect) - which is the same as what I wrote in 1.b


    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
    Monday, December 20, 2010 5:03 PM
  • Got you. Thanks. I will try and will update you with response.

    About changing the names, I think when you moved my post, the forums just put my name instead of my id.

    Monday, December 20, 2010 5:14 PM
  • I created SP list in access and I can see data in access now but now how do I insert new data from SQL into this list?
    Monday, December 20, 2010 10:57 PM
  • Change the data **in the table**. This will then be synchronised with the list.

    (This is all from me in this thread)


    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
    Tuesday, December 21, 2010 4:17 AM