Forum Discussion
Moving Away from MS Access
Your story supports my point about the relative security of the data depending on how its stored.
The analogy I like best is to compare Access to a locked safe. Stored in a closet inside your house where casual observers don't see it, that safe is reasonably secure, but if you haul it out on the front lawn where everyone walking or driving by sees it, you actually entice the bad guys to come back with a truck and haul it away. And once they have it under their control, they can open it at their leisure. Accdbs and xlsx files can be copied onto a thumb drive, or sent out as an email attachment, and opened at the leisure of the bad guy.
On the other hand, I also like the narrative presented by a VP in a company I worked at many years ago. She was fond of claiming that most employees drive to work thinking along these lines: "I hope I can do something really good today so I'll get noticed and maybe earn a raise." Very, very few employees drive to work thinking, "I really plan to screw up today so I can get fired."
Sure, there are bad actors, but if an organization structures itself around the belief that employees are evil and have to be treated as suspects, it has bigger problems than Access accdbs on its hands.
A little more background is the databases reside at an agency I used to be part of. There were a team of maybe six people who were responsible for any issues with the databases. Eventually I was asked to help out with that. Outside of taking a few Access classes, I'd had no experience with Access and to this day have never built a database from the ground up. Around 2014, I and the others who assisted with those databases were absorbed into another agency. The thought being why should the agency with all of the Access databases have their own "IT shop" (it wasn't really an IT shop, more of a first line of defense Help Desk type area). So at my new agency, the decision was made by the "powers that be" that the crew of six people (self included) who "maintained" the Access databases would continue to do so. Eventually all of them retired or moved onto other jobs, including the one who loved using VBA (nothing against VBA, but it's the devil when you know nothing about it). She was always on disability for health issues and eventually just went from that straight into retirement after bilking the system for as long as she could. (Another story in and of itself). So that meant the support of all of those databases (sixty-some at that time) fell in my lap. Because I was able to somehow keep them running smoothly and keep the users happy, they kept it that way. Fast forward to 2019, where I joined a team that supports O365 products, including Access databases. They came with me. Fortunately on that team is a person who is very knowledgable and savvy with Access and VBA and she helps me when I need it. But now that we're at the real IT shop, and are part of it, the visibility of these databases is no longer in the garage with the door shut, but out on the front lawn for all to see. And the "powers that be" are still saying "get them out of Access". So we're slowly working on converting the tables and data to SQL Server back-ends as a start, until someone finally decides what new technology they want to use to "get them out of Access". Where I work, Access is viewed as old technology. I have yet to hear a good reason to get things out of it other than security/encryption. Also auditors wanted some sort of logging feature to be able to tell every move made in each database. Apparently SQL Server accomplishes the logging and encryption of data (that's what I'm told anyway).
The main issue I'm having has been touched on several times. The people pushing to get things out of Access know nothing about the data contained in Access. An example would be an Access database developed in the early-to-mid 1990's that is still being used today. The consultants who developed it did a very nice job, and it has plenty of VBA (which I am unfamiliar with) and suited the users very well at the time. But as users, needs and developers changed over time, the database has as well. And there have been "several hands in the pot" so to speak. One developer loved VBA so used it very heavily behind the scenes. Others didn't know VBA at all.
So at the present time, our push is to start moving all data currently in an Access database into SQL server back-ends, while leaving the front-ends as they are. Obviously it depends on what the purpose of each database is, where the data is coming from, and many other factors to determine what technology will be used to ultimately "replace" Access. But the people tasked with that project are those that know little-to-nothing about the databases. I don't know if I'll ever see the end of this project while I'm still actively working.
All of you are appreciated and I can tell have vastly more knowledge than I do about the inner workings of Access, VBA, SQL Server, etc. For now, I'll just keep plodding along trying to learn SQL Server and trying to get the data into it. At the shop I work at, the "database" people create the SQL Servers and are responsible for them. But my coworker has familiarity with SQL Server Management Studio and is starting to show me the ropes (slowly but surely). But she is trying to wash her hands of Access support and it's like pulling teeth to get information from her to get all of this done. I honestly don't know why she just doesn't do it herself. But she has many new projects and is pushing towards retirement, so that probably weighs into it.
I hope this finds you all healthy and well and enjoying your 2022 so far.
Yes, indeed, the importance of KISS is something we all need to keep in mind.
Perhaps this meeting, which starts in a few minutes, would be interesting.
If you miss it, DAAUG posts videos of their presentations on YouTube later.
Brian_Hayes
In principle I could retire next year. If I wanted to live on mac & cheese and sleep on a park bench.
Good luck!- BrianWS1O I'm eligible to retire in 2026, but we'll see. Thanks for your insight. Appreciate it.
Brian_Hayes
I feel ya...I've been in very similar situations, and I'm trying hard NOT to visualize your circumstances too vividly because it makes me cringe and clench to imagine having to again go though those struggles with IT Creatures. Been there, done that, ready to retire early if I have to do it again.
The thing to remember is, people often over-engineer Access apps, and you should resist the urge to go nuts building fancy forms, reports and so on. KISS, as they say. Learn VBA; it really is fairly easy and quite powerful in the right hands, and some VBA skills are sorta transferable to newer, trendier languages like C#. Try to learn TSQL and let the server do as much of the heavy lifting as possible, it's much faster and someday if you do find some other mythical new technology to "get them out of Access" it will probably play nice with SQL Server, so if the server is already doing most of the work, converting should be less painful.
Other than that, all I can say is that I hope you're not too many years away from retirement when you can blissfully forget about all this BS!
