... GPG On MS Access

It's all about the data.

Saturday, January 4, 2014

Look out for Lookups--Part II

A lot has happened in the 11 months since I last posted here. Here's the top 3 events, I guess.

Our new book, Professional Access 2013 Programming, was written and published. The book contains two divisions, one on the Access web app functionality introduced in Access 2013/SharePoint 2013/Office 365, and the other providing advanced techniques for Access client databases. I worked with Ben Clothier on the web app component.

My previous employer closed its doors, prompting me to find new employment. I am now a Reporting Analyst in the Revenue Operations Department of Davita, one of the country's leading providers of kidney dialysis treatment. While I'm no longer doing the work I love most, I'm really proud to be part of an organization which can honestly say "we save lives every day".

At Davita, my job as a Reporting Analyst involves an Oracle Data Warehouse, Access 2007 to retrieve and manipulate data from the data warehouse, and Excel 2007 for reporting. While I no longer have much chance to work with Access as an interface design tool, it's certainly a crucial part of my toolbox in its data integration and manipulation role.

On January 1st, I was re-newed for the Microsoft Access MVP award. It's a humbling experience to be part of this group. They are my esteemed mentors and coaches and some of my best friends. It's an honor to be among you.


On the other hand, it's clear I've not lived up to the promise to provide follow-on blog posts on the vagaries of Lookup fields in tables, in Access. Well, on the theory that it's better to be late than never, I'm back to drop in a few follow up comments about the inherent difficulties in Lookup Fields, as used in traditional Access client tables. I've moderated my opinion a little--to the extent that I really no longer think they are evil. I just think they open the door to rookie mistakes that create damage and confusion, and are best left out of the picture.

If you want to take a quick look at what I posted last year, you'll see that I only briefly touched on the issue of ambiguity about the actual value stored in a lookup field. And, you'll also see that I really only touched on table views of the data, not what would happen in a combo or list box in a form. To say nothing of a lookup field used in a query or on a report. It's sufficient to say that ambiguity can be spread liberally around your database as soon as you let lookups in the door; just don't do it.

But, as I forecast in that last blog, there's a more sinister problem waiting for the unwary. Lookup Fields make it possible for a naïve user to store, incorrectly, the VALUE from the lookup table instead of its FOREIGN KEY. That, my friends, is as close to being evil as it can get, IMO.

Here are three screenshots illustrating this problem. I'll explain what happened after you look at the pictures. Most of you will, I'm sure, get it immediately from just looking at the screenshots.

Two Lookup Fields in a Table, Visually Identical but Logically Very Different

Technically Correct Implementation of a  Lookup Field

Technically wrong Implementation of a Lookup Field

Okay, so here's the deal. The two lookup fields in the first image look exactly alike. Both present the concatenated First and Last Name fields. Just looking at them in a table, you'd never know how differently they were constructed. Moreover, until you select one or the other, a new developer or naïve user would have no clue they are even lookup fields (although it is clear to seasoned developers that they must be either that or perhaps calculated fields.) The point is that they represent a subtle trap for inexperienced developers.

The second and third images show how these two lookup fields were created. The second image shows a technically correct implementation, with the Foreign Key value being stored, not the concatenated name. It's still not ideal because it does drag in that display value, but at least it doesn't introduce real errors.

The third image reveals one of the all-too-frequent errors committed by naïve, inexperienced developers. They think, "why not just store the value I want. After all the Lookup field makes that easy. And before they know it, they have a real mess on their hands. Confusing, illogical, just plain wrong.

I think I'm going to move on from here, although I can think of at least one or misuse of Lookup fields I've seen in tables created by inexperienced users (Trying to use lookups across fields in the same table to set up a Self-Join field.) There are bigger fish to fry, and I can't afford to wait another 11 months to post another installment.

