Does Anybody Really Know What Time It Is?

Yes, me. I do. I know what time it is.

First, though, you have to tell me where you are and what the date is. Then I can tell you what the local time is where you are.

Simple, eh?

Yes, and No. Obviously, we do this all the time without really thinking about it all that much. If it's 10:02 PM in Seattle in June, then it must be 1:02 AM the next day in New York.

With Access Web Apps, unfortunately, it's not that simple. When Access Web Apps were launched, they weren't fully gestated. Lots of good things went into AWAs, but a lot of important stuff got left out.

More importantly, the basic technology behind the AWA made it impossible to NATIVELY figure out what time it is within the AWA itself. We're talking about server technology which runs in a browser. As far as the AWA is concerned, the current time is the time on the database server where the SQL Azure--or SQL Server--database resides. If the server is somewhere in central Washington state, then Now() returns the current local time in the Pacific time zone. Washington state observes Daylight Saving time. If you ask for Now() in July, you get PDT, but if you ask for Now() in December, you get PST.

Let's say, for example, you happen to be in Arizona when you log in to this AWA. Arizona is one of a handful of states that don't observe Daylight saving time, so it'll be one hour ahead of PST, but it'll be the same hour as PDT.

And, of course, if you happen to be in New Jersey, Now() on this same database server will tell you it's three hours earlier than it really is, because of where your browser is physically located in relation to the database server.

It's enough to make a serious developer walk away from AWAs. I've actually talked to other Access developers who did just that.

Me? I'm stubborn. Besides, I figured, how hard could it be?

Not all that hard, as it turned out.

You can quite readily calculate the current local time for any location in the U. S. If you work at it, you can expand that to other countries as well.

Here's how I did it in one AWA to show how it works. It's based on a list of addresses for various locations throughout the United States. I started with family addresses, but the demo is now a generic set of locations,

First, I imported a new table of ZIP Codes which also includes the UTC offset for that ZIP code and whether that ZIP Code is in a state that observes Daylight saving time. I got that off the internet.

A word of caution. I am well aware that there is no guarantee this approach will handle every possible case, even within the U. S., where each ZIP code does fall entirely within a state. I am aware of one case, out in West Texas, where a ZIP code might be in two different time zones. There are two West Texas counties in the Mountain Time zone, while the remainder of the state is in the Central Time Zone. Therefore, it depends on whether the relevant ZIP codes in those counties fall entirely within their respective county lines. I don't know for sure, but I'm sure local residents do. I'll leave it up to them to work out the details. With that caveat, then, the table provides two of the three pieces of data we need to do the time zone offset calculation.

Next, I created a table of the dates and times when Daylight saving time goes into effect each year, and when it ends each year. This allows me to compare the current date to the date when DST changes and decide whether to apply it or not. This table will need to be maintained for future years, of course. I didn't have sufficient confidence it will be constant for the foreseeable future, although we should be able to count on the next three or four years.

The next step was to update an app table to set flags for this calculation. My app tables have other flags as needed in other operations. It's a handy way to persist such things, but also make them updateable when needed.

Then, I created a Data Macro to perform the DST calculation,

It runs in the On Start Macro. It sets a flag  in the app table to indicate whether DST is in effect or not. Later calculations refer to that flag field in the app table.

A second caution. Note that this flag field is the same for everyone using the AWA. If one user logs in, they set the flag. If someone else logs in a little later, they reset it. Except for the wee morning hours on the days when DST changes, though, this really shouldn't matter. And even then, it's not likely to present a serious hurdle because what matters is that the CURRENT local time is calculated as needed.

Next, I created a set of queries to combine the local addresses in the database with the Time zone offsets in the ZIP Code table and the DST In Effect flag in the app table. The queries report current local time at each address, or for all of the ZIP codes in the ZIP code table.

A set of Data Macros calculates the current time from:

a) The time zone offset reported by the app. This is, as previously indicated, determined by the location of the database server hosting the AWA.

b) The time zone offset for the zip code at each address in the table of addresses stored in the AWA.

c) The DST In Effect flag set when the AWA first starts.

The output of the Data Macros can be used in display current local time next to an address on a view.

It's actually somewhat simpler than it might sound. Please go to my website and download the demo if you want to install it on your own Office 365 site, or on your own on-premises SharePoint site. I'd really appreciate your feedback on  it.

