Wednesday, August 17, 2016

Duplicating AutoNumbers — Sort Of

One of the shibboleths of Access design is that Autonumbers make good surrogate Primary Keys because a new one is always generated whenever a new record is started in a table. They are, therefore, guaranteed to be unique, or so goes the belief.

Moreover, because their New Value property is by default set to increment, i.e. to be sequential, they are also counted on to increment in an unbroken pattern, one at a time. That's generally what happens.


Unfortunately, as any long term Access developer can tell you, there isn't any such guarantee in the real world. There are at least two ways in which one or the other of these confidences can be betrayed.
  1. Autonumbers can be generated with gaps in the sequence
  2. Somewhat frighteningly, they can be duplicated.


Gaps appearing in an AutoNumber sequence is familiar to all Access users. It happens either when an existing record is deleted or when a new record is started but abandoned before it is saved.  Because Access is designed to generate new, previously unissued values whenever a new record is started, and because it drops those values when the record is later deleted or not saved, any such values are lost, creating gaps in the sequence. Other than the discomfort some users feel when they see such gaps, it's generally a case of "No harm, No foul." Although there's no universal truth here, most developers feel users should never even SEE the AutoNumber Primary Keys in tables, anyway. What they can't see can't hurt them.


The second way is not unfamiliar, although fortunately quite a lot less common: corruption. Corruption occurs for several reasons. Although this article is getting a bit long in the tooth, Allen Browne has published one of the most comprehensive discussions of the topic I know about. It's not the main focus of this discussion, so I'll leave it to you to you to follow up, should you be interested in learning more about corruption of the database type.

Suffice it to say that Access can abandon corrupt records--including their AutoNumber values--when you do a Compact & Repair on that database. Both the record itself, and that particular AutoNumber value, are lost. The lost AutoNumber value is in itself, not a big problem. Just like with lost or manually deleted records, the loss of the AutoNumber should result in no harm. The loss of the record, of course, can be very harmful, especially in the absence of a backup of that record.

As an aside, there are documented cases of such corruption being persisted through several generations of backups, so that recovering a good copy is no longer possible. That's another story for another day.


There is still another way, though, in which AutoNumbering can go very, very wrong, and that's really what I mostly want to talk about today.

This problem came to light in a recent incident where a friend asked me to lend a hand in a project. He was leaving on vacation and didn't think he was going to be able to wrap it up in time. 

The database in question was split, but a couple dozen users were all sharing a single mdb Front End. This is as close to a universal truth as we get in the Access world:

Sharing a single FE file across a LAN is bad juju.

Shared front end mdbs and accdb files corrupt, sooner or later. Shared back ends can also corrupt, but fortunately that's less common.

We rescued them, doing a compact and repair on the back end and copying the front end to each user's desktop. That took all morning, walking from workstation to workstation, booting the user off while we copied and set up the file.

Then, we had the owner check. Sure enough one record was gone. ONLY ONE! Cool beans. All we need to do is reenter that one record and make sure all of its child records are updated to the new Primary Key. Not so fast, bubba. Whoever designed this puppy decided to use this very same AutoNumber PK for their Work Order Numbers. It's even printed on the certification documents they send to customers. Now, that right there is a problem. It's the main reason why experienced Access  Developers don't show users Autonumbers for any reason. Just imagine. If we had another field in that table called "WorkOrderNumber" with a separate AutoNumber Primary Key field, it would have been a matter of mere seconds to add a new work order, manually give it the missing Work Order Number and then update those child tables to match. Well, no more than a couple of minutes, anyway. But the original developer didn' t do that. He used that doggone AutoNumber.

So what is that message going to sound like? "Um, sorry, but we lost your data and that certificate is no longer viable. Here's a new one. This one is DEFINITELY good. Trust us."

Doable but very, very undesirable.