Let me just sum up by pointing out that Lookup Fields in Tables are pointless at best and flat-out dangerous at worst. I can't say you should NEVER use them, but it's my opinion that doing so should be left to the pros. It's sort of like dynamite and rubbing alcohol. Used properly by someone who knows what they are doing, all three can be useful tools. Using them improperly by inexperienced newbies is just asking for missing fingers, blindness, and databases that don't work right.

Sunday, February 10, 2013

Look Out for Look Ups

For a long time now, I've argued that Lookup Fields in tables are One Bad Idea. I've even  Blogged about it.

Recently it occurred to me that maybe it's not enough to TELL people Lookups are almost always bad. There's  a world famous page that offers eight significant problems with Lookups. My complaints are actually focused on the fallout from Number 6 on that list. Lookup fields in tables simply make it far too easy for inexperienced users to screw up royally.

Let's look at just one example, which is all too common. First I'll show how it should be done, if you really, really just have to do it yourself. The following discussion is based on a three table database of employees, departments, and employee assignments to departments. You can find the originals of these tables, btw, on GPCData.com

The first table, shown in design view and datasheet, is pretty straightforward, a Primary Key, a native key used by the company to identify departments, and the name of the department. No surprises here.

Department Table in Design View

Department Table in Datasheet View

In the next two screenshots, you see the employee table in both design view and datasheet view. Again, nothing out of the ordinary here. 
Employee Table in Design View

Employee Table in Datasheet View
In the next two screenshots things get interesting. This table is a junction table that sits between employees and departments to track the history of department assignments. It's a history table because a department can have one or more employees and an employee can be assigned to different departments at different times during the course of their employment. Look at the first screenshot, and then I'll explain how it was set up.
Employee Department History Table in Design View.

Because it's a history table it has from and to dates, in addition to the foreign keys that "point back" to an employee and to a department.

The first four fields in this table are really all that is needed. The last two fields, EmployeeIDLKP, and DepartmentIDLKP are only there to illustrate why Lookup fields in tables are One Bad Idea.

The primary key on this table is actually a compound key on EmployeeID, DepartmentID and FromDate. That allows us to designate a unique combination for each assignment. Combined with other rules, it keeps us from duplicating assignments inappropriately, while allowing overlapping assignments. That's all outside the scope of the current discussion though.

So, as you know. This design stores PKs from department and employee tables exactly as they appear in the parent tables with no ambiguity,

I added two lookup fields  to illustrate how they work. These are, btw, designed so that the proper PKs are stored as FKs in the look up fields. The screenshot above shows the row source for the employeeID. It is the first column in the row source, which is also hidden. That means when we flip this table to design view, we'll see the employee name, not their PK. A win for the person who wants to see the name in a table.

Employee Department History in Datasheet View
Here we see that the Employee Lookup field not only displays a value, it actually displays a concatenated value, "LastName, FirstName". How cool is THAT? The department Lookup field shows, not the department name, but the text value for the department Number. Hm. That's kind of cool, too, but doesn't it raise a question about WHAT you're really planning to store here? is it really the (appropriate) DepartmentID Primary Key, or the (inappropriate) Department Number, which is not a Primary Key.
No crisis yet, but this sort of ambiguity is precisely why I object so strenuously to lookup fields in tables. Even when used correctly, they throw a layer of ambiguity on top of the process, and that's something we can all do without.
In future installments, I'll go beyond this initial problem and show you even more insidious problems caused by Lookup fields in tables. Trust me, it gets worse than relatively harmless ambiguity, much worse.

Tuesday, January 29, 2013

Ambiguity Is For Poets, Politicians and Comedians

Access developers already know this, of course, but sometimes we run into situations where we need to be reminded just how important it is.

The human mind thrives on ambiguity. Poetry is the art of making one multi-faceted word speak with many voices. Politicians couldn't survive in office more than a few minutes without the deft word play that conceals as much as it reveals about their true intentions. Comedians depend on the double-entendre to make their audiences roar at the absurd contradictions in their patter.

