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

Pushing Access data to the web: Integrating with Office Live Small Business

Access Team Blog — Mon, 10 Mar 2008 21:19:25 GMT

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

Clint Covington: Software design, Microsoft Office Access : Cool As Ice uses Office Live and Access 2007 with custom ribbon

David Overton's Blog — Wed, 30 May 2007 02:29:07 GMT

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

SharePoint Takes Lead in Collaboration Market - WSJ article

Clint Covington: Software design, Microsoft Office Access — Tue, 24 Apr 2007 23:15:50 GMT

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

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

Alan Cossey — Mon, 12 Mar 2007 12:22:19 GMT

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.

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

grovelli — Sun, 11 Mar 2007 14:26:42 GMT

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

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

Alan Cossey — Sun, 11 Mar 2007 01:25:04 GMT

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!

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

Alan Cossey — Sun, 11 Mar 2007 01:05:18 GMT

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.

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

Alan Cossey — Sun, 11 Mar 2007 00:59:12 GMT

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.

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

Alan Cossey — Sun, 11 Mar 2007 00:35:21 GMT

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.

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

Clint Covington _MS_ — Sat, 10 Mar 2007 20:21:58 GMT

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

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.