Data Access IS The Problem

I have harped on this here before, but once again my blood-pressure is rising because I have seen a simple, efficient use of the powers of Excel marginalized by a client’s inability to get support and cooperation from the “Gate-Keepers” (“Crypt-Keepers” ?)  of corporate data stores.

The Gate-Keepers are often corporate DBA’s who are deathly afraid that users might hurt themselves if they actually get the data they need to help them analyze performance and plan their business futures (even though they are already doing it mostly in “crappy”, dangerous, silos of user-designed and maintained spreadsheets already anyway).  The rest are the makers of third-party accounting or BI software who’s sole motivation is simply to get total control of the data and the process so the business is forced to keep paying them forever and ever …

By forcing BDMs to flail along with stand-alone spreadsheets without direct or reliable integration with REAL corporate FACTS they are exposing the business to risks that may or may not be large.  Regardless, these processes are inefficient at best and frustrating to users and their bosses no end.  The efforts to eliminate spreadsheets altogether (which is the unspoken goal of most IT “Professionals” anyway)  simply has not and will not work.  That is unless they can succeed in moving everyone to the browser-based “calculators” being offered as Excel alternatives by Google and even Microsoft and eliminate the powerful Office Client Excel that has so much power and capability.  That would be unconscionably stupid.

In the case that has me worked up today it is data being collected by a 3rd party software provider who takes simple, straightforward collected data and presents it to the user in an awkward, complex and generally useless GUI in a browser that takes otherwise logical and straight-forward data and makes the export of the data to Excel a painful, inefficient, and in the end unusable, process – thus making use of their data outside of their interface (which they will sell the client and reporting module BTW ) impossible.

I have run into this in the BI “Universe” as well where at one client we have been trying to get the data we need for a critical business analysis tool in Excel for TWO YEARS to no avail.  I have found more and more cases where DBA’s will simply not allow departmental users or developers access to the very data they need to run their businesses while it’s being collected and collected but is being used for absolutely nothing.

To me the efforts made by DBA’s and 3rd Party software developers to prevent the effective import of corporate data into Excel is a major impediment to Corporations.  Tragically these Corporations don’t even realize it’s true or how much inefficiency and extra cost they are accepting as normal.  BDM’s as a rule defer to the “Professionals” in their IT brain’s trust or worse they are afraid to cross IT (like you might not to complain to the Police for fear of repercusiions).   It is truly a tragedy.

We should all remember that it is THE BUSINESS that matters, not the careers of the IT “professionals”.  And we are supposed to be working at using technology to make business processes better and more flexible not less.  We are servants to the Business not the other way around and whenever that gets forgotten we all lose.

Dick

TEMPVARS Look Like The Secret In Access Services Web Forms

Anyone who comes here for Access stuff knows that I am a BIG fan of the Hybrid capability of Access 2010 with SharePoint 2010 and Access Services.  But I am getting dragged kicking and screaming into more Browser-only stuff as clients insist that we provide browser-only capababilities (for various mostly specious reasons – but that’s another story and they ARE the Clients ).

I have spent far too much time in the last few weeks trying to build what is actually a fairly straight-forward Access 2010 Browser-Only form for a client, and I seem to have finally come up with the magic trick to make things work – but it has been unnecessarily difficult IMHO.

I would create a nice-looking Web Project form that would run perfectly on the client that would then fail when published to the Web … in other words simply making a form work on the client does NOT mean it will run in your Browser – in fact more often than not it won’t – and the reason is never explained and always looks like your fault until you exhaust all possible combination of Master/Child relationships, parameterized rowsources, or the incredibly powerful but cryptic BrowseTo function in your Macro code.  It all ended up failing for me over and over to the point I was thinking for the first time in years, that this simply wasn’t going to work.

Then I tried the TEMPVARS method which I knew was one that I would try eventually but only after I exhausted all the usual techniques that I as an Access Developer would think might work.  And it solved everything !!

Simple fact:

You can create a TEMP variable in code (say on the AfterUpdate or OnCurrent of a form or subform)  and then refer to that as a variable in any code or open form on display in a formula like this =[Tempvars]![varName]

Here are 3 places I used the TEMPVARS in my app:

1. Redefined the variable in Code for the AfterUpdate of a Text Box:

2.  Referenced in as the Source of a TextBox Control:

What’s interesting is that this TEMPVAR is referring to a field on a subform that is calcing the total for a field (“[CAD]“) in that subform like this:

This Control is on the subform and is a hidden field.  Then that form has an OnCurrent Event like this that resets the TEMPVAR being used by the Control in the Parent which immediately picks up that value for display:

I’m sure that this could probably be done with another event on the subform but I KNOW that it will work in the OnCurrent so I am happy with that .. (“Get ‘er Done !!!”)

3. In the Query driving the Rowsource of a Listbox:

Every one of these usages ofTEMPVARS solved ALL my problems.  It simply means that by redefining TEMPVARS as events fire in my various Forms, Subforms and Controls and with a few well timed requeries in code, I can effectively do all the same things I am used to doing with Master/Child relationships, with Query parameters or as Formulae in informational Contols such as aggregations.  And BTW the table source for this Form and Subform has 60,000+ records and it is REALLY fast at refreshing … faster than when run on the client actually.

Maybe this technique is posted somewhere else on the “Internets” or it’s in somebody’s book (which you should then go out and buy immediately) but I had to figure this out the hard way.  What this does is open me up to do many, many more things in Access Services Web Forms.  Too bad I had to learn it the hard way (like I used to all the time )..

Dick