Building Reliable Data Solutions

Warning: This post is outdated. It is here for reference purposes only.

Spreadsheet Hell. It's a place many people find themselves in and it's a difficult hole to dig yourself out of, because your spreadsheet is King of Spreadsheets and can do no wrong. Or, it does wrong frequently but you just can't seem to start the required Anarchy because Joe Executive might ask you a question only the King can answer. This post is quick summary about how I overthrow the King, one spreadsheet at a time and the tools I use to accomplish this feat. Reliable data solutions are a must for any business in this age.

Who Can Replace the King?

The initial requirements for the revolution to replace Spreadsheet Hell are as follows: Ability to replicate calculations in Excel, data entry, reporting, data integrity, and security. We want the data to be stored in SQL Server which allows me to ensure data integrity, security, and calculations. What we need now is something that can provide user friendly data input and flexible, functional reporting. Oh, and free is preferable in case you hadn't guessed it.

The Candidates

Unfortunately, the candidates to connect to our internal SQL Server are rather limited. We gave Perfectforms a good try but it had a shortcoming when it comes to printing (I wasn't directly involved with this issue, I have no more details, sorry). We built out another project using InfoPath which had a number of issues for me. The biggest problem was it's lack of integration with SQL, but apparently it works great if you need to connect to Sharepoint. For that project Sharepoint was not an option because we needed relational data and powerful querying abilities for writing the required reports.

There have been a few others that I/we have explored but the unfortunate candidate that keeps winning is.... MS Access.

Whaaaaa? You said you wanted Data Integrity!

You heard me right, MS Access has been the option that just won't die. Every time we explore a new possibility we run into issues that don't exist in Access. Such as... being able to easily connect to SQL Server. Or ability to easily create forms for data entry. Or to generate reports with charts.

Yes, it has its fair share of weaknesses which brings me to....

** Rule #1 of Well Crafted MS Access Front-ends:**

Give Access as few responsibilities as possible.

To illustrate my reasoning, here's an example from two weeks ago... I have been working on Database X to allow users at our 3 physical locations to administer a survey over the phone. I had the brilliant idea to save myself some time and allow a set of 5 forms to connect to a linked view (from SQL, of course) by setting the form fields to use the appropriate control sources. At first I was like "Woah, I forgot all this stuff just works"... and then it didn't.

Long story short:

• Page 1 wouldn't allow you to save the record the first time you filled it out, but the second time it worked, every time!
• Page 2 worked great.
• Page 3 had two different somewhat random errors that I couldn't explain....

... And then I scrapped the control source settings and went back to making the connections in VBA behind the scenes... and no more errors. My time wasted on this lousy experiment was minimal but it was a gentle reminder to not trust Access to do anything correctly.

Do Things Right

Before moving onto Rule #2, here's a laundry list of things I don't like about Access. (This is not an exhaustive list!)

• Smart users can bypass almost everything I can set
• Smart users can directly access the tables and data
• The VBA editor is worse than any text editor with no syntax highlighting
• Access corrupts itself regularly (this must be part of its performance goals)
• Can't track my VBA in my source control
• Form building tools don't work right/consistently
• It will corrupt your data if you let it store your data (Please, do not ever do this!)

Rule #2 of Well Crafted MS Access Front-ends:

Give SQL Server as many responsibilities as possible.

There are two basic ways the front-end should talk with SQL. It should be able to retrieve data relevant to the user, and send data back to SQL for storage. To accomplish these two functions I use views, stored procedures and user defined functions. In a recent post I wrote about how I created a custom function to use in views to help filter data based on the current user.

Stored procedures handle every single transaction that modified data. Period. Sure, it might seem like more work to start with because instead of writing a simple one-liner like:

INSERT INTO dbo.gradAttemptContact (gradID, employeeID) VALUES (1, 2);

You end up with a monstrosity such as:

CREATE PROC dbo.surveyAttemptContact  
    @gradID INT = NULL,
    @employeeID INT = NULL  
    AS  
    BEGIN  
        SET NOCOUNT ON;

        DECLARE @errMsg VARCHAR(500);  
        DECLARE @errLocation VARCHAR(50);  
        SET @errLocation = 'SP: surveyAttemptContact';

        BEGIN TRY  
            BEGIN TRANSACTION  
            INSERT INTO dbo.gradAttemptContact (gradID, employeeID)  
                VALUES (@gradID, @employeeID);  
            COMMIT
        END TRY  
        BEGIN CATCH  
            ROLLBACK  
            SET @errMsg = ERROR_MESSAGE();  
            EXEC dbo.errorLogInsert @location = @errLocation, @desc = @errMsg;  
            RETURN  
        END CATCH  
    END

This is the most extreme example I could find, but I believe the advantages far outweigh the extra bulk of code shown in the above example.

Let's just imagine that you have developed the perfect solution for Business Problem Q, so perfect in fact that there are no bugs in the code and the users LOVE using it. But then, the Business changes (gasp!) how they need to handle Problem Q, and your database isn't setup for that. If all of your logic is in Access either in defined queries or VBA you have to update the front-end and redistribute it to all the users. Bummer. OR.... if you have your logic for handling data in SQL you can make the necessary changes there and along you go. (This assumes the inputs/outputs don't change, of course)

Tightening Up Access

Now that we have our users getting data through pre-filtered views and sending data back through stored procedures, the only thing left to do is apply some security to keep them out of everything else. We use Active Directory with user groups setup so we only need to apply the permissions to the parent group(s). Here are a few examples of what you can do.

Allow the group to....

SELECT from a view:

GRANT SELECT ON [dbo].[vCurrentEmployee] TO [UserGroupHere]

UPDATE a view:

GRANT UPDATE ON [dbo].[vCurrentSurvey] TO [UserGroupHere]

Execute a Stored Procedure:

GRANT EXECUTE ON [dbo].[surveyStart] TO [UserGroupHere] AS [dbo];

Setting up access this way allows you to deny access to the base tables, such as dbo.employee, while allowing the users the ability to make the changes they need.

It's Not Perfect, but....

After 6 years of developing Access to be a front-end interface I have picked up a number of tricks, but only 2 rules. Ensuring data integrity and security is the most important thing to me and these tricks to control and restrict access have helped immensely.

How do you accomplish building similar solutions? Am I missing an incredible tool? Leave a comment and let me know!