Microsoft freaking Access.
It’s apparently possible to do a wide range of functional tasks with this misbegotten piece of compiled offal but the learning curve is damn near vertical. Maybe beyond vertical, like where you have to climb a face that’s leaning out over you.
Me and my supervisor, neither of us Access developers but generically at home in databases, me coming from FileMaker with some adequate chops at the SQL command line when necessary, she with her own background in various data architectures, trying to cope with an Access database that predates either of us here at the agency. Trying to make it do the simplest goddamn things.
Like yesterday, trying to make it so that when the status of a subsidiary organization is changed from “Active” to “Obsolete”, it pushes the organizational ID, the new status, and the date on which the status is being changed over to a different table in which each record is the from date and the until date of a given org’s status, i.e., a status history table. This is the kind of thing that should be almost effortless in any reasonable database environment.
She knows Access better than I do, so is taking lead. “Well there are two ways to connect a triggered event to the status field, it can be a SQL query or a code, which is written in visual basic…”
There don’t seem to be any tools for conjuring up SQL commands that let you reference the values of the record your feet are planted in, i.e., THIS recordID, THIS status, THIS org ID. So she has us diving into the vB code. Borrowing existing code form other written updates that autopopulate fields. But she can’t find any that dash off to write to a table other than the one associated with the form. We find a command that does open a named form and yes, when the status is changed we end up staring at the desired history table.
But an hour’s worth of attempting various things fails to get it to also create a new record there, let alone populate any portion of it with values brought over from the table and the record that we came from.
“We need some way of scribbling down values from the record we start with onto the back of some kind of virtual napkin”, I say. “Does Access have global fields, maybe?”, I ask. “Before FileMaker had variables, where you could just set a declared variable to the org ID and another to the status and then reference them when populating fields on the other end, we would use global fields to hold the information”. She shrugs. Not to her knowledge. “Or how about simply copying it to the clipboard? In even older FileMaker that’s how I used to do it, copy a value over here then go over there and go into the destination field and paste. Can we reference what’s on the clipboard? Can the script do a copy, like Control-C?” She looks through the array of commands in vB. There’s one command, Run SQL, that seems like it would be useful if we could tell it to accept parameters and use the parameters instead of hardwired values, but there’s no obvious way to do that.
I’m sure there’s a way to do it but good software design makes it possible to do what you want to do intuitively, where a basic familiarity with the tools clues you in on how to do stuff. Access flunks.
We’ll reconvene a few days from now and continue the attempt. At this rate it will take weeks or months to do what I could do in FileMaker, along with sealing off the existing status history with an end date, in about 24 seconds.