Yes, without ambiguity, much of human interaction would be drab, colorless and boring.

Yet, as Access developers all know, ambiguity in a database is a short path to data loss, corruption and misleading results. None of which is a good thing.

I was reminded of this yet again in a couple of recent discussions involving dates in forum posts.

Ironically dates are probably the most subtle of the ambiguity problems in Access precisely because  Microsoft put a lot of work into making Access pretty good at "guessing" about dates. You can try this yourself by asking Access to evaluate expressions that just LOOK like they should be dates.

Put this in the immediate window and see what you get.

?Datevalue("Jan  3, 2013")
?Datevalue("Janu   3, 2013") also returns a correct value.
?Datevalue("Jan  3. 2013") does not. I guess you can only push it so far.

Access has become quite good at interpreting and using "date" values. For that reason, we sometimes don't get errors from "bad" dates; we just get wrong or incomplete answers. And that, in my mind is even worse.

One of the problems is that Access actually stores both a date and a time portion for ALL dates, even when one or the other is not supplied.

In other words, if you look under the covers at a field which displays as 01/29/2013, for example, you may find it's actually something like this: 01/29/2013 10:12:23AM.

How does that happen? We'll, let's say you have code that inserts a default value into a date field, like this:

Me.txtStartDate =Now()

Now() as you probably know, means right now, this second. I.e. 01/29/2013 10:12:23AM

And that means in turn, that when you just look at a display of that value, formatted as mm/dd/yyyy, you're going to see only 01/29/2013, which is NOT what is actually in that field. Ambiguity just snuck in the side door through this combination of stored values displayed in an ambiguous fashion.

Most of the time that kind of hidden ambiguity doesn't cause problems, but there are times when it makes a huge difference. Let's say you're trying to match records from two tables on that date field. Inspecting the tables, displayed as mm/dd/yyyy, you'll spot some matches. However, when you ask Access to match the two tables on that date, you're quite likely to get zero, or a very small number of matches. Confusing? You bet.

Maybe you're already ahead of me, but the explanation is quite simple: To Access, 01/29/2013 10:12:23AM is not equal to 01/29/2013 10:12:24AM

Access doesn't stop with the formal display of the field; Access compares the values in their entirety. Reporting on Sales Results which matches dates--with times--of sales against dates--without times--of shipping is going to make you look pretty bad because almost nothing is going to match.

Wait, I just realized, there is some humor here after all. If your Shipping Timeliness report depends on matching Order Dates, including the time, against the Shipping Dates, not including the time,  for those orders, the joke's on you, isn't it?

Saturday, January 19, 2013

What Does a PCV Do Anyway?

My daddy taught me a lot about living a good life. I wish I'd been smart enough to pay better attention when I was 18, but I survived long enough to be able look back and pick out the important stuff through the prism of my own experience. For example, he taught me not to try to fix a PCV without the right tools, the right parts, and the right kind of training. Let me tell you about that.

My father and brothers ran a fuel distribution business in the late '70s and early '80s. That included what was still called a "service station". You may remember them, the corner gas station with a half dozen pumps and a service bay or two where the station operator fixed flat tires, did oil change and lubrications, and other minor mechanical repairs. My dad loved working there because he got to talk to dozens of people every day. My dad loved to talk to people about everything under the sun; he was very smart, and he read a lot. He knew a little bit about many subjects and a lot about a few subjects. And he was always eager to learn more, or just tell or hear a funny story. Running a service station was a good fit for him.

Anyway, I happened to be working there one summer. I was looking for a full-time job and my family let me work at the station in order to make some money, rather than go on unemployment. So it worked out that he and I were alone in the station late one Saturday night. It was summer, so it was still fairly light out.

