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.

To use InfoPath on SharePoint or not that is the question

We all make mistakes and sometimes we have to change our first impressions. This happened to me twice last week. Firstly, after all my moans about the Villa manager’s lack of transfer activity over his summer holidays I discovered how good the crop of youngsters from the title winning reserve team last season really were. As they sensationally won the global tournament ‘The Peace Cup’, which included teams like Real Madrid, Juventus and Porto. Then on the SharePoint front I had to change my mind about using InfoPath in SharePoint. Although, I could happily spend the next ten minutes describing the achievements of the talented Villa youngsters I think most of you are probably more interested in the Forms in SharePoint argument. I have always tried to stay away from InfoPath Forms with SharePoint because most companies do not have InfoPath as a client application. It comes as part of Microsoft Office Enterprise Edition but is not included in the more common ‘Standard’ or ‘Professional’ versions. Instead I have tended to use the basic Custom Lists inside SharePoint to very quickly design online forms. So what are the advantages of using InfoPath in SharePoint instead of just SharePoint Forms? I will list a few that I have found; 1. Copy layouts/text straight in from Word or Excel 2. Produce a nice looking printable form (that you can sign) 3. Take fields from multiple data sources (including SharePoint lists from different SharePoint sites). 4. Fields integrate perfectly with your metadata in SharePoint 5. You can easily add validation and logic to fields. 6. Can easily add increased formatting of how data is displayed (more date options and does better number format options). 7. Some powerful wizards and for once actually useful templates (the expenses one is nearly useable). 8. The InfoPath forms are very portable and can be easily added to Content Types. 9. Forms can be published so it can be viewed in the browser for people without InfoPath. So why was I so anti InfoPath in the past and an ‘infopathist’ (thought I would invent a new word)? Well, I can still think of a few reasons to use SharePoint Forms instead; 1. They are easier to create as they have less fiddly options. 2. Nobody needs to have InfoPath installed (if you view InfoPath through the Browser somebody stills needs InfoPath to create and manage the forms). 3. Every individual change involves re-Publishing the form. 4. The inbuilt design error checking can drive you mad. 5. Forms are slower to load. So suddenly I have decided that the benefits outweigh the drawbacks. In much the same way that the Villa new signing combined with the terrific youngsters (I realised that a lot of the team are now half my age which is scary) more than compensate for the loss of Gareth Barry and Martin Laursen. So if you do use SharePoint (either MOSS or WSS) then I recommend you purchase one copy of InfoPath and start creating forms to publish to your SharePoint. The available free Microsoft templates include Expenses, Travel Request, Purchase Orders and Absence Reports. But Office Talk have already created for their customers Exit Interview Questionnaires, Job Appraisals, Claims Forms and IT Helpdesk Requests. If you ask us for a quote we will happily create one tailored to your needs for you. Now, the new Premier season is just three days away and with the rumour that Villa are about to sign the Turkey captain Tuncay from Boro I have my ‘Villa Performance Season 2009/10’ SharePoint site ready to fill in. Don’t forget that the free template I have produced for any supporter of an English Premier League team to record all their teams’ games, players and goals next season is only available until August 21st 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.