Wednesday, August 28, 2013

Excel 2013 and Power Pivot

Getting Power Pivot

Power Pivot was introduced as a free, downloadable add-in for Microsoft Excel in version 2010. In Excel 2013, the way to get Power Pivot became complex, difficult and murky. This has improved. +Bill Jelen details in this video, how you can buy or subscribe to an Excel licence that has Power Pivot


But why would you want Power Pivot? What does it do? It brings the relational model to Excel.




Saturday, August 17, 2013

Microsoft Access 2013 - No Can Does

Microsoft Office 2013 has been a sleeper version. Whilst Office 2007 created an impact by implementing the Office Ribbon User Interface and Office 2010 has become the version that corporates, educational institutions and governments embraced, Office 2013 (or Office 365) has been relatively quiet. So quiet that I have not encountered clients who run it or want to discuss version specific functionality. So Office 2013 has been sleeping on my machines.

In my first runs with Access 2013, I encountered the first and major no can do - it would not open the famous Access 2000 vintage NWIND.MDB that I have used for years to introduce, demonstrate and train people. Documentation on the web gives even more no can do-es. So, take a seat, maybe a stiff drink and let's see what Access 2013 does not support any more.

Access 2013 does not open or import the classic Access .mdb.

  • Access 2010 and 2007 supported .mdb well - even those secured by Workgroup Security. And all previous Access versions would import older versions of Access database files. Not this one.  You need to convert the .mdb to .accdb in Access 2010 or 2007.
  • In Microsoft's words: "the Jet 3.x IISAM driver is no longer available." The last version of Jet 3.x was 3.6 that I encountered with Access 2003.

Access 2013 does not create web databases (.accdb stored on Sharepoint Server)

Well, that was a short life. Access 2010 introduced the idea of creating Access databases that could be deployed to Microsoft Sharepoint servers and interacted with using a web browser. Microsoft has abandoned that idea. 

Access 2013 does not support .adp files

.ADP files - Access Data Project files were introduced in Access 2000. At that time, the idea was that Microsoft Access had a successful and admired Forms and Reports facilities - the Tables and Queries too, were not half bad in terms of user interface. So they removed the native Access JET engine and glued on a thin framework to use the Access screens with the SQL Server back end. Unfortunately everyone realised that more than half the beauty of Access was the intelligence and close integration of JET with the Access screens - without JET, .ADP files were weak in robustness and power. Microsoft has been walking away from that idea since Access 2003, so no tears to be shed.

Access 2013 does not support PivotCharts and PivotTables natively

When I first saw PivotTables grafted into Access 2003, I was quite excited, having seen the success of PivotTables in Excel over several generations. Unfortunately, PivotTables didn't "fly" - they were sluggish in performance, they didn't behave or produce results like genuine Excel PivotTables did and I never used that feature. No loss.

Access 2013 does not support dBase .dbf files

This feature is not regretted except for nostalgia. When Access 1.0 was born, the Number One desktop database was Ashton Tate, later Borland dBase III and dBase IV. dBase was a veteran product famous from the era of dBase II on early CPM-80 computers (even Apple ][s) and ushered the age of relational or pseudo relational databases onto personal desktop use. From those birth days, dBase market share plunged as it failed to dominate and excel in Microsoft Windows (dBase was extra premium cost, Access was bundled free in the Office Pro Suite). Of course in those days, Access had to be able to import from .dbf files but dBase as a commonly used program disappeared years ago.

Access 2013 does not support Data Collection teamwork with Microsoft Outlook

In Access 2003 or earlier, Microsoft had the idea of having an Access database, then using Microsoft Outlook to send out the data entry form, the recipient would key in one record and respond by replying to the sender. The sender's Outlook would detect the response and working with Access, route the data into the Access database. A sweet idea of hop-skip-and-jump and not needing corporate IT support for this action. Except that not the world is dominated by the internet and people not using Outlook to send or respond. I took this for training and demo but did not see it used in production. 

Access 2013 does not support Database Replication

In the time of Access 97, the PCs were generally standalone and disconnected. Web apps were not as common. As a hop-skip-and-jump idea, Microsoft designed quite a sophisticated replication system so that disconnected PCs could accept data entry and then the files could be exchanged and merged. The level of complexity in the idea and in the implementation defeated users.

Access 2013 does not support Source Code Control

Some corporates and third party vendors did use Microsoft Access for multi-person team programming. With the proliferation of different programming tools, .NET apps, web apps, it is likely that this feature may not be missed.

Access 2013 does not have an SQL Server Upsizing Wizard

Access 97 or earlier offered an SQL Server Upsizing Wizard to help people migrated tables and queries to Microsoft SQL Server. A good idea but of course the differences between Access and SQL Server are large enough to require quite a bit of enrichment of migration features. Very quickly, third party migration utilities outstripped this wizard in ability.

Access 2013 does not have a Package Deployment Wizard

Once people got used to Access as a productive, useful system, there were often requests to have a one button installer that could install Access runtime and place the Access datafiles on the client's PC. This request might still surface, the way forward is to simply install Access runtime separately, place the Access datafile and create shortcuts to run the system.



Thursday, August 1, 2013

Outlook 2007 (and later) HTML and CSS rendering

In Microsoft Office 2007 and later, Microsoft decoupled Internet Explorer from Microsoft Office - thus the rendering engine for HTML/CSS email is Microsoft Word. Of course, Word isn't a HTML editor so here's an article on what it does (or does not do)