A car pulled into the station and up to the service bay door, not stopping at the gas pumps, so we knew he had some problem. The driver hopped out and hurried into the station. He was obviously agitated. He told my dad that he had a bad PCV and he wanted it fixed, replaced, whatever. He went on to tell us he had an important meeting Sunday morning in a town two or three hours on down the road, and he couldn't wait. He demanded Dad fix the PCV immediately. Demanded, yes, that's the right word.

I was bit surprised that my Dad refused. Instead, Dad suggested he get a motel room and wait until Monday morning, when the car dealership would be open, and have them fix it. He was usually accommodating to such requests, and I'd even known him to get out of bed in the middle of the night to deliver a five-gallon can of gas to a stranded traveler. I thought maybe it was the attitude of this stranger that raised the stubborn streak in him. It was there, too.

Eventually, the driver left, after tossing a few nasty comments our way, and slamming his car door loud enough to signal his displeasure even further.

We watched him drive across to the convenience store on the other side of the street. Now, I ought to pause to explain that, in those days, service stations like ours were still common, but they were being pushed out by the convenience stores that sold gas, pop, hot dogs and other junk food. Cheap gas and a Big Gulp were beginning to win out over old-fashioned service and a candy machine by the door.

So, we watched as the counter clerk came out this gentleman's car, popped the hood and proceeded to whank on parts with a crescent wrench and a pair of pliers.

It was about then that my Dad shared his insights into fixing PCVs. He pointed out that he really didn't mind trying to fix a PCV, IF, and only IF, he could run over to the local dealership or the parts store to get a replacement, if he needed it, and if he could call his friends at the dealership for advice if he couldn't figure it out on his own. That wasn't going to happen in a small town on a Saturday night. But since he figured he might also need a special tool to replace it, and because he'd never worked on such things, he didn't want to risk making the problem worse.

You see, my Dad was a fair mechanic up until the time the vehicle manufacturers starting putting electronics and things like PCVs and so on in their cars. He knew his limits, and tackling that PCV on a Saturday night with no chance to get the right tools or parts or advice was on the wrong side of those limits.

Well, you can probably guess the rest of the story. On Sunday morning that car was still sitting right where it had been the night before, only the hood was closed. And it was still there Monday morning when the dealer sent his tow truck over to get it. Apparently the Saturday repairs had gone wrong in a bad way. To his credit, my Dad said little; however, he DID catch my attention and nod over at the car as it was being towed away. He wasn't a saint, after all.

So, once again, you're wondering what in the heck a PCV has to do with Access, aren't you? Let me see if I can tie them together.

Long-time Access developers have become really good working with the tools of our trade: Access and related Office Automation. And some of us have included SQL Server in our tool kits. But now, we're faced with a real choice. We can go on fixing flats and changing oil and even doing some fairly complicated mechanical repairs. But there's a new game in town called "the cloud" and we have to decide whether we're going to go on running the local service station, or if we want to acquire the tools and knowledge that will take us on to the next phase.

Looking back at that day, I think I'm pretty close the age my Dad was when he decided he didn't want to learn the new stuff. He didn't complain much about it, that I recall. He just recognized where the line was and chose not to cross it. He may have been disappointed; I don't know. But he did NOT complain that the new technology was leaving him behind. After all, a PCV, whatever that is, was a useful thing on new cars and that made people's lives better. He was always a believer in making people's lives better, but that is another whole story. If the mood strikes I might tell that one, too. I just can't figure out a way to tie it to Access development.

So, here I am staring at that same technology line. Unlike my Dad, I have already committed to stepping over it into the new world where Access Web Apps will start making people's lives better. It's easier for me, in one way. The technology is far more accessible to me than it would have been to my Dad. We have Bingoogle, for goodness sake. And the tools I need to do my work are readily available and relatively cheap. Still, the thought that I could just coast on through the next few years, fixing flats and changing oil in the random Access databases that come me, is never far from me.

