Alan Cossey recently sent me a overview of how Cool As Ice is using Office Live and Access to track process information. He has found a couple bugs we will fix in SP 1 but overall it is working nicely for them. I thought some of you might appreciate seeing what others are doing with Access 2007. If you have an interesting scenario that you would like featured on my blog–feel free to send me an email.
Nice work Alan.
Here is some information about the Access 2007 application that I’ve developed for Cool As Ice, which is a leading Norfolk-based supplier of air conditioning and refrigeration here in the UK. Cool As Ice wanted a bespoke customer contact system that they could use to hold data securely yet allow their workforce to use that data on their laptops, including adding and altering data, i.e. not just a read-only copy. Access 2007’s integration with Sharepoint meant it could provide the basis for a good solution.
The application holds its data in a workspace that is part of my company’s Office Live site. The choice of Office Live to provide the SharePoint part of the solution means that their data is secure and that Cool As Ice do not need to get involved in organising that part of the solution, concentrating instead on the things they do best. Backups of data are done by Office Live and of course, the Office Live Recycle Bin is there in case it is needed. If neither of these needs to be used, everything is handled in Access 2007, i.e. the normal end-user just sees Access 2007 and no web user interface.
The image below shows the main screen of the application, i.e. where the main customer contact information is entered.
The application uses a ribbon, with it shown neatly tucked away in the above screen shot. However, most of the main actions in the database are called from it as can be seen from the next shot where it has been single-clicked to show it in drop-down mode.
Included in those actions is the ability to kick off the synchronization with the back end in Office Live. Clearly, the use of the application on laptops requires the use of offline data, but even in the office the system is run with the data offline and sychronized at an appropriate point, e.g. the end of the day. As you have pointed out already in your blog, using offline data allows for very quick data handling. When it comes to time to do the synchronization, even with about 3000 customer records already in the database the database synchronizes in under a minute on broadband.
A useful part of the Office Live integration is that it allows the use of Windows Live ID to provide the security of the data in the Office Live database. Thus when it comes to time to do the synchronization, clicking the Synchronize Data ribbon icon requires the user to provide their Windows Live ID and password and then off it goes.
You may have noticed in the first screen shot that there is a Notes field that enables users to record notes in rich text format rather than the old plain text of previous Access versions. You will also see from the next screen shot that use is made of Access’ ability to use subdatasheets. A bit more planning than usual is required to ensure correct updating and cascading deletions in the application, including with subdatasheets, but it is certainly “doable” as you can see from this solution.