Manipulating SharePoint Lists with Microsoft Access 2007

Manipulating SharePoint Lists with White Paper Microsoft Access 2007 document library with the contact spreadsheet, CONTENT Overview grab the latest copy of the spreadsheet and look up the person’s contact information.” Overview SharePoint 2007 is a great platform for storing all sorts of information. SharePoint makes it very There were several problems with this approach. Challenge easy for users with the proper credentials to create The two document libraries and the spreadsheet many lists and document libraries without any Approach had completely disconnected data. You could have thought towards organization, duplication of data, someone in the contact list that you did not have a Wrap-Up or site standards. resume for. You could upload a resume with So what happens when you have data that needs to remembering to add that person to the contact list. be moved around or combined with more than one You could edit the contact spreadsheet and list? SharePoint does not provide a simple way to accidentally save a different version of it to the accomplish this. document library – now, which one is correct. I’m sure you could find some other issues with the Challenge current approach. “SharePoint is a great I recently came across a SharePoint site used for platform until users get recruiting. It consisted of a document library that Approach themselves in trouble“ contained spreadsheets with lists of contact My suggestion to the primary user and business information for candidates. In another document owner of this site was to: library was a collection of resumes for those same 1. Add metadata columns to the resume candidates without any metadata columns. The document library to hold the contact resumes were stripped of any contact information information for each resume. since they were used to submit to other clients. In both cases, these two document libraries only 2. Transfer the contact information from the stored the contact spreadsheet and resumes - no contact spreadsheet to the new metadata other content fields in the resume document library. So I asked “What’s the procedure when you have 3. Remove the document library that held the a new candidate?” The answer “Well, we upload contact spreadsheet the resume to the document library, then check out With this approach, the users of the site would the spreadsheet from the other document library, have one location for the resume and the contact add a row with the person’s information, then information. When the list of resumes was listed upload it and check it back in to the site”. So on the site, the user would have the contact begged another question “What do you do when information right there without having to take any you find a resume of someone and need to contact additional steps to find/update the contact them?” The answer to that question was “Well, information. while viewing the resume we go back to the Manipulating SharePoint Lists with White Paper Microsoft Access 2007 About the Author MS Access 2007 Solution 6. Before I could create a query that joined the David Gilbert has over 20 years There was no way to do this via the traditional two lists, I needed to populate the First and experience in Information Technology and is a specialist in application SharePoint interfaces and although I could write a Last Name fields of the resume document architecture, project management, fairly simple Visual Studio project to move the library so I would have something to join on. internet technologies and relational databases. He has focused on the data around, it would still be too involved and be I created an Update query (“Create”, “Query application of technology to business Design”) that set the First Name and Last functions in the areas of manufacturing, throw-away code. telecommunications, financial services, Name fields based on the document name insurance, supply chain and commercial Instead I used the new integration features of software. His technical strengths include (with a little string manipulation). I ran the Microsoft Access 2007 to SharePoint to make this SharePoint, SQL Server, Oracle and query and the name fields in the resume web-based systems. happen. Here are the steps I followed: document library was updated. He can be reached at 1. I found out two very import things from the http://www.linkedin.com/in/davidrgilbert 7. With the Last and First Name fields primary user of the site: populated in both lists, I then created another a. Where the current contact spreadsheet is Update query. I added the candidate list and b. The naming standard they used for the resume library as tables and then joined them resume filenames via first and last name. I set the metadata fields in the resume library to the values from c. The decision we came to was that the the candidate list. I ran the query and resume only way the two things could be “MS Access 2007 is more library was updated. connected was the person’s first and last than just a simple database name, At this point, the resume document library had tool” everything I needed, so I deleted document library 2. I created a new MS Access 2007 database to that contained the contact list spreadsheets. I also hold all my work. created some new views on the resume document 3. I imported the contact spreadsheet into the library to make viewing the information. new Access database as a table via “External Data”, “Excel”. I called this table Wrap-Up “Candidate”. This table would contain all the MS Access 2007 is a great tool to use with metadata to populate the resume document SharePoint for not only data manipulation, but you library. can also create, forms, reports and anything else 4. I connected the resume document library to you can do with a local database table against a the Access database via “External Data”, SharePoint list or document library. “SharePoint List” – make sure you link the list and not create a copy. 5. Now everything that I needed was in a single Access database.