So, I very cleverly came back the next workday to reinsert that lost record. Here's an interesting, and probably not widely known fact about Access Autonumbers: you CAN manually insert one specific value into an AutoNumber field. Of course, you can't duplicate an existing value in a Primary Key field, but, it's not a problem to reinsert one that was lost due to deletion or corruption. To quote one of my favorite tv shows from the 60's--Surprise, Surprise. That is not the only problem to look out for. Here's what happens, where I went into the ditch and the simple way I was able to recover, but not without significant loss of face in the process.

When Access looks for the next AutoNumber value to insert into a new record, it DOES NOT USE the largest existing value to calculate the next one. Access DOES USE the most recently entered value to calculate the next one.

Oopsie.

When I reinserted that lost AutoNumber value to restore the Work Order Number, it reset that starting value. Unfortunately for all concerned, a lot of people were already in the database, busily entering new work orders. So, rather than having the owner boot everyone one so I could do a compact and repair on it, I let it go, reasoning, incorrectly, that we'd just pick up where we left off.

The trap was set. There were already several new records with higher AutoNumber values which had been entered between the time I started and the time I finished, no more than 10 or 15 minutes. The next two or three new records were able to reuse an incremented AutoNumber values previously lost due to deleted or abandoned records, but as soon as it tried to generate one that WAS in use, it blew up.

Well, not literally, but it might as well have. It simply raised the error that Access couldn't add that new record due to a conflict with an existing Primary Key value. Bad juju.

So, more recovery work, with the correlative loss of confidence. And this time, when the data was recovered, I DID have the owner boot everyone out and do a Compact & Repair on the Back End. Testing demonstrated that it was now using, correctly, the last entered value as the seed for the next available value, based not on the reinserted value, but on the last new value generated by entering a new work order (I hope that makes sense). We burned two or three new Autonumbers testing to be sure, which must cause heart-burn to their people who look for gaps in Work Order Numbers on printed certificates, but that one's treatable with anti-acids, and not a problem for customer relations.

All said and done? I would have been ahead to tell them to recreate that particular work order, with a new Work Order Number and regenerate the certificate to be mailed to their customer.

Either that, or do the job right and finish up every significant database change with a Compact & Repair.

It's just that simple, I guess, when you stop to think about it. Do a Compact & Repair and do not call me in the morning.



Thursday, August 4, 2016

The Lost Realm of Best Practices

First the good news.


Today's developer community seems to have gotten the bug to search out "Best Practices". Probably they try to implement them, but that's beyond my ability to assess, so let's call that one my "Best Guesstimate on Standard Practices".


Now the bad news.


The Realm of Best Practices is in the same place you will find Nirvana, the Lost Realm of Cardolan, Álfheimr, Asphodel Meadows and a nice little cottage with a small dock by the lake.


It's possibly worth the effort to look for it if you happen to be searching for enlightenment, fighting orcs and demons, or even just waiting out eternity. Just don't give up your day job while searching for it.


Sometimes, just good enough is, well, just good enough. And all we really need to do our jobs well, I submit, is to make things work out as good as we can.


If there's a better way to parse a text string than bog-standard:


 Split([ImportField1], ",")


well, then, so be it. I'm not going to waste my time fretting about it. If you want to share YOUR version, I'll listen attentively and maybe even adopt it next time.


If I can get the same results with an In() clauses as I can with Or'ed parameters, I'm not going to stop and ask BingGoogle for advice. Of course, with that one, it's probably not quite so simple as all that. Sometimes an In() clause isn't very efficient. On the other hand, if my code with an  In() clause works, doesn't drag like a flat tire on an overloaded hay wagon, and gets the right results, I'm good enough with that. I have a handy alternative or two in my tool kit, if need be. I'm just not going to burn my time and my client's money looking for the magic sauce of a Best Practice. Note I said "magic" not "secret". And yes, that's about how I see it. Your metaphor may vary.

Sunday, June 26, 2016

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.


Friday, June 24, 2016

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.