Tuesday, November 17, 2009

Moving Access Views in SharePoint

Each year our company creates a new site within Sharepoint which acts as a depository for all of the current years data. Blank custom lists also moved from the previous year site to the current year site. One issue we run into when re-creating these blank custom lists is it breaks the links in the Access Views we have created for the list.

Here is the procedure for re-linking the Access Views so they display accurate information. (This procdeure assumes you are using Microsoft Access 2007)

  • The Access View points to an Access Document in a Document Library on your SharePoint site. Find the specific Access document and move it to the appropriate Document Library.
  • Open the newly moved Access document in Edit mode.
  • Once Access it open, Right Click on one of the List Tables listed under the All Tables. Select Sharepoint List Options.
  • In Sharepoint List Options, select Relink Lists. (NOTE: Relink Lists can also be found by clicking the External Data Tab)
  • Type in the name of the New Sharepoint site where you new Lists are located. Click Get lists.
  • It may take 30 seconds or more for the new site lists to show. Be patient!
  • Once your new lists are displayed, match the Original site link tables to the new list. Do this on for all of your lists. Click OK. (Again, this may take a minute or more for everything to take effect)
  • You will now need to clean up the Table (Lists) titles under the All Tables. Save and rename any table that ends in "1". These are the lists that were just linked.
  • Right click any list that you don't need and select Remove. This only removes the link, not the table data.
  • Don't forget to go into the report and adjust any Report Titles etc.
  • Click "Publish to SharePoint Site" to save your changes to the server.
  • Select where you want to save the report and it's name.
  • You will be prompted with a pop up that states "Do you want to be able to add shortcuts to each list's View Menu?" Select Yes if you want the report to show up in your List Views.
  • You may receive an additional pop up that states "You have selected "Tables and Related Views" as the way to group objects in the database. Access needs to update information on the object dependencies to create the groups. This will take some time for larger databases". Select OK to Continue.

No comments: