Hi,
Recently, I've been hearing more and more requests for a utility that takes SharePoint lists and creates a SQL Server database to represent it. Usually, the desire is for a tool that would export the data from one repository to the other without having to deal with conflict resolution or updating. Sounds like a legit need, isn't it? Well, guess what - you already have a utility for doing this. Even two to be exact. Both of them reside well within the Office 2007/MOSS 2007 synergy:
Data Sheet View – A very handy view that MOSS/WSS supports which basically uses familiar Excel look & feel to edit and view List data. Once the list is opened in this manner, you can perform all sorts of little tricks such as multiple updates or multi selecting rows (Oh, and of course you can open the data in Excel itself and do some harder magic). The major advantage of this view is that SQL Server Tables/Views comes with full support for copying data while maintain the correct structure from datasheets such as Excel (or Excel view in our case). This gives us a very quick solution for the Export problem – just open the list in "Edit Data sheet view" and do copy and paste directly in SQL Server. Nice and Slick and you're done. However, this is mainly for a one time deal since doing it over and over can be a bit of a hassle? Need something more Nice & Slick – skip to the next bullet…
- Access 2007 - MOSS is widely known for the Word and Excel integration but I feel not enough is said about the fantastic Access 2007 integration. You may not know that Access 2007 supports opening WSS Lists as Generic Access Tables with full support to everything Access can do to manipulate and display data/reports. This is HUGE guys since this feature supports many capabilities that you'll fail to find in the Excel 2007 synergy (For Example: Read/Write synchronization to all of the WSS Lists data). Now, for the export to work, we can use another cool feature of Access having to do with creating a Linked Table. Linked Tables can use SQL query to read data from one table and insert into linked table in the Access Database. When you combine WSS Integration with Linked Tables in the same DB file – you can create a very powerful tool for continuous synchronization between SQL tables and WSS Lists (that we're opened as Access Tables). A utility that was created by Access 2007 – no single line of code needed. Oh,and if you're on the way - be sure to check all the other amazing stuff you can do with Access as your front end and WSS as your data repository. Think of it as a brand new Access Server or Access Services – hell, if Excel got a sexy name for his server features, why not Access?
Bye,
Adir Ron.