Get GPC's Demo Access Web App -- Timezone and Daylight Savings Offsets to App Time (U S locations only)

Just Say "No-Rmalization"

It never ceases to amaze me.

The other day, we got a question over at UtterAccess about adding a Total column to an existing query.

In chasing that one down the rabbit hole, we finally learned that there were not one, but two, different tables involved, one for each Fiscal Year on which reporting was required. That's a basic table design problem right there, but it got worse. The earlier table, named FY16 had three fields, Department, Month and "Amount". (I never did learn what that Amount represented, but I suspect a Budget, since I've seen and built FY Budget tables before.) The later table had 14 fields, Deparment, "Amount", "January", "February", etc.

Altogether now, a collective groan so we can move on.

Okay, feel better?

So the original question was how to add that "Totals" column to a query representing the monthly amounts by department. We also didn't get an explanation of how they intended to combine the two FY tables, but, hey, that's the next step.

I pointed out, of course, that this represents two different design flaws, and that getting the required "totals" would be duck soup, or the equivalent in chickens, if the underlying table flaws were fixed.

I also refrained from preaching the gospel of normalization. I've come to the conclusion that too many people don't want to hear it. It DOES sound preachy to say "You've violated two of the Rules of Normalization. Repent!"

No, I try to point out a "more flexible, expandable way" to design and build tables and hope they take it onboard.


Sooner or later, they get it or they give up. Either way, the world is happier place, IMNSHO.

Just Say "No-Rmalization"

It never ceases to amaze me.

The other day, we got a question over at UtterAccess about adding a Total column to an existing query.

In chasing that one down the rabbit hole, we finally learned that there were not one, but two, different tables involved, one for each Fiscal Year on which reporting was required. That's a basic table design problem right there, but it got worse. The earlier table, named FY16 had three fields, Department, Month and "Amount". (I never did learn what that Amount represented, but I suspect a Budget, since I've seen and built FY Budget tables before.) The later table had 14 fields, Deparment, "Amount", "January", "February", etc.

Altogether now, a collective groan so we can move on.

Okay, feel better?

So the original question was how to add that "Totals" column to a query representing the monthly amounts by department. We also didn't get an explanation of how they intended to combine the two FY tables, but, hey, that's the next step.

I pointed out, of course, that this represents two different design flaws, and that getting the required "totals" would be duck soup, or the equivalent in chickens, if the underlying table flaws were fixed.

I also refrained from preaching the gospel of normalization. I've come to the conclusion that too many people don't want to hear it. It DOES sound preachy to say "You've violated two of the Rules of Normalization. Repent!"

No, I try to point out a "more flexible, expandable way" to design and build tables and hope they take it onboard.


Sooner or later, they get it or they give up. Either way, the world is happier place, IMNSHO.

What Walks Like a Duck?

 

 I love MS Access. It's been very good to me. I’ve made a living using (primarily) Access for more than a dozen years. There’s one thing about it, though, that keeps me up at night. It’s too flexible.
 

You know the old saying, “If it walks like a duck and quacks like a duck, it’s a duck”. Well, that’s fine if you have a flock of ducks and you’re trying to pick out one chicken among them. Unfortunately, “an Access database” isn’t a Duck, or a Chicken. Or a Goose, Turkey, Partridge, Sage Grouse, Pheasant, Guinea fowl, Cornish hen or Dove. It’s all of those and much, much more.
 

My point? Oh, yeah. Sorry I got a bit hungry and went for a snack. I’m back and ready to state the point.

MS Access is a Relational Database Management System (RDBMS)*. Access is an interface design tool. Access is a Rapid Application Development (RAD) tool. Access is all of those and much, much more. It’s a Data Aggregation Tool; it’s an Extract, Transfer and Load (ETL) tool; it’s a prototyping tool. It’s even got secret sauce and a close, personal relationship (relational pun intended) with its big brother, SQL Server and its other brother, Excel. Oops, got carried away there. There’s no secret sauce.

MS Access has the ACE data engine. It’s a remarkable engine, the little engine that could. You can abuse it, confuse it, rearrange it and extend it. One thing for sure is that, if you can dream it, you can probably build it with ACE. The ACE data engine is almost too flexible and too easily manipulated. And that’s a big problem, from time to time, especially for novices.

