Getting small databases like Access and Excel under control

One of the most difficult tasks I have seen my customers grapple with as I advise them on technology strategy for their information management needs is how to get a handle on those pesky Microsoft Access and Excel “files”. While we tend to treat these “documents” as simple file management problems they are far more than that: they are real applications and they are real databases with complete enterprise architecture impacts. I remember at one of my clients when were doing analysis of HIPAA privacy concerns related to patient information we calculated over a thousand MS Access and Excel “files” with health data that needed to be protected.

What can be done to rope in these real (small but important) databases and get them under control? First, we need to figure out why people use Access and Excel. Well, that’s simple: with those tools users are in control, they don’t need permission from the CIO to create a new app or database, they can connect to external databases, and most importantly they get their job done. Now, if you can give them those same capabilities but in a centralized manner, would your users use it and let you manage, secure, catalog, backup, and protect the data? The answer is: probably not immediate-term, but certainly yes in the medium- and long-term if you can setup appropriate policies.

So, how do you give your users those features? Well, a new class of end-user-friendly application-creators are hitting the market. There are enterprise-hosted systems like Caspio and Internet-hosted systems like Zoho Creator. Zoho Creator is currently more feature rich than Caspio but Caspio is pretty user friendly and can be installed behind your firewalls and the data goes into your own (SQL Server) databases instead of sitting on the Internet. I’ve spoken to the folks at Zoho Creator and they said they are working on an enterprise-hostable system that should be ready soon as well.

By setting up a server or service and training your users to use the new tools instead of Access and Excel, they can:

  • Create applications from scratch
  • Create applications using templates that other users (or you) can create for them
  • Create applications by importing existing spreadsheets or databases
  • Share applications with their colleagues and clients without sending secure data via email
  • Get email notifications when records are added/updated but only notifications (not the data) is sent across mail
  • Export data in formats that be analyzed in Excel or Access

Here’s what you get out of systems like these:

  • Manage the tool and the database server and all your data is in one place instead of strewn across hundreds of spreadsheets and little databases files
  • Secure the data and protect health information centrally
  • Look at the kinds of applications users are creating and learn from the requirements they’re fulfilling themselves to see if your group should be doing that work instead of users creating custom apps

      Newsletter Sign Up

      11 thoughts on “Getting small databases like Access and Excel under control

      1. Yes, IronSpeed and Oracle’s tools are nice but they are intended for a tech/development (programmer) audience. I actually know the co-founder of IronSpeed (he and I co-founded a health IT company back in the dot boom era).

        Have you seen anything that you like designed specifically for non-tech end-users?

      2. Thank you Shahid for mentioning Zoho Creator.

        As you said managing information is the most critical and very crucial for orgainisations. When the the information grows beyond control in 1000’s of files, it becomes tedious to manage them. Zoho Creator helps you manage your informations (data) online easily and very quickly.

        On the other hand, we undertand that putting data onto the web is a security concern for some users, and they should be concerned about the privacy of their data as well as their personal information.

        I would like to take this opportunity to explain all the readers out here that, our servers are located in a very secured environment. Firewalls and advanced security technologies are employed to prevent access from outside intruders. And your data can only be accessed by you and the users with whom you have shared your application with.

        And if at all, you want to take your data with you, you can do it at anytime. Zoho Creator provides various exporting options like xls, pdf, html, csv and tsv.

      3. Shahid – I have found nothing that is not an Access style clone. A similar tool that is fairly user-friendly, once live and installed is PHPSurveyor.

      4. Shahid, if the MCO I work for ever took this advice I’d be out of a job!

        On the other hand, I’ve been banging this drum since the first day I started here. My predecessor was the “keeper of the Access databases” for my Customers, a mantle I inherited. One of the first things I proposed was bringing about a half-dozen individual Access databases under a common, front-end .mdb file and porting the tables and queries to a more robust back-end.

        That proposal was shot-down because it sounded an awful lot like I was proposing custom application development and we don’t do that sort of thing here – or so said TPTB.

        This dismissive mindset about Microsoft Office is the primary reason we still have a federated mess on our hands. Culturally, the “get-it-done-now” mentality about Access (and most other Office programs) is as persistent in IS as it is just about everywhere else. No one sees Access as a powerful application development tool, consequently the people most responsible for ensuring the management and integrity of applications, their data, and the processes that both support end up facilitating the degradation of all three.

        There are many technical shortcomings of Access and Excel-bases applications that the tools you recommend would address. However, it’s been my experience that many organizations aren’t even at a place where they realize there are technical shortcomings to overcome because procedurally they’re lumping Office in with Post-It Notes and index cards.

      5. Another approach for helping to reign in this kind of “MSOffice bloat” is to get out there and actually let people know that they have an IT department. One that they can actually call and make requests of! Time and again I find IT departments that make 0 effort to go out and actually help people with their daily tasks. Even something simple like a VB script to automate the formatting of data can improve an end-users work experience and yes, even enhance their quality of life.

        I know, we all have bigger fish to fry. Databases need to be backed up, interfaces written, but without some kind of contact with the customer, “MSOffice bloat” can get seriously out of control. I’ve found that being out of touch is the #1 cause of this, and also the most easily remedied.

      6. Pete makes a terrific point.

        What’s worse is when a Customer does come to IT for help and IT management takes a pass and leaves them to their own devices because they’re “only Access files” or “only spreadsheets.”

        I’d be interested in reading about how other healthcare IT professionals have overcome this prejudice against user-friendly/desktop applications that seems so pervasive in IT departments. Or is it something unique to my organization and its senior IT management?

      7. Jeff — your situation is not unique at all. The “only access files” or “only spreadsheets” issue is quite pervasive. I think as MS Office moves online and other tools like Caspio and Zoho mature we will see some migration to centralized app development like we’ve seen with everything else.

      8. Hello Shahid,

        I have a question, these services you mention, Caspio and Zoho Creator, if I was to use their services to build a web app for paitient interaction fully hosted by them, would their service and security be sufficient in the eyes of HIPAA?

        I want to create a custom app but not keep a web programmer in house for $90K per year salary when I can go with one of these services and do it much easier and without the added overhead.

        Thanks for your input.

      9. Pingback: Protect yourself from Shadow IT, embrace “good enough for HIPAA” secure cloud services like Box and Skydrive | HIT Patient Engagement

      Add Comment