Back
Access

Feral Cats: Managing Access databases in your organization

This post was written by Steve Greenberg, a Program Manager Lead on the Access team.

Every few months, the Microsoft Access engineering team receives a high priority e-mail from someone in our worldwide sales org. It’s always the same root issue: an IT department is frustrated with the proliferation of Access databases and the manageability headaches that ensue.  Recently, we talked with the CTO of a British company who compared databases to feral cats wreaking havoc on his organization’s work.

We find that this focus on manageability is nearly universal among customers we talk to, both large and small. Organizations want to empower users in business units to do self-serve development of apps that improve efficiency and reduce costs. But empowerment creates headaches. Data must be secured so that only the right people can access it. It must be backed up so that hardware failure is minimally disruptive. And there must only be one copy of the data, so that all parts of the organization make consistent decisions.

This release, we have a number of exciting things to announce about managing databases with Access. First, let’s talk about some new tools for managing desktop databases:

  • Microsoft Office 2013 Discovery and Risk Assessment allows you to scan an organization’s network shares and SharePoint document libraries to find Access databases. You can scan on demand or on a routine basis, and incorporate the discovery results into a master inventory. Once files are inventoried, they can be evaluated for complexity, financial impact, risk, and errors. Check it out here.
  • Microsoft Office 2013 Audit and Control Management Server contains a set of database management tools that allow you to audit databases and understand the changes being made to them. Check out the tools here.

These tools help manage desktop databases. But most people agree that the right place to store data is on a server. Data that’s centrally stored on a server is inherently more reliable and secure than data floating around as files on file shares and desktops. As you evaluate your data tracking options going forward, you should consider Access 2013 Web Apps. Manageability is one of the most significant reasons we chose to build Access 2013 Web Apps on Office 365 and SQL Azure. Here are some of the benefits:

Reliability: Office 365 and SQL Azure are both enterprise-class cloud services backed by robust Service Level Agreements. To learn more, check out the Office 365 Trust Center. Also, here’s a recent blog post about fault-tolerance in SQL Azure Databases.

Security: You can use SharePoint permissions to control who can create new apps, modify the tables, queries, views and macros of existing ones, edit data and read data.

Monitoring: IT organizations often want visibility into the usage of applications in their organization. To meet this need, SharePoint collects data about each Access Services app. To access this information, visit the Site Contents page. Hover over the app, and click the “…” icon. Then click Details.

The app details menu

Here’s an example of what you’ll see. The App Details page lists properties of the app, allows you to see any errors encountered by users of the app, and gives usage statistics to help you understand how often the app is being used.

SharePoint tracks many statistics about an app.

For the administrator who loves dashboards, SharePoint allows you to configure monitoring so that you can visit one web page and see the usage of selected applications in your organization.

As a team, we’ve always cared deeply about manageability and believed fundamentally that data is more manageable when it’s on a server than when it’s on a desktop. A SQL Server in a datacenter is the best place for data to live. But for years, the cost and technical barriers to creating such a server meant that it was only available for mission-critical projects. With SQL Azure Databases, we finally have the technology available to allow end users to easily and affordably create real SQL databases in a datacenter. Check them out using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

Join the conversation