Unlike Ray Kinsella in the Field of Dreams, Eli Whitney or Henry Ford, most new Access users come to the task with no background in the field, no experience designing and building a prototype, or a vision and burning passion to realize their dreams to make the world a better place through the power of their creation. They just have a job to do, and most often, little time to do it.

That means, unfortunately, they all too often don’t have an inclination to invest the time and effort into figuring out how to use the ACE data engine most effectively. It’s sort of like Ray Kinsella setting out to clear his corn field with a garden hoe and rake, or Henry Ford trying to build cars on an assembly line with no way to move the vehicles along except teams of horses hitched to each one. Possible? Sure. Practical? Not really.

While I do applaud the creative, inventive use of Access, I have to wonder if enough novices really grasp the significance of learning how to do it right. And by right, I mean the five Rules of Normalization. Actually, there are many, many discussions of normalization floating around on the internet. If you want to know more, just ask Bingoogle. They both know where to look.

I guess, to sum it up, I’m trying to say that all ducks are not born equal. Some are Chickens, some Geese, some Pheasants, and a few I’ve seen have been real Turkeys. Applying the rules of normalization is the best way to avoid those turkeys.

 

 

 

*I’ll get some argument on this point. Access ACE is not a full-fledged RDBMS and was never intended to be. I know, I’m glossing over that fact on my way to a larger point. Take it up with me in an email; I’ll be happy to hear your opinions on the issue. I'll probably share them too. Depends on how many nasty words you use--or don't use, if you get my meaning.

 

 

 

 

 

 

 

 

---

[1] I’ll get some argument on this point. Access ACE is not a full-fledged RDBMS and was never intended to be. I know, I’m glossing over that fact on my way to a larger point. Take it up with me in an email; I’ll be happy to hear your opinions on the issue.

Back to Earth for a Moment.

Recently, I’ve spent a lot of time thinking about, and working with, Access Web Apps, which is Microsoft’s tentative venture into making MS Access databases work “in the cloud”. I know, pretty sexy, huh? The Cloud: that’s the ticket. (Bow in the general direction of Jon Lovitz.)

 

Well, it’s not all that.

AWAs followed on from the idea that MS Access is the best small database solution available for Windows, and has been for over 20 years. So, moving it to the cloud was a no-brainer, at least in the beginning 6 years ago. Microsoft’s Access web database offerings turned out to be less than we hoped they would be, and still could be. We’re not done yet, so there’s still hope, but that hope has definitely faded with each passing month. However, that’s only part of my point. The rest of my thoughts have to do with how we think about, how we approach, what we do for, our clients. We’re Access developers. What we do is create Access database solutions, right? Well, not so fast.

 

While we may be tempted to think in terms of "a database solution", what we are really after, most of the time, is a "data management solution". Clients come looking for our help to solve problems within their organizations which usually, but not always, include managing some of their most important business-related data. They need to make processes more efficient, minimize transaction time and costs, and produce more actionable reports. Those are the things we must put front and center, not the specific tools we use to solve those problems. That means the specific tools we choose should not be the determining factor in how we do the job.

 

I'd say at least two other factors must be given higher priority than our choice of a specific tool:

 

Simplicity. A variation of Occam's Razor can be applied. When there are competing ways to get the job done, it's usually best to choose the simplest one. Not always, of course. There may be other complications which require more elaborate solutions. However, the end user's needs should be met with the least complex, but still effective, solution.

 

Cost. We must never lose sight of the fact that we, as developers, are expending SOMEONE's resources no matter what solution we implement. If a sexy solution offers us the chance to show off our chops, and it costs no more than a mundane solution that would get the same result, okay fine I say, show off. You don’t get that many chances to dance in front of an audience. Just remember, the person with the checkbook is paying for results, not a flamboyant Flamenco.

 

On the other hand, it's not likely to be in the client's best interest to invest two or three times the amount in a feel-good solution using the latest bleeding edge technology just because it can be done that way. Why go to the cloud? Is that what meets the minimum acceptable requirements of the project? If so, then do it. If not, resist the urge to try out something new, just so you can add it to your resume. I have yet to meet the client who told me, “Make sure you have a good time working on my project. I don’t really mind what it costs.”

 

I have had one client tell me, on the other hand, “If what you want to do will save me money, or make me more money, go ahead and do it.”

 

That’s been my motto for more than a dozen years now. I approach every new project with the same thing in mind. You should too.