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 (19)

  1. Zen says:

    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

  2. grovelli says:

    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?

  3. Alan Cossey says:

    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.

  4. Clint Covington says:

    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.

  5. Chris Bartley says:

    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

  6. Alan Cossey says:

    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.

  7. grovelli says:

    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?

  8. Alan Cossey says:

    Grovelli,

    You can use the built-in ribbon command of SynchronizeData or you can call DoCmd.Synchronize. I use the latter as Cool As Ice run the application offline. SynchronizeData tries to synchronize even if you are not connected to the internet. I get the app to check that it is indeed connected to the internet then closing all forms and reports before actually attempting to synchronize using DoCmd.Synchronize. Another way, which I have not tried properly is to use application.CommandBars.ExecuteMso("SynchronizeData").

    I check there is an internet connection using

    Private Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRefdwflags As Long, ByVal dwReserved As Long) As Long

    with the following in a function IsOnline

    Dim LFlags As Long

    IsOnline = InternetGetConnectedState(LFlags, 0&)

    So, one way or another, it uses the built-in Office Line/Sharepoint synchronization. No SQL from me here.

  9. Alan Cossey says:

    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.

  10. Clint Covington says:

    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.

  11. Alan Cossey says:

    Grovelli and Clint,

    I was overly complicating things in my stuff about DoCmd.Synchronize and so on. When I did my testing earlier I am sure the app hung if I was not already connected to the internet or had a form open. Hence my blurb about using that API to check whether the app was connected or not (I also closed all forms and reports first). That is why I didn’t use the built-in idMso as in <button idMso="SynchronizeData" size="large"/>. However, on trying it again now with the built-in version, it fails gracefully if there is no internet connection and works if there is a connection, i.e. it acts as you would hope. I must be cracking up. I’ll change my app to use <button idMso="SynchronizeData" size="large"/>.

    Thanks for the background info on how the synchronization works, Clint.

  12. Alan Cossey says:

    Here are a couple of work arounds for Word mailmerge. The first requires you to be online to use your offline data but the second does not.

    Let’s assume our offline data is in A.accdb and in B.accdb we connect to a couple of the tables in A.accdb using linked tables. Using Office Live as the Sharepoint source…

    1) As long as you have an internet connection you can start the Word mailmerge wizard off in B.accdb. However, it prompts you for your Windows Live ID and password before it lets you progress. This may or may not be a useful way of doing it.

    2) If you are not connected to the internet, you can still do it. In Word 2007 (don’t know about previous versions),

    Create a new Word document.

    Click on the Mailings tab.

    Click on Select Recipients in the ribbon.

    Select "Use Existing List" and then, using the file dialog that comes up, find B.accdb (not A.accdb). You will be presented with an error message saying, "This data source contains no visible tables." Don’t be put off. Click OK.

    You will be presented with another error message saying that Word can’t find A.mdb (even though you selected B.accdb. A.mdb doesn’t exist anyway though A.accdb does). Click OK.

    You will now be presented with a Log In dialog box. Click the Database button and select B.accdb. Click OK then OK again.

    You will now be presented with a dialog box saying Select Table, but won’t see any tables.

    Click the Options button. You should see that just the Tables checkbox is already ticked.

    Tick the Synonyms checkbox then click OK.

    Voilà. Now you should be able to see all the tables in A.accdb (not just the one you linked to from B.accdb). You should now be able to use the data from A.accdb.

  13. Alan Cossey says:

    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.

  14. Alan Cossey says:

    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!

  15. grovelli says:

    Alan, how are you coping with the lack of Referential Integrity?

  16. Alan Cossey says:

    Grovelli,

    Please bear in mind that my experience of Office Live is still rather limited, so there may well be better ways than doing things the way I have done so far. It is still Beta here in the UK and that only became available in November last year.

    Disclaimer over so….. referential integrity is normally something you take as bog standard in a database. If I was using the Office Live front end for my database I would struggle unless the database consisted of only one table, i.e. there was no need for referential integrity. As things stand, I don’t see the built-in Office Live UI as being suitable for a complicated database. I don’t think Microsoft are claiming it is. As far as I know there is no means for updating child tables within Office Live when, say, a record in a parent table is deleted or updated, i.e. no cascading deletions or cascading updates. This is a deficiency. Maybe in that case you should not use Office Live.

    However, where data is only handled only via Access 2007, as in my case, things are manageable. Firstly, I have made sure none of my tables have updateable primary keys, e.g. if there is a table with company details in it and a related table with equipment for that company, the link is via a unique, non-modifiable number rather than the company name. That way, if the company name changes in the parent table, no other data needs to change in the child table (gone is the need for a cascading update). In a "normal" Access database, you could just use an incrementing Autonumber field as the primary key in the company table to do this. However, things are a bit more complicated when using Office Live as the back end if you want to be able to maintain data offline as I do. In Office Live you can only have a unique index on the system-generated ID field in any table and, when using data offline, this number is generated differently from when you are online, i.e. when running online, the ID numbers are incrementing positive numbers and don’t ever get altered, but when generated offline, the first record has an ID of -1, then the next record is -2 and so on. When synchronized, they are converted into positive numbers. With the Cool As Ice database there are three main related tables – company –> equipment for that company –> service details for that equipment. Using the system-generated ID field as the unique identifier for each record in each of those tables, it did not synchronize properly, i.e. I got left with some records without parents. To overcome this, I added another field to the company (parent) table which is based on an Autonumber field with its New Values property set to random, thereby allowing the creation of up to ~4 billion random numbers as it is of data type Long Integer, i.e. this is effectively going to guarantee uniqueness. If that is not a large enough number for your database you could presumably use Replication ID instead of Autonumber (even though replication is gone from .accdb files). When creating a new record in the equipment table, I use that value from the company table as the foreign key value and use that field to link master form and subform. As it happens, I create these new numbers in a special table in my Access front end and push them into the relevant fields in the three tables via a simple function. I did this because I have been creating my tables and fields in Office Live and didn’t see how to create the equivalent of an Autonumber/Random field there.

    So to sum up, I have to use a substitute "primary key" where I create the uniqueness similar to the manner used in the old Jet replication days.

    The other problem with uniqueness is that you can have two company records with the same Company Name even though their primary keys (ID and/or my random no. field) are different, e.g. you could have two records showing details of Blogit and Son Ltd. Actually, if you had a unique index on that field (which you can’t with Office Live), you would have to set up a means of resolving the conflict when synchronizing (which you can’t in Office Live, other than using the fairly basic conflict resolver) in the case where such a duplicated record was added offline offline. My customer and I are happy for them to run a check at a time of their choosing to highlight such duplicate records and act on that.

    Lastly, when deleting a record in a parent table, you need to ensure that child records get deleted too. That is fairly easy VBA stuff.

    The end result is that if your database design is not too complicated you need to handle updates, deletions and duplicates a bit differently, but the end result is that you can handle data offline in read-write manner over the internet. For my customer, that was what they were after and, in this case, Access 2007 and Office Live was the basis of a good solution.

    Even more of a last point. Don’t create tables in Access and call any of the columns "ID" as that is the name used for the system-generated column in Office Live. If you do use such a name, one gets rename as "_ID" and it gets confusing.

  17. Here is a great article by the Wall Street Journal about SharePoint adoption and scenarios where people

  18. I saw this and thought you might like to see how one ISV has joined Office Live and an Access application

  19. Recently our friends over in Office Live have had major announcements about both the Office Live Workspace