How Many Forms and Reports Should Go in the Back-End Database File?
Spoiler alert! The correct answer is zero.
"You never tell a kid not to put peas up their nose," my mentor, Bruce Grandjean, admonished me. "All that does is put the idea in their head." (The peas soon follow.)
He told me this after I had warned some of our users not to change the system date on their computer. Over the years, a couple of creative users had stumbled upon this "clever" hack as a way to circumvent the auditing features of our tax collection software. Each one made a mess of their data. In my naive attempt to preempt that from happening again, I succeeded only in putting the idea into the heads of our new users.
I fear I've still not learned my lesson.
Front-End vs. Back-End Databases
Yesterday, I wrote about front-end and back-end databases for my Access 101 series.
Mike Wolfe
In my attempt to clarify that the only difference between the two types of files was their purpose, I wrote the following:
It's important to note that there is no functional difference between a front-end and back-end Access file.
For example, there is no "Save As Front-End" or "Save As Back-End" option in the File menu. You can add tables to the front-end file. You can add forms and reports to the back-end file.
The only difference between the two types of files is their purpose.
My concern was that new users would read the article about front-end and back-end database files and assume that they were different things, the way .accdb files are different than compiled .accde files.
In my head, the line, "You can add forms and reports to the back-end file," was implicitly followed by the line, "but of course you shouldn't." On re-reading the article, I realize that was not clear. To make matters worse, my use of italics on the word "can"–which I read in my head as "caaaan" with a sarcastic edge to my voice–could just as easily be interpreted as a synonym for "should." As in, "you should add forms and reports..."
But, of course, you shouldn't.
Conversation on LinkedIn
I realized the error of my ways with the conversation that played out on the LinkedIn comments on my article post.
Someone posted this follow-up question:
If I can put some forms and reports in the backend, why can't I put all of them in the BE?
Uh-oh. After I inadvertently informed my readers that they could stick peas up their nose, the first follow-up question I got was, "Yes, but how many?"
Luckily, another reader jumped to answer the question:
It increases the chances of corruption and therefore losing your data.
What follows is the remainder of the conversation between the original poster (OP) and me. I hope this will be instructive for other readers who, like the OP, read the words I wrote and not the ones I had in my head.
[OP]: Thanks. That suggests that there must be some guidelines for how many, the types, and the size of forms/reports to put in the BE. And why would forms and reports corrupt the data?
[Mike]: The guideline is that there should be zero forms/reports in the backend.
The forms/reports themselves don't corrupt the data. Having multiple users accessing the same forms/reports at the same time in the same .accdb/.mdb file but from different computers will corrupt the .accdb/.mdb file. If your data happens to be in that file, well, then you've lost that, too. That's why the best practice is to put a local copy of the front-end on every user's computer. If the front-end gets corrupted, you just give the user a fresh copy. No data is lost in the process. This also lets you make design changes to the front-end on a developer copy of the .accdb/.mdb file.
[OP]: Thanks, Mike. Then I could move all of my forms and reports to the BE since I am the only user. And I haven't used .mdb files since 2007 because they can't handle my multi-value fields. Is that correct?
[Mike]: Even if you are the only user, I still would not recommend moving your forms or reports to the BE. Even with only a single user in the database file, Access databases with form and report objects are much more susceptible to corruption than ones with only table objects. If it were me, I would not want to put my data at risk in any way. That's why I strongly recommend restricting back-end Access files to tables only.