Here's my question for my colleagues in the Access Development business. What's it going to be?  Go on fixing flats or learn how to change PCVs (whatever the heck they are)? We have the choice and the web tools are easily available. I am pretty sure it would been really bad if my dad had gone out onto the curb to throw lug nuts at the kid from the convenience store.  The one thing I think we can't afford to do, as I see it, is to go on throwing lug nuts at the people who are making and installing the "PCVs" in Web Apps. That does no good to anyone, does it? And if we hit them in the back of the head enough times, they're going to start putting distance between them and us. And that would be a real tragedy, IMO.

Sunday, November 25, 2012

Yes, We CAN Get There From Here

The day after Thanksgiving turned out to be a pretty good day. It was the day I discovered that sometime in the last couple of weeks, Microsoft had turned on a really significant functionality in the 2013 version of  Office 365. Specifically I'm referring to support for creating and deploying Access 2013 "hybrid" databases on Office 365 as well as in on-premises installations.

This is a positive step for Access developers who look forward to having a broader range of tools with which to meet the needs of our clients. I have to acknowledge we're not all the way to the Emerald City, but at least we now know that, when we do get there, we'll find someone other than a phony wizard on the other side of the curtain.

Let me explain by reviewing past history with Access 2010 web apps running on SharePoint.

The Way We Were

In the 2010 environment, we could publish a set of Access tables to a SharePoint server as an Access web database.

A Quick Side Trip Around the Thicket of Database Terminology

In the previous sentence I referred to "an Access web database." In the last couple of years, a lot of my professional consulting work has involved Access Front Ends with SQL Server Back Ends. Along the way I've gotten used to thinking of the Access Front End as the interface to the SQL Server database. In Access that terminology is less common. Both files are accdbs and you have to crack them open to see which is which even though the names "BE" and "FE" are a strong clue, of course. Call it an FE or an interface, whichever suits you best. Call it a BE or the database, whichever suits you.

Conceptually, it is true that the tables are really what we think of as a "database", and the remaining objects are the interface through which we interact with the database. Thus, when I refer to "a database" in this discussion, I'm primarily thinking of the tables, not the interface, which CAN be created with Access, but isn't necessarily.

This Looks the Point Where we Left the Main Path

So, having published our tables as a SharePoint "database", we could create one or more interfaces for it using standard Access forms, queries, VBA and reports which connected to those SharePoint lists, just we would any other linked tables. Here's where it gets interesting again.

I'm a Little Bit Webbie; I'm a Little Bit Classic

When it came time to build the interface for that database, we had two choices.

First, we could create and publish a set of web forms, queries, macros and reports to work with those tables. Those objects ended up residing on the same SharePoint site where the database itself lived. That web app ran directly in a web browser. See that cord tying you to the desktop? Just go ahead and snip it. No problem. You're in the cloud now, cowboy.

Moreover, we could also create a traditional, client-side Access interface to work with those tables. That interface was deployed onto a Windows workstation for users. Solid, dependable, good old Windows workstations.

That ability to create two kinds of interfaces for a single database (technically a group of SharePoint lists), was a slick way to leverage the strength of traditional Access interface design against the new web capabilities. One database could support both the client side user and the web side user. Pretty slick. We called it a hybrid database. And it worked for both on-premises SharePoint installations and for Office 365 hosted solutions. It did have its limitations, and most of us aren't going down that path any longer, but it is still a great design for some situations, and, IMO, it was probably under-exploited for reasons that have little to do with the technology.

In retrospect, it seems that many of us took that O365 functionality for granted; it was part of the package. I, for one, didn't even think about it all that much. Then came Access 2013 and things got REALLY interesting again.

With the launch of Access 2013, though, we were given a whole new set of tools with which to build Access web apps based on a very different design paradigm. Gone are SharePoint lists functioning as pseudo-relational tables. We're publishing our tables to SQL Server databases now. Fully relational SQL Server databases. I'm not going to go into the details here, because there's really only one point that matters to this discussion and I'm quite seriously afraid I'll go wandering off down some garden path of no relevance.

