They are rare in the food industry, but they live among us. They are not nerds by any means, they are very passionate about working with data, like me. Sometimes you find them in financial functions, but rarely as CFO’s. Sometimes they carry innocent titles like “controller”, sometimes more divulging like “business-analyst”, and on occasion you even find them in operational functions like COO or VP of operations. I am talking about “power users”. These people have figured out that software technology can do already a lot. They utilize MS-Excel beyond basic formulas, can create complex expressions, vlookups and even resort on occasion to write some Visual Basic code to extend MS-Excel functionality with urgently needed methods for yield calculation or other things.
I have seen a lot of these power users leveraging MS-Access in the past decade to create some simple applications. In one instance I saw a pretty amazing application for cut-yield developed by the guy that was in charge of operations at a beef-packer. That solution had been developed in MS-Access 2003 with basic user authentication. It has outgrown its application. Applications that have been written by end users using technologies like MS-Access are great, but they quickly outgrow themselves. In this instance, two major events happened:
- The application was so good, that more people wanted to use it. MS-Access, though great as an end-user tool, cannot really be used for multi user environments. You could say its greatness is its demise.
- Microsoft decided to put away with User Authentication features, instantly turning the application into a legacy application, for which Microsoft’s mainstream support ended already more than 2 years ago.
Removing this functionality was in hindsight a smart choice. It was a decision that declared MS-Access as a database application just for individual use on the desktop. This is what it is, this is probably where it ends.
Lately there have been two developments for the power user community indicating major improvements for these Power Users. Here are my favorite picks:
Every power-user that I know uses Pivot tables. Pivot Tables had a major overhaul in the office 2010 suite, and your be reason alone to upgrade if you don’t have it yet. Pivot Tables allow users to create quickly slice and dice type of reports that can drill on the screen, a feature that has been historically a selling point of BI-Suites. Using Pivot tables in MS-Excel though had its problems when the data tables were getting pretty large. 50,000 rows and more made MS-Excel too slow to really do fast and interactive analysis of the data. The BI-Team of Microsoft (the Analysis service people) developed a plug-in for MS-Excel called PowerPivot, which can be freely downloaded from its website. PowerPivot enhances connectivity to different databases, allows creating of relations and can process within MS-Excel Millions of rows of data. I saw recently a presentation called “Killer Real-World PowerPivot Examples“, where a guy was processing more than 20 Mio. records in MS-Access. Try this in an older version, or without the plug-in. It is free they say, but once you get the hang of it, you want to share the results with others. To do this, Microsoft expects you to pony up MS-Sharepoint Server, Microsoft’s collaboration software. Note though that Microsoft will offer some “MS-Sharepoint Online”, a basic version of it as part of its new MS-Office 365 subscription software, in case you plan on moving your office licenses and solutions to the cloud.
MS-Excel still reaches quickly its limitations, especially if you need to create real database applications in which you enter and store data and where you need to add some basic business logic to it. The people that used to use MS-Access for quick application prototyping turning into real life applications have now a new toy called MS-Visual Studio Lightswitch. Visual Studio Lightswitch is in its Beta 2 right now, and will be officially launched in a week. Lightswitch is a new end-user development toolset, that allows you to create graphically forms and applications that run on the most current Microsoft web technologies, Silverlight, Internet Information Server, MS-SQL Server. The application toolset is really written for power users, and you can do a whole lot without adding a line of code. If you want to add code, you can do that in VB or in C#, very similar to Visual Studio Webdeveloper. With these easy tools, end users can now create very robust, multi user applications that can easily be used in environments with thousands of users. So if you are calling MS-Access today one of the primary weapons in your arsenal, take a peek at what the Microsofties have in store for you – you will overcome their reckless removal of user authentication in MS-Access and will enjoy sharing the road with professional developers.