Saturday, September 17, 2016

A Field By Any Other Name is Just As Good

It's clear that Microsoft has truly renewed their commitment to our lovable little friend, MS Access. They have launched a series of Tutorials aimed at introducing MS Access to a generation that has little or no idea what Access is and how it works.

Outstanding.

The initial tutorial, however, set off a small controversy among some of my peers; some of us felt it was a bit lax in modeling good design practices. After a brief discussion, though, it became clear that the overall goal is more important than some minor points--points that can be debated anyway.

If it brings more developers to the table, and if they taste the flavor of our lovable little friend's power, and go on to adopt it as a tool in their own toolkits, that can only be seen as a Good Thing.

So, despite my initial misgivings, I am sharing the link here. I hope you do as well. The more the merrier.

Wednesday, September 7, 2016

Proof of Life

The A-Team (i.e. the Access development team) at Microsoft have done something unprecedented, I believe.

They've started putting their plans for MS Access on the Office 365 Roadmap. You can read more about it all on Anders Ebro's Blog here: Smiley Coder's Review of the Office 365 Roadmap for ACCESS.

You can find the Roadmap itself here: Roadmap for Future Access Enhancements

Note that the two items mentioned here have been the subject of considerable discussion among the Microsoft Access MVP Group for a long time, and the fact that The A-Team is moving ahead on them sends a powerful message to the many fans of our lovable little database tool.


The inclusion of the BigInt data type increases the compatibility between Access and modern versions of SQL Server. That's a good thing from any perspective.
Restoring the ability to link Access to .dbf format files addresses a HUGE pain point for a couple of industries. Most of us probably shrugged our shoulders when it was deprecated in Access 2013, but to those people who NEED this ability, it was a deal-breaker.


I couldn't be more delighted to see a public announcement... Well, at least not in the arena of software development.

Tuesday, August 30, 2016

It's Alive!

With a grateful nod towards the late, great Gene Wilder in his classic role in Young Frankenstein, I have the pleasure of noting the stirrings of life in our favorite little database tool, Microsoft Access.

What I hope and expect will be the first release in a trickle--dare we hope a stream or even a wave--is now available.


 

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.