Cool As Ice uses Office Live and Access 2007 with custom ribbon

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.

Clint

______

From 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.

Comments

• Anonymous
  March 06, 2007
  Hello, Access 2007 is highly optimized for win Vista? I make this question because from mine tests under win XP (pentium III, 512 MB of memory) I have found slow interface performances. Moreover, the "working set" of converted applications is much higher respect to Access 2003: why? Thanks

• Anonymous
  March 06, 2007
  Looking at Alan's overview: If the backend is in OfficeLive, where does SharePoint come in? Or is the backend in OfficeLive actually stored in SharePoint?

• Anonymous
  March 06, 2007
  Grovelli, Office Live is based on Sharepoint, but has some web UI applications already set up for you which you can use as is (in Office Live Premium). It's a sort of hosted Sharepoint. You can set up your own workspaces, which is what I did here, and get to the data in them either with a web UI or a client such as Access 2007.  Thus Cool As Ice have their own workspace within my company's Office Live site and all their interaction with the data is via Access 2007. It could be done all via a web UI, but Access 2007 gives it a better UI. Certainly, if you need to take data offline as they do, you need something like Access 2007 anyway.

• Anonymous
  March 07, 2007
  I should note you need Essentials ($19.95) or Premium ($39.95) for list services. Here is a link for more information. http://office.microsoft.com/en-us/officelive/FX101465131033.aspx One unfortunate thing to note, if you want to do RI in SharePoint/OfficeLive, you need to use custom code/macros on the form delete events to cascade the deletes. The native WSS platform doesn't support RI.

• Anonymous
  March 07, 2007
  The ability to store an Access database centrally and synch it - thus allowing offline access - is exciting. But I don't really understand how the local Access data is 'synced' with  Sharepoint - could you flesh this out a bit? Does sharepoint store Access database files? How does it work? Cheers, Chris

• Anonymous
  March 07, 2007
  Chris, It isn't an actual Access database that stores the data on the server but Sharepoint. This data storage in turn uses SQL Server, but you get no direct access to the SQL Server bits; you deal with Sharepoint as customised by Office Live (Clint might have a better way of explaining it). For all intents and purposes you can forget about the SQL Server bit. When you store you data in Sharepoint, it gets stored as a series of lists. To the developer they appear as a series of slightly cut down versions of normal tables. In Sharepoint, and therefore Office Live, you don't get referential integrity. In Office Live, at least, you also can't have more than 10 fields with indexes nor can you set up a unique index on any of the fields you set up (though there is a sort of Autonumber field available). So you can see there are some important restrictions (restrictive restrictions?). However, if you can work within those confines, you have some real possibilities for doing something rather good. For my customer, it means being able to store his data in Sharepoint/Office Live with easy access to it from wherever his people are and the ability to take the data offline and update it as and when he wants. It also means he can let Office Live look after backups (though he can also take local copies), he has a Recycle Bin, can be easily informed of any changes to the data via a system of built-in alerts, access to the online data is protected by Windows Live ID (aka Passport) and so on. Office Live is also cheap. Challenges for the developer (me) were mainly:

1. Supplying a unique ID that would work during replication (Office Live has a system, but with my app requiring 3 levels of relationship between tables (customer --> equipment --> service details) I found it best to generate my own hidden, unique field to link customer and equipment. I've used a random, Long Integer value. It may be that I missed working out how to get the built-in system to work properly, but at least I got mine working OK.
2. Supplying a button to kick of the synchronisation (calling from the ribbon turned out to be best). Ditto for Compact and Repair.
3. Setting up my very first ribbon (OK once you get going, really it is).
4. An issue with using an unactivated trial copy of Access 2007.
5. Finding that you can't do a mailmerge directly on offline data (I need to export to a temporary table instead), at least in this first release of Access 2007.
6. Writing my own cascading deletion function (again quite easy to do, but you do have to do it).
7. A check in code to avoid duplicates (or at least highlight them). However, a system using a form of replication is going to need this sort of thing anyway, so it is no great shakes. The synchronisation of data is not as extensive as that available via the old Jet replication, but what made it good for us was that we just bought into Office Live and had the system there, ready for us to use. This may sound a bit scarey and there is a learning curve, but it has opened up some real possibilities for me as a developer.

• Anonymous
  March 07, 2007
  Alan, the screen shot with the ribbon in drop-down mode shows a Synchronise Data button. What does that button do? Does it launch a mixture of VBA/SQL that you've prepared yourself or does it also involve the synchronisation provided by Office Live?

• Anonymous
  March 07, 2007
  The comment has been removed

• Anonymous
  March 08, 2007
  On the mailmerge side, I've found that you can mailmerge offline data using the wizard in Access 2007. Though you don't seem to be able to do it from within the database holding the offline data (A.accdb), you can do it if create another, empty database (B.accdb) and link to the tables in the original database (A.accdb). You can then run the mailmerge wizard in the second database (B.accdb). It's a bit of a bind, but bearable.

• Anonymous
  March 10, 2007
  Thanks for the comments Alan. Couple comments... The ribbon has a far easier extensibility model than  using and application.commandbars or even doCmd. If there is a ribbon command on the ribbon all you need to do is use the idMSO in your ribbon XML. Something like <button idMso="ExportExcel" label="Export to Excel" size="normal"/> Internally, we will track the enabled and disabled state for you. You can find the list of all controls at the following download (I'm not sure what the name is for sync...). http://www.microsoft.com/downloads/details.aspx?familyid=4329d9e9-4d11-46a5-898d-23e4f331e9ae&displaylang=en Let me explain how the data is cached. SharePoint and Office Live expose all  lists as web services. Access has an ISAM that connects it with those data sources via the web services. When the application is taken offline a couple things happen. First we prompt to save a local copy of the database if you opened it read only from a SharePoint document library. Second, we take a snapshot of all the link tables and save them locally in the database. There are internal flags that track the updates, deletes, and additions. We also track the time stamp of when the database came offline. When the user syncs we call a SharePoint web service method called GetItemsSinceLastChanged(). This returns all the changed records. These changes are merged with the local changes and local changes are pushed to the server. We did lots of work to make sure whenever connecting to the server that we use the GetItemsSinceLastChanged to reduce the size of data that has to traverse the internet or network. Alan did report a bug to us where you can't do a mail merge when offline. The problem is offline databases are always opened exclusive. This lock is preventing Word from getting to the data. We are looking at different options for SP1.

• Anonymous
  March 10, 2007
  The comment has been removed

• Anonymous
  March 10, 2007
  The comment has been removed

• Anonymous
  March 10, 2007
  Clarification on using synonyms. At the end of my last post I mentioned that all the tables from A.accdb appear at the end. I should have pointed out that you can only actually use ones you have linked to from B.accdb. Thus in my instructions, I said to link one table. The end result of doing that would be that only this table is usable in the mailmerge. If you want more, link to them all from B.accdb.

• Anonymous
  March 10, 2007
  Mailmerge again. If you start a mailmerge from Word 2007 and use ODBC there is no need for a linked database, i.e. you can get directly into your offline data database. Much easier!

• Anonymous
  March 10, 2007
  Alan, how are you coping with the lack of Referential Integrity?

• Anonymous
  March 11, 2007
  The comment has been removed

• Anonymous
  April 24, 2007
  Here is a great article by the Wall Street Journal about SharePoint adoption and scenarios where people

• Anonymous
  May 29, 2007
  I saw this and thought you might like to see how one ISV has joined Office Live and an Access application

• Anonymous
  March 10, 2008
  Recently our friends over in Office Live have had major announcements about both the Office Live Workspace