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.



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")
1/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?

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.

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.

That was a CLOSE One!

A few days ago I gave in to a bit of discouragement and posted a long diatribe on why I am afraid the story for Office 365 and Access web apps is discouraging.

Well, it turns out I was premature in my efforts at digging a grave for my BFF, Access. What a relief.

Here are the three big reasons I was so pessimistic, and the reasons I now have a lot more reasonable view. Again, keep in mind that this is entirely based on my work with the Preview on Office 365 and has nothing at all to do with on-premises SharePoint installations.

1) The data siloing and sealing scenario is not as much of a dead-end as it is a hurdle that can be crossed, if we just have patience.

2) There really is a life-cycle story for these apps. It's not as pretty and neat as we might like, but it is a true story.

3) Inter user credentialing can be finessed, with enough imagination and patience.

Again, out-of-the-box, I'm pretty sure the typical "Johnny Poweruser" is not going to do much more than build himself a tool to track packages shipped out by his department, or maybe host a department site for time off requests, or something similar. Nothing high-level, nothing too complex.

But, there does seem to be a story to tell about extending these web apps. Moreover, as I see it, that story has to include developers after all.

1) Right now, of course, the path for data in Access web apps on O365 is still one-way. And that's not good. When the pathway is opened for data flow in the other direction, though, the biggest pain point gets a lot smaller. In an on-premises environment, a user can connect to their SQL Azure, or SQL Server, database from SSMS and work directly with the data in their Access web apps . When that same feature is opened up to Office 365, we will be able to at least create a backup of the data, or get to it for local use.

2) It turns out the app life-cycle story is not all that different, conceptually, from what we've always done with Access mdbs and accdbs: Make a local copy of the app (with or without data) and restore it, if necessary, to a production environment. The tools and techniques are still a bit raw, shall we say, and not really fit for Johnny's use. But it's there, and it's actually documented on MSDN! The url is http://msdn.microsoft.com/en-us/library/fp179918(v=office.15).aspx

3) Inter user credentialing does seem to have a solution, although I am afraid it's going to be "roll-your-own" in large part. We have three expressions that retrieve information about the currently logged on user. These are:

UserLoginName()
UserDisplayName()
UserEmailAddress()

It seems to me that a combination of these expressions and data macros should allow us to figure out a way to control access to data. I've not worked it out yet, and I'm not 100% sure how it's going to work. But there does seems to be a path there.

So, after a few days of rest, and a thoughtful discussion with some of my friends and colleagues, I guess I can recover a bit from my pessimistic outlook of a few days back.

I'll be following up again soon, as I begin to implement some of these concepts in my test apps.