That point of importance to me here is this: When you publish a web app in Access 2013 to a hosted account on Office 365, the database is a SQL Azure database. If you have an on-premises installation, you might be publishing to your own local SQL Server database, but with Office 365, it's SQL Azure--the database in the cloud.

Web is Web and Client is Client and Never the Twain shall Meet

That advance on the database side, unfortunately, came along with a take-back in the first release. We lost the ability to connect a standard Access client side interface to those tables when they were published to a hosted SQL Azure database via Office 365. If you had the on-premises installation, that limitation wasn't in effect. However, for those of us who run small development shops, and whose clients are all small- and medium-sized organizations, the lack of connectivity from the desktop to the database was a significant hurdle. Maybe a deal-breaker if you look at it from a certain perspective.

Together Again, Naturally

For months I've been whining to anyone at MS who would listen, and I've been getting promises that this problem was going to be resolved. Not that I'm impatient, but, well, it took a while, guys.

The good news, of course, is that this functionality has now been enabled for Office 365 accounts, and YES, I can publish an existing Access database ("Back End" to you Access developers) to my Office 365 account, get the connection information for that database, and connect to it directly from any standard accdb interface (that's the "Front End"). And with that configuration in place, we're now able to deploy a hybrid database again. It's different from the hybrids we built with Access 2010, but I still think of it that way.
You Take the High Road and I'll Take the Low Road

In future blogs I'll dive into more of the details, and any snags I run across as I explore this functionality. Here's a quick overview, though, of what I have found so far.

The first step is to publish the database itself. For my first attempt I selected the time and billing application I use for my own work. I blogged about it last year, when I first migrated the data from a local SQL Server instance up to SharePoint. You might get some insight into that process by reviewing the three blogs in that series.

This time I told Access 2013 to make me a new web app database by importing those SharePoint Lists.
Surprisingly to me, the total amount of time needed to create tables on SQL Azure and move the data into them seemed to be less than when I first published them to SharePoint last year--and there was another full year's worth of data to move. A foggy memory could be at work, as well as other technical factors, though. It's something that bears further research if someone is so inclined. I found one puzzling problem in this step, but it wasn't a deal breaker. I'll tell you about it next blog post.

The next step is to copy the existing Access accdb interface into a new accdb and delete the linked SharePoint Lists from it, leaving only the interface elements in that accdb.

The final step was to use the connection information now exposed in the Access web app to create a new ODBC connection to the SQL Azure database from the plain accdb and link in the tables. I ran into one snag here and I'll tell you about it next time.

Oh, yeah. The final, final step was to rename the tables. If you have worked with ODBC linked tables, you're familiar with the fact that Access prepends the schema to table names, so instead of, for example, tblCustomer, you have dbo_tblCustomer. Well, in the new web app environment, your tables will be prepended as Access_tblCustomer. That reflects the fact that the schema in which Access published tables are created is called "Access", instead of "DBO".

At this point, you have a local, working, client side Access interface that can run down here on your desktop along with a rudimentary web app that can run in the cloud, both connected to your SQL Server database. I'll blog about the design of the web app interface in future installments.

In coming blog installments I'll also go into other details about what I've found, the good, the bad and the puzzling. And we'll have to come to grips with some of the other limitations that still have to be resolved.

However, the fact that this brand of hybrid functionality is now available to anyone with a $6-a-month Office 365 account makes my little toes tingle and my little heart thrill. For $6 a month, you can build and deploy an Access interface for your clients' workstations, and add on a web app that extends the reach of that application to any browser.

The Fine Print

In addition to the technical problems still to be resolved, I've only brushed the surface of the pricing model. We still have to learn how MS plans to make this really a scalable solution cost-wise. Still, at the risk of reading too much into what I know so far, I am highly confident that's just a matter of time, now that the first Ruby Slipper has finally fallen.