Active Topics

17. My Access Database doesn't Work! (How Not to Get Your Questions Answered in an On-Line Forum)

Publish Date -- Feb 27, 2021

• Diagnosis by Osmosis, or
  Patient: "Doctor, my head hurts. I need a brain transplant."
  Doctor: "Wait, I won't know whether to prescribe aspirin or recommend brain surgery until I know what's really going on."
• Her name was Magill, she called herself Lil, but Everyone Knew Her as Nancy, or
  Only the names were changed to protect the innocent. I'm going to call them "Larry", "Curly" and "Moe" < wink >.
• I Did It My Way, or
  Now I want you to confirm that My Way is Practically Perfect.

---

Years of answering questions in various on-line forums have led me to an unpleasant conclusion: It can be hard to ask a good question. These are not bad people and they are not stupid. They just don't really know what it takes to communicate a problem effectively to someone sitting at a computer thousands of miles away with only a few words posted in the forum to guide them. Lack of experience and lack of knowledge are the culprits and only repeated practice can change that.

I'm going to speculate a little on why that's the case for three of the most common problems I see on-line. And then I'll suggest a few ways to help you avoid them.

• Diagnosis by Osmosis, or
  Patient: "Doctor, my head hurts. I need a brain transplant."
  Doctor: "Wait, I won't know whether to prescribe aspirin or recommend brain surgery until I know what's really going on."

Sometimes people don't realize Access almost always supports more than one way to do many things ; more importantly, that also means there can be more than one reason for a problem that looks the same, especially to someone with limited Access experience. To take a really simple example, you can bind a table to a form directly, or you can create a query based on that table and bind that to the form. Most of the time, there's no important difference in functionality, but there are exceptions. For example, if an aggregate query is bound to a form (i.e. if it has a GROUP BY Clause), that query is not updateable. That, in turn, prevents any kind of data updates in the form to which it's bound. It's not a form problem; it's a query problem, but naïve users may not, probably do not, know that.

This problem might get presented in a forum as "Why can't I add new records in my form?" Actually, there could be a number of reasons (including the actual query problem), but it could also be the result of setting the "Allow Additions" property of the form to "No". Or even something else. Just as your doctor is going to ask many questions before scheduling you in for surgery, the only way for someone like me to be able to offer suggestions for you to try is to ask a bunch of questions designed to elicit a clearer picture of what you are doing and how you are doing it.

Related to that problem is the expectation that simply stating a problem is sufficient, without offering any context. MS Access is a very comprehensive application. It takes a broad set of skills to manage development of Tables, Forms, Reports, Queries and VBA or Macros. In addition to that, there are a number of properties for all of those objects. Without the context of a problem, it's usually hard, if not impossible, to make a good guess as to why it occurs.

• Her name was Magill, she called herself Lil, but Everyone Knew Her as Nancy, or
  Only the names were changed to protect the innocent. I'm going to call them "Larry", "Curly" and "Moe" < wink >.

You might be surprised by the vast number of Access databases built every year to track fruit: Apples, oranges, bananas and pineapples. Don't believe it? Nah, me neither. I think those fruits are actually something else entirely, and the poster is trying to "simplify" the problem by aliasing those things. Sometimes that's okay, but far more often attempts to simplify end up obscuring, omitting, and even distorting information in ways that impede communication. And that means it's much harder to get to a valid suggestion.

It's Just NOT That Simple, Is It?

Far worse than simply aliasing are those questions which deliberately omit several factors in order to make it "simpler". More often that not, one of those simplifications turns out to be crucial to understanding -- and correcting -- the problem.

I guess there is a fine line between too little information and too much, and sometimes a problem can be identified and resolved by a combination of educated guesswork and interpreting the clues found in the poster's question (I've seen hundreds of non-normalized tables over the years, and I recognize many of the symptoms right away). Nonetheless, it can be quite frustrating to offer a potential solution, only to be told, "That won't work because of such and such factor, which I left out of the first post to keep it simple."

• I Did It My Way, or
  Now I want you to confirm that My Way is Practically Perfect.

These are my least favorite kinds of questions. They come with a ton of emotional investment in addition to the investment in time and resources to create the relational database application. The poster has spent hours, days, weeks, even months on their Access database, and they really, really, really don't want to hear that it's not Practically Perfect, well, except for this one more little thing. The last thing they want to hear is that their table structure is not properly normalized and that's why they have to change forms, reports and queries to accommodate the pomegranates they just added to their Christmas Gift Baskets. They think they should have a code module to modify forms every time a new kind of fruit gets added to inventory to save all that work.