10 comments
  1. "Feral cats"??!! You lead your own news about tools to help organizations deal with the proliferation of MS-Access databases and applications in organizations by agreeing with an (obviously) anti-MS-Access-biased IT Manager who characterizes them with a derogatory term like "feral cats" and use that terminology in your own thinking – to the extent of even headlining this blog article about the tools designed to help with the issue?

    Really?

    That really makes one wonder what you must think of those of us who choose to make providing MS-Access-based solutions as our profession then.

    wow. talk about respecting oneself…

  2. MBurns – We are always in awe of the amazing solutions that our customers are able to provide with desktop databases, and I’m sorry the characterization of end user-developed databases as "feral cats" didn’t sit well with you. Manageability can be a bit of a dry topic, and I was just attempting to inject some humor.

  3. Hi Graham,

    Let me get you connected with some folks who can help.

    Thanks, – Steve

  4. @Steven,

    Ah well, perhaps it is the case that my exposure to too many anti-MS-Access IT Management types (please note that I am studiously avoiding the use of the word ‘bigot’ there) – and my innate defensive reaction thereto – over the years has made me a tad overly-sensitive in this area.

    I can apprecaite the aforementioned IT Manager’s analogy both in humerous and situational respects, it’s never too good of an idea to ignore a management issue for too long, because when it does come back to bite you, it usually does so in a way not too dissimilar to being harrassed by a bunch of cats, feral or otherwise…

    but still…in the headline? doesn’t that tend to promote the viewpoint that we should be intending to reduce/eliminate instead (especially amongs the headline-scanning, short-attention-span-IT-Manager-masses ….who will probably never see this blog anyway…) *sigh* nevermind.

  5. Steve,

    I think one big driving factor behind proliferation is because users can’t do what they need to do using the ‘designated’ tool. A common scenario I hear about is that they want to do ad hoc reporting but can’t do so using the tools or need to archive data that will be lost (due to truncation & reload). So though the data is already on a server in a big iron database somewhere, they end up importing stuff into an Access database just in order to massage the data into format they need so they can perform the reporting they want.

    As long the users are frustrated and prevented from using big iron database effectively, they’ll be working around this by making their Access databases or putting it in Excel spreadsheets.

    Those tools seems to help with identifying the file and their usage but doesn’t directly solve the problem with encouraging users to keep only one version of truth.

    I also note that there’s an underlying assumption that data is being stored in Access files but what about those that uses linked tables? Furthermore, in this context, they already have data from a local big iron. So I’m not sure they appreciate a need to go to SQL Azure / Office365 in same way a small business owner would and may be constrained (or simply reluctant) to move their data out of the local network.

    So I’d be interested in knowing if Access team has any plan to help address those scenario and help promote effective ad hoc reporting without compromising the data integrity.

  6. Hi Andrew,

    There appear to be no instructions with the Discovery & Risk Assessment tool or the Audit & Control Management Server. The product Help file fails to launch.

    Regards,
    Graham R Seach (former Access MVP)

  7. Feral Cat here.

    I’m all for manageability and all of that stuff. But IT setting up SQL Servers and empowering us users who actually understand the problems that need solved is not the real world for millions of users.

    At work, it I waited for IT to make available SQL Server space that I could just use for all the little databases that are part of getting my job done, I’d be retired first. What IT departments do you talk to that want to empower users to solve problems with tools like Access? The ones that are already running Win7 and testing Win8? The ones that actually encourage users to make organic solutions in stuff like SharePoint? The ones that might let users use a centralized and managed SQL Server as a back end without 500 open work tickets, 1,000 forms, and 10,000 reviews and committee meetngs and architecture boards and so on? (All aimed to gum the idea to death and beat the user into submission.)

    Maybe this is reasonable from their PoV, but once again: MS is only paying attention to the companies that are spending lots of money with Microsoft and viewing them as "typical" which I don’t believe they are. (They may, of course, be typical of the kind of customers Microsoft would rather have.)

    They are not considering the ones that are stull running WinXP and Office2003 and fighting introduction of newer technology to the death. (Quick: how widely used is WinXP, still?) By targeting the "best customers" as "typical" with all of this new stuff, you are ignoring–indeed disaffecting–a huge mass of companies and users that aren’t anywhere near your bleeding edge. (Win8 and the MS notion that "nobody uses the start menu any more, everybody uses pinned items and jump lists" is another good example. Right–that’s what all us us WinXP users are doing…) This is making the "end of the PC" a self fulfilling prophecy. The curve to get to Win8/Off13 will be SO HIGH from WinXP/Off03, many will just put us all on Citrix Server03 or 08 sessions and buy cheap Wyse boxes or similar to sit on our desks. Our 3,000+ person corporation (wholly owned by two Fortune 100 companies) is already headed this way.

    The other big driving factor behind proliferation is accessability and response and the natural compulsion of many IT departments to be the exact opposite of what MS targets. Not interested in user empowerement (scared to death of it). Not interested in deploying enabling technology. If they get too worried about us "feral cats" and our scary unmanaged desktop Access databases, they aren’t going to put up SQL Servers and deploy Access 2013 to use them. They’re going to uninstall Access and then we’ll have to use Excel.

    Not to mention that tiny minority of us that use Access at home… Where is our SQL Server and SharePoint and so on going to come from?

    Feral Cat Out…

    • @DickWatson,

      Something tells me that somewhere along the lines, some of the Managers @ MS fail to comprehend that Gartner’s reports and surveys really miss the essential nature of MS Access and its varying roles across widely disparate enterprise types.

      I think your point about the resistance levels to changing technology in "empowering" users running contrary to their own management’s intentions/fears is a truly excellent one. Access empowers with a small ‘e’ in all those places where Management does not WANT TO empower their information workers with a capital ‘E’. There are many reasons for this to be the case, but somehow this rather essential real-world truth gets missed in calculating out MS’s Big Plans For The Future(tm).

  8. p.s., "CTO of a British company who compared databases to feral cats wreaking havoc on his organization’s work" — that is so typical of the standard IT refrain: "why our job would be so simple if only these infernal users would just go away" as though they are an end unto themselves.

    If my use of Access is causing some IT Manager nightmares, all I can say is now we are even.

    The [insert corporation name] IT Service Pledge: We’re not happy till you’re not happy.

  9. I am new to Access and what you are saying makes sense to me I look forward to creating my own databases in the future.

Comments are closed.