locked
Link an Existing Access Database to a SharePoint List RRS feed

  • Question

  • I have an Access Database that was created by using the "Create linked table in Access" function from SharePoint. It is working as required. I want to copy the Access 2003 database and link it to a different (identically structured) SharePoint list so that some developers can "play" with the data without affecting our production version.

    I tried using the the Linked Table Manager function in Access to link to the new list. When I'm asked to select the new location, I can navigage through the Windows Explorer-like GUI to the folder where my SharePoint site is hosted, but I cannot find the list to link it to - as I don't believe one can actually access a SharePoint list through the Windows Explorer GUI.

    Is it possible to link an EXISTING Access database to a SharePoint site? I know it is easy to do when done from a NEW Access database.

    Any help in this regard would be appreciated.

    Thanks,

    Dave

    Wednesday, November 10, 2010 5:06 PM

Answers

  • Hi Dave,

    Thanks to share your problem.

    To start the wizard (in Access) ,Click the External Data Tab->Click the SharePoint List icon in the group->Then you are required to enter the URL of the SharePoint site and indicate whether you would like to import the list data or create a link. In this case: Enter the full URL to your SharePoint Site, Click the Link to the Data Source by creating a linked table ->Then next, select the list required, click OK. Also note the new item in the Access 2007 status bar located in the bottom right corner->Clicking the button opens a context menu that will permit you to: Cache the List data locally, Work Offline.

    If you choose to Work Offline, the online link between Access and the SharePoint list is removed with your data now existing independently in Access Note how the linked list icon changes to reflect the status of the list.

    Now that the list is offline: Open the list ->Add a new Record using Access.

    Once you have added the new record you will need to synchronize the changes to SharePoint:

    On the Status bar click the button titled Offline with SharePoint ->Select Synchronize.

    Access will now update the SharePoint list with your new records!

    Another link which I think would also give you a proper reference:

    http://office.microsoft.com/en-us/access-help/import-from-or-link-to-a-sharepoint-list-HA001230313.aspx.

    Best Regards!

    Leo

    • Marked as answer by Leoyi Sun Friday, November 12, 2010 10:17 AM
    Friday, November 12, 2010 4:17 AM

All replies

  • > Is it possible to link an EXISTING Access database to a SharePoint site?

    A Table in an existing Access database (2007 or 2010) Yes. Most field types are transferable to a SharePoint List.

     

    Note: Please always say which version of Access and which version of Sharepoint when asking such questions.


    2010 Books: SPF 2010; SPS 2010; SPD 2010; InfoPath 2010; Workflow etc.
    2007 Books: WSS 3.0; MOSS 2007; SPD 2007; InfoPath 2007; PerformancePoint; SSRS; Workflow
    Both lists also include books in French; German; Spanish with even more languages in the 2007 list.
    Wednesday, November 10, 2010 5:27 PM
  • Thanks for your response Mike. Not a big deal, but I actually did state in my post that I'm working with 2003 (in the second line). So, I guess you're saying that it's only possible in 2007 and 2010.

    Dave

    Wednesday, November 10, 2010 5:31 PM
  • Hi Dave,

    Thanks to share your problem.

    To start the wizard (in Access) ,Click the External Data Tab->Click the SharePoint List icon in the group->Then you are required to enter the URL of the SharePoint site and indicate whether you would like to import the list data or create a link. In this case: Enter the full URL to your SharePoint Site, Click the Link to the Data Source by creating a linked table ->Then next, select the list required, click OK. Also note the new item in the Access 2007 status bar located in the bottom right corner->Clicking the button opens a context menu that will permit you to: Cache the List data locally, Work Offline.

    If you choose to Work Offline, the online link between Access and the SharePoint list is removed with your data now existing independently in Access Note how the linked list icon changes to reflect the status of the list.

    Now that the list is offline: Open the list ->Add a new Record using Access.

    Once you have added the new record you will need to synchronize the changes to SharePoint:

    On the Status bar click the button titled Offline with SharePoint ->Select Synchronize.

    Access will now update the SharePoint list with your new records!

    Another link which I think would also give you a proper reference:

    http://office.microsoft.com/en-us/access-help/import-from-or-link-to-a-sharepoint-list-HA001230313.aspx.

    Best Regards!

    Leo

    • Marked as answer by Leoyi Sun Friday, November 12, 2010 10:17 AM
    Friday, November 12, 2010 4:17 AM
  • Thanks Leo. I'm actually running Access 2003. However, I believe I was still able to do what you suggested and link to my new table. Maybe this is something I need to post in an Access forum, but all my queries point to the original "production" site and not to the "test" site. Is there an easy way to change all the queries to point to the test site? Unfortunately, the queries are not written in VBA but I just used the Access GUI to create them.

    Any direction you're able to provide would be appreciated.

    Dave

    Friday, November 12, 2010 1:22 PM