SharePoint and the Access Database

So the England (and Wales with a hint of South African) cricket team have regained the Ashes from Australia and all is right again in the world. Well, it will be if the Villa can finally score their first goal of the season. Although, hopefully by the time you are reading this we will have found the net a few times hopefully against Liverpool. With my lack of Sky Sports I spent much of yesterday following the final day of the Test Match watching the cricket on the ‘Text’ page off BBC One. Just waiting for the number of wickets to change and fearing the worse that Australia could score a world-record final innings score. I was impressed at how quickly all the many details on the page were updated and even started to think about if they could be using SharePoint to display these results ( I know I ought to get out more). But they could have been using SharePoint and perhaps even Excel Services to produce the nice graphics. With Office 2007 it is very easy to publish live data to SharePoint both from Excel and also from the under used Access databases. So why go to the effort of linking your Access database to your SharePoint? There are a number of advantages of publishing to SharePoint and I have listed these below; 1. Users can work with the data either in a SharePoint List or their own Access application. 2. They can use the features of a SharePoint site to manage the data and stay updated with changes. 3. The Administrator can use SharePoint to manage permissions and versions so that you can see who changed it or restored previous data. 4. If you are collaborating with others, you can store a copy of a database in a library on a SharePoint server and still continue to work in the database by using the forms and reports in Access. 5. If you want to view or print an Access issues report for a monthly meeting, you can do it directly from the SharePoint list. 6. You can open a SharePoint list in Access. If a database doesn't exist, you can create it in Access and then create a set of forms and reports based on your list. 7. Take SharePoint lists offline with Access. If you need to take some work home with you (especially now the cricket has finished), you can take your SharePoint lists offline with just one click by using Access. You can then work on your data at home in Access and then synchronise your changes or reconnect with the SharePoint site when you are back in the office. So how do you publish an Access Database to SharePoint? When you publish a database, you can start with an existing database or build a database by using the lists on the SharePoint site. 1. Click the Microsoft Office Button, point to Publish , and then click Document Management Server. 2. Type the URL of the SharePoint site where you want to publish the database. If you used the same location the last time you opened Access, the database appears in the Publish to Web Server dialog box. NOTE - This option is available only if your database is saved in Office Access 2007 format. 3. Select the library, such as a document library, where you want to publish the database, and then click Open. 4. In the Name box, type a file name for your database. 5. Click Publish. NOTE - When you open the database from the SharePoint site, you are prompted to save a local copy. It’s all very easy to do, now if only it was that easy to score a Premier League goal. Up the Villa. With the 2009/10 Premier League season up and running I have started filling in my ‘Villa Performance Season 2009/10’ SharePoint site (unfortunately with no goal scorers) . Don’t forget that the free template I have developed for any supporter of an English Premier League team to record all their teams games, players and goals next season is still available (extended due to popular demand to August 31st 2009). So if you want it drop an email to me at andy.dale@office-talk.com with the subject being the team you support. It is free unless you support Man City then it will cost £1000.