No, the answer is not modifying forms via automation every few weeks. The answer is starting over from scratch with a more appropriate table design. No one wants to hear that they must start over; what they want to hear is how to patch things up a little with some new bit of clever code. It's heart-breaking for anyone to be honest about their flaws, and just as often as not, these posters go away thinking it's just a matter of finding that one silver bullet function that will preserve the status quo without making them start all over. Maybe a different poster at a different forum will have that answer….

What Are You Gonna Do About It?

Fortunately, the answers are not that hard.

• The Truth, Whole Truth and Nothing But the Truth, So Help Me Codd*.

When you post a question, include a lot of detail, even though you think it's not necessary because you already KNOW what needs to be done, you just don't know how to do it. Include things like the version of Access you're using, Access 97 or Microsoft 365, the bitness, 32 or 64, whether the application is split into a Front End and Back End, whether you are using mde/accdes, and so on. It may not matter, but if it doesn't, the people who answer forum questions are perfectly capable of ignoring it. Besides, they might ask anyway, just to be sure…. And they might have a totally DIFFERENT solution based on the facts you actually present.

Include a full description of any error messages, including both error numbers and error descriptions. "It doesn't work" is worse than useless, to be honest. We KNOW it doesn't work or you wouldn't be here asking questions. What we DON'T KNOW is the circumstances in which it doesn't work, and the error number and error description go a long way towards guiding our analysis of that.

Tell us what you tried already, and what the results actually were. "I changed the criteria in the query, but it still doesn't return the right records." Okay, I'll bite. What were the original criteria? What were the results returned? What is the new criteria? What were the results returned? How is that different from what you expected, and why is it different? Is it wrong because it leaves things out, or wrong because it shows invalid values? We don't know your data, but you do. Anything you include may be the one clue that solves the case.

Don't be afraid to try stuff out. If a forum responder suggests you change the syntax in a function, please change it. And tell us what the result of that change was. Again, it might not be the answer, but it might be a clue that reveals the answer.

One of my personal pet peeves is a question like, "Can I use a pass-thru query in a report?" Well, did you try it? If not, why not? As far as I know, you can't ruin the Access relational database application if you try stuff out yourself, on a backup copy, of course.

Avoid Editorializing.

Microsoft is a big corporation; we know that. They do a lot of good stuff and a lot of stuff that isn't so good. We also know that. That's beside the point. What we need are the facts pertaining to your problem. Right now. It may feel good to let off steam, but it seldom helps get that solution for you.

Not every problem you encounter is the result of a new bug. Sometimes stuff happens. Leaping to conclusions isn't good exercise, it's too exhausting.

Share a bit of history. Did this relational database application perform as expected yesterday? Last week? If so, what changed? Sometimes something as seemingly innocuous as a new kind of data point can trip up an existing workflow. If incoming data, for example, always, always, always, consisted of only whole numbers in one field, changing that to allow for fractional values can easily break functions set up to expect only whole numbers. Nothing internal to your relational database changed, but it's no longer returning valid results, no fault of yours or your application.

• Honesty IS the Best Policy

  Don't worry about revealing company secrets, unless of course, the information really is a company secret. The fact that you are manufacturing sports equipment probably won't come as a surprise to Nike or Adida, especially if you work for someone in that same league. If you are worried about shoe sizes, tell us you're tracking shoe sizes. It really does help put the question into the proper context. If I see one more question about green apples and red apples….

• The Truth Sometime Hurts, But You Can't Cancel It Because You Don't Like It.

Be prepared to hear that experts who have spent decades as professional relational database application consultants and developers have a different idea than you do about what makes a good relational database application, and what doesn't. If someone tells you your idea isn't going to work, don't get upset, don't try to come up with arguments to defend it. Just try to rationally and reasonably evaluate the situation and decide how invested you are in the way things have always been. As a matter of fact, on more than one occasion I have told posters that I didn't think their Access database was properly set up, but that I also thought that the cost of retrofitting it might be more than it's worth, especially if a small change can rescue it. It goes both ways, actually. Sometimes, the only salvation for a poorly designed relational database application is really just to start over. And if that's the case, well, sometimes you just have to do what you have to do.




* Edgar F. Codd, inventor of the relational model for database management

---

Unfinished Puzzle Image by Gerd Altmann from Pixabay

Feedback is welcome and appreciated. ghepworth@gpcdata.com