Quick Queries #106
By Richard Rost
2 days ago
What Happens When Your Access Developer Leaves? In today's Quick Queries we will discuss what to do when the person who built your Microsoft Access database leaves the company, including protecting the working system, locating files and dependencies, testing backups, documenting business processes, and deciding when internal staff or an outside developer can help. We will also cover Datasheet View, template databases, VBA troubleshooting, standard deviation, Excel automation, naming conventions, SharePoint, remote Access use, and other questions from the community. Nathaniel from Richardson, Texas (a Platinum Member) asks: Richard, I run a small business with about ten employees, and a former front-office employee built the Access database we rely on every day. It still works, but nobody understands how it works behind the scenes or what we'll do if it breaks. I can't hire full-time IT staff or become a developer myself. What are my realistic options? LinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, Access developer left company, inherited Access database, Access database maintenance, Access database support, Access database backup, ACCDE vs ACCDB, Access database documentation, Access database dependencies, hire Access consultant, Access Developer Network
More InformationTranscript So, the person who built the Access database for your business just left. And now everybody's afraid to touch it.
The good news is, if it's still working, you don't have to panic or immediately throw it out and start over. You got lots of options.
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today we're going to talk about what to do when your Microsoft Access developer leaves the company or gets hit by a bus. Either one.
The database is still running the business, but now no one's around to take care of it. Maybe it handles your customers, orders, inventory, scheduling, all that stuff. It's stuff that's way too important to just cross your fingers and hope nothing breaks.
Now, you don't necessarily need to become a programmer yourself, and you may not need a full-time IT person either. We'll talk about protecting what works, figuring out what you've actually got, and how to make a sensible plan for support without letting a maintenance concern turn into an unnecessary replacement project.
In addition to that, we've got answers to more questions from my forums, YouTube, emails, and lots of other stuff.
All right, let's get to it.
Today's first question comes from Nathaniel in Richardson, Texas, one of my Platinum members. Nathaniel says, "I run a small business with about 10 employees, and a former front office employee built the Access database we rely on every day. It still works, but nobody understands how it works behind the scenes or what we'll do if it breaks. I can't hire full-time IT staff or become a developer myself. What are my realistic options?"
Well, you don't have to throw out a working database just because its original developer left. The important thing is to protect what you've got and make a sensible support plan. So let's take a look at what you can do.
I've actually heard this story from a lot of businesses over my 30-year consulting career. This is a situation that a lot of small businesses find themselves in.
So you're a small business. Someone in the office was good with computers, so they became the computer guy. He knew a little Access, and over the years, he built a database that now runs the whole business. Orders, customers, scheduling, inventory, whatever.
And now that person is gone for whatever reason, and everyone looks at the database like it's some ancient artifact with warning labels all over it. Because I've gone into a couple of companies where there actually were warning labels, like sticky notes on the computer saying, "Leave this alone."
But the good news is that if it still works, you've got time. You don't have to become a database developer yourself overnight, and you don't necessarily need to throw everything away and start over. But somebody does need to be responsible for the database going forward.
So today we'll talk about a practical path. We'll protect the working system, find out what you actually have, decide whether someone internally can learn part of it, and determine when outside Access help makes sense.
Now, first, figure out which problem you actually have. Is the database broken right now? Are people unable to enter orders, print invoices, or get to the information they need? If so, that specific failure needs attention first. Let's begin with a grand modernization plan while payroll is due on Friday.
But in the situation we're talking about here, the database still works for everyday use. The concern is that nobody knows how it works behind the scenes, nobody knows how to make changes, and nobody knows what happens when something eventually does break.
So what you have isn't a disaster. It's a planning problem. A working application gives you time to make good decisions. So the initial goal should be continuity and understanding, not adding new bells and whistles. Put the feature wish list in a drawer for a moment.
And before asking about that new dashboard with the animated pie charts, make sure you know how the database works, where your data lives, whether or not you can restore a backup, those important things.
Now, before anyone changes anything, find out what you've actually got. What are you dealing with?
In many Access systems, the file that users open contains the screens, the buttons, the reports, the queries, and all the programming. We call that the front end.
If it's a shared database, in other words, lots of people use it, then the actual business records may be in another Access file on a shared drive, maybe a server, or someone else's workstation acting like a server. It could be an SQL Server database. I've even seen Access databases built off of back-end Excel files. It's not pretty, but people do it.
So have a knowledgeable technical person identify the application files, the location of your data, where your existing backups are, and any credentials needed to access the services connected to the database. That's the most important part, making sure your data is secure.
Then find out whether you've got an editable copy of the development database.
Now, sometimes what Access developers do is they compile the database into what's called an ACCDE file, and that means all of your workers won't have copies of the Access database they can actually make changes in.
So your old developer guy probably has the ACCDB file, the editable database, somewhere safe, probably on his computer. If he did all of his work on the laptop that he uses and he took that laptop with him, well, you're going to have to give him a call.
The next thing to look for is everything around the database, everything database-adjacent. An Access application can depend on linked spreadsheets, shared folders, other databases, scheduled tasks, printers, email accounts, network locations, and all that stuff.
And sometimes these services might have been set up under the former employee's account. For example, perhaps every month, I had this happen to one company, they received a spreadsheet that the person saved in a particular folder, then the database knew to import it.
Or maybe sometimes clicking a button emails invoices, like once a week. I had another company that did that.
Or maybe a report reaches out to a file sitting on somebody's old computer under their desk, surrounded by three years of coffee cups. I've seen it all, folks.
We call those dependencies. Those are the supporting pieces that the database expects to find.
I'm guilty to admit that I have one of those too. I've got a spreadsheet that I have all my YouTube members in, and my database looks at that because it's easy to update. So I download the update file, it goes in there, and the database uses that file. So I'm guilty of it too.
So make a simple list of what people know: import folders, regular imports, exports, reports, emails, all that stuff. You don't need to understand every technical detail of it right now, but you want to avoid discovering six months from now that the month-end processing depended on an account that was shut off when the employee left.
Next, let's talk about your safety net: backups. I harp on backups all the time in my videos. You want backups of both the application and the data, wherever each one lives.
More importantly, verify the backup can actually be restored. A backup that nobody has tested is like a spare tire that you've never checked. It might be there, but it also could be flat.
In the industry, we call that shorting your backup. It either works or it doesn't work until you test it.
Now, any investigation or repair or change or whatever should normally happen in a test copy first, not in the live database that people are using to run the business. The live system is often called production. Production is where the real work happens, and we don't test in production.
Well, I do, but it's just me here. I break things all the time, and I go, "Whoops," but I've got good backups, and I've got multiple rotating backups. I'm very backup-conscious.
Now, make sure you keep a known good copy protected. Keep a backup record of where it's located, who can access it, and what data it represents. Make sure you keep an off-site copy.
I drop my backup copies into Google Drive. That way, it's copied on their servers. I also have an off-site replication bucket with Sabi that I drop stuff in.
Now, this doesn't solve every problem, but it keeps an incoming developer and your business at a much safer starting point if something happens.
Now, the people using the database every day already possess valuable documentation. They just may not call it that.
So ask a few key employees to demonstrate their tasks. What do they do every day? Have them show you how an order gets entered, how an invoice gets produced.
Have management show the report that they need at the end of the month. Show what happens when a customer has a special price, a return, an unusual shipping arrangement, all that stuff.
Record those walkthroughs, or at least write down the steps. This is business documentation. Processes. As John Taffer says, you got to have systems.
So you've got software, but you still have people that use that software in a specific way every day.
Now, the technical documentation is different. That explains how the tables, queries, forms, reports, and all that stuff work together. An incoming developer needs both eventually.
But don't wait until your documentation is perfect before seeking help. Perfect documentation is a mythical creature. It's right up there with the empty inbox.
But start with what your people know, because their explanations can help a developer understand whether a strange-looking button is actually a critical part of the business process.
And as a consultant myself, when I used to go into businesses to build a new system, one of the things that helped me the most was sitting down and learning their old system so I could see how they were doing things. So your next person that's going to help you, a developer or someone, is going to want to know all that.
Now, my first recommendation is always to look inside the company for someone that might be interested in learning Access. And this is usually how you got that guy in the first place.
Look and see if someone else has been maybe shadowing him without knowing it. And this doesn't mean that you, the owner, have to become a programmer. You already said you don't have the time or the desire, and that's perfectly reasonable.
Learning the business is your job. You don't want to be bothered with inner joins and action queries and all that stuff.
Perhaps you've got an office manager, a bookkeeper, an operations person, a power Excel user who enjoys figuring things out and would like to take on some extra responsibility.
Someone who already understands the business has a major advantage over a consultant who's coming in. They know what an order is supposed to do, what an invoice is supposed to look like. They know how everything works. What happens on the last business day of the month.
I always said that it was easier for me to do the database part than to learn the business part when going into a business to build them a system.
Now, this person can start by just learning the basics: tables, queries, forms, and stuff. My Access Beginner 1 Free Course is a good starting point for anyone who wants to see if they want to learn Access. Sit them down and have them watch my videos.
Now, keep expectations realistic. Getting routine Access work is not the same thing as instantly being able to maintain a complicated application full of VBA programming.
A beginner may be able to handle some simple issues, make some basic changes over time, and then you can have an experienced developer handle the complicated stuff. That combination can work very well.
If you've got someone on staff that at least knows somewhat about your database and how it was built, that can be very helpful when an advanced programmer does come in to do little things.
I used to always try to train someone in the office to make those little tweaks and changes in the database so they didn't have to call me every two minutes when they wanted a button moved three inches.
Pick someone who's your junior light developer.
Now, if no one internally can take this on, or if the database is really complicated, then look for an experienced Access developer.
Now, taking over an existing inherited database is a lot tougher than building a shiny new system from scratch. I used to always hate it.
In fact, I used to go into companies and be like, "Look, I can build you a brand-new system that'll work better than your old database."
But that usually was the case when you go into a company and the database was built by someone who really didn't know Access. So only an experienced developer can really look at it and tell you that.
But a good developer is not just going to try to sell you a new system just for the sake of selling you a new system. They should understand that the first job is to learn what's there, preserve what works, identify any risks, and help you make decisions.
And when talking with someone, ask whether they have experience taking over existing Access applications. Ask how they communicate, how they handle requests, what information they need from you, and what their availability is, especially for urgent problems.
Don't assume that the occasional consultant is automatically available 24 hours a day. If your business needs same-day help during business hours, discuss that before there's an emergency.
I used to get calls from people on Monday morning at 9 a.m. like, "Hey, something happened over the weekend and we're down." And I'm like, "Great, I could be there Thursday. Sorry."
And that's another reason why it's good to have someone in-house who knows a little bit about the database. Maybe they can fix any little problems that come up.
Now, a lot of consultants are going to give you an estimate that sounds expensive just to evaluate a database. I used to see this all the time.
A brief introductory consultation and a detailed technical assessment are not the same thing. An unfamiliar Access application may have years of forms and reports and queries and VBA programming and all that stuff, hidden business rules.
So I can't tell you that every quote in the thousands is unreasonable. Sometimes the investigation really does take significant time to figure out what you're dealing with.
So what you should do is make the assessment a defined purchase. Ask what the developer will examine, how the fee is determined, what spending limit applies, and what you'll receive when the assessment is finished.
And ask for deliverables. Get something from him aside from just a number. Get a short system overview. Have him list what the dependencies are that we talked about earlier. Immediate concerns, missing files, missing credentials, that kind of stuff. Recommended next steps.
Don't just have him come in and say, "Yeah, two grand." Okay, what's that two grand for?
And this way, even if you decide not to continue with that particular developer, you'll be better informed than when you started. Hopefully.
If his scope is vague, ask questions. If the price seems out of line, get another opinion. And compare the actual work proposed, not just the largest number on the bottom of the estimate.
Now, one thing you should watch out for is turning a maintenance problem into an unnecessary replacement project.
You're going to hear, "Well, it's Access, so you need to move everything into the cloud," or, "You need something more modern." You're going to get people coming and telling you that Access is outdated and you need this new shiny brand-new whatever.
And I've done lots of videos on this in the past. Moving to the cloud doesn't automatically create a handover plan. It doesn't document your business rules or make the new system easier to support.
Anyone recommending a full replacement, you got to ask them, "What problem are we solving? Is Access failing to meet my business's requirements, or is it just something that you want to sell me? Are there serious design problems with this database? It's been working fine for years. Are there support limitations that can't be reasonably addressed? Or are you making this proposal simply because you, the developer, prefer a different technology?"
But I'll be fair, the same standard applies in reverse too. Continuing with Access should also be based on whether it still serves your business well.
Replacement has costs beyond building new screens. Someone has to understand the existing rules, move the data, test everything, retrain employees, and reproduce those important little workflows that nobody remembered until they disappeared.
A modern-looking screen is not automatically an equivalent business system.
Go watch Quick Queries 105 from last time for a full discussion of replacing Access with a web application. The short version is: Access first, decide second, rebuild only when there's a good business reason to.
Now, whatever path you choose, use this situation to prevent the next knowledge gap. Assign an internal person to coordinate database questions, keep documentation organized, and communicate with any outside developer.
Now, this person doesn't have to write code. They just need to know who to call, where the important materials are, and what the business needs.
Make sure the business owns access to necessary files, email accounts, credentials, backups, support contracts, and all that stuff. Don't let all of that live solely in one employee's personal account or memory.
And as the system changes, update the documentation. Keep basic operating instructions for staff, and keep enough technical information that a future developer can investigate without starting from absolute zero.
And if you're watching this as the person who understands the database, ask yourself a friendly but important question: If you are unavailable tomorrow, what would your co-workers need to keep the business running?
A little preparation is much kinder than leaving behind a treasure map with no X on it.
All right, so let's bring this back to the small business owner with 10 employees, no full-time IT budget, and no desire to become an Access developer.
Your developer leaving doesn't automatically mean you lost your database. If it still works, protect it. Find the application files, the data location, the backups, the credentials, all that stuff, and the outside dependencies.
Capture the knowledge your employees already have about how the business uses the system.
Then consider your realistic paths. You may have an interested employee who can learn the basics and become the internal point person. You can use an experienced outside Access developer for maintenance and repairs.
Most often, the best answer is a combination. Internal people provide business knowledge, and outside technical help handles the difficult parts.
So don't let a maintenance issue pressure you into an unnecessary rebuild. Get a clearly scoped assessment, understand the condition of the existing system, and then make the next sensible decision.
Now, if you are looking to hire an Access consultant, check out my webpage. I have something called the Microsoft Access Developer Network. It's a listing of other consultants, developers, tutors, and people who can help you with your Access database. Check that out. I'll put a link down below.
Also, while I was researching my video, my associate over at Lando Consulting, Steven Lando, recently wrote a good article about a broader problem of what happens to your company's data and system access when an employee leaves. So I'll put a link down below.
He covers the IT offboarding side of it: passwords, email, cloud accounts, company data, that kind of stuff. So check it out. Link down below.
And hey, while you're clicking on things, click on that like and subscribe for me. If you're not subscribed already, subscribe to my channel. It's free, and it lets YouTube know that you like this kind of stuff, including my videos.
And give me a like. It helps me out. It pushes my video out to more people. And I'll love you that much more for it. So thank you.
All right. Let's see what else we got in the grab bag this week.
I know it's been close to a month since I did the last Quick Queries, so it's been a while. I've been busy. I've been doing stuff. We'll talk about that in a few minutes.
All right, starting off on my website, we got a comment from Kevin. This is more of a comment than a question.
Kevin brings up something I probably don't talk about enough. It's when you should use Datasheet View versus a continuous form. He points out that every Access form already has a Datasheet View built in. And for a basic grid of records, it gives you sorting, filtering, column resizing, and all that stuff without having to design the continuous form yourself.
And Kevin's right. I probably underutilize Datasheet View in my videos.
I personally prefer continuous forms because I'm a control freak. I like to be able to decide exactly where everything goes, use different controls and buttons and combo boxes, conditional formatting, pictures wherever I want, all that stuff.
If I'm building something polished for myself or even an end user, I usually prefer a continuous form.
But if all you need is basically a grid of records where someone wants to browse, sort, filter, resize columns, and work with the data, Datasheet View is fantastic and takes almost no work.
You can even switch between Form View and Datasheet View with VBA, like I showed you right there.
So I wouldn't say one is better than the other. For data entry and a polished custom interface, I generally lean toward forms and continuous forms. And for browsing and manipulating a bunch of records in a grid, Datasheet View can absolutely be the better tool.
And Kevin's right, I should probably show it more often. And I'm probably going to do a couple of videos on it.
Datasheet View is also awesome if you're building a database for someone who is used to Excel and they're having a hard time giving Excel up. Because then you could say, "Here, look, it's just like Excel." You can slowly wean them off of it. I used to do that all the time.
Next up, we got Lee asking whether, now that his database is basically finished and full of real data, he can make another copy, delete the data from the tables, compact and repair it, and use it as a clean blank database that he could reuse or share.
Yes, absolutely. That's a perfectly reasonable way to make a template copy.
Just remember that not everything in your tables is necessarily user data. You might have lookup tables or settings or configuration stuff.
I had a whole table full of states and other counties and stuff like that, and I scrubbed the database to send to the client for the production, the final production one.
And Alken even mentions that, like cities and countries and stuff. So make sure that you don't delete stuff that's needed, like a settings table. You want to clear the transactional data out without wiping out the stuff that the application actually depends on.
Thomas also mentioned that another approach is, if you want a particularly clean distribution copy, create a brand-new blank ACCDB file and import only the objects you actually want. That leaves behind all of the accumulated development junk that can build up in the database over time.
Personally, I keep three working copies. That's what I said down here.
Keep the working development copy with your current test data. Keep a full backup copy with real data. And then keep a clean backup template copy with only the required reference or setup data.
And I did do a whole video on resetting your database, where essentially what I do is you can create either VBA code or macros that just go through and delete the stuff that's like production data. Delete your customers and your orders and your contacts and that stuff, but leave the settings stuff behind.
That way, you just click one button and it'll go through and scrub your data for you. There's a link to it right there. I'll put a link down below.
Next up, we got Will. He says that when he first opens his Access database, anything involving VBA throws a "Return without GoSub" error. Here it is down here, right there. "Return without GoSub."
But here's the strange part. If he opens an event procedure in the VBA editor and closes it without changing anything and lets Access save it, everything starts working normally. So what's going on?
Well, this turned into an interesting discussion because there's really two things going on here. There's a whole bunch of stuff. I'll put a link to this thread down below. There's a bunch of people who chimed in on this one.
Thomas said that he actually tested them himself and pointed out that a stray Return compiles just fine, but blows up at runtime. And I tested it too, right there. And sure enough, he's right.
So congratulations, Thomas. You helped find me another one of my other errors from my Error video series.
I've been doing error messages lately, so I'm getting a lot of emails about error messages. I'm like, "You know what? I'm just going to start going through them and make a video on all the errors." So when people search for them, I don't have to keep answering the same questions again.
But in Will's particular case, I don't think there's necessarily a stray Return sitting there. The big clue is simply opening a procedure and saving the VBA project makes everything work again.
That makes me suspicious of a stale or corrupted compiled VBA project, like a missing reference or possibly even corrupted metadata.
And then Alken, whose name we finally got, he was always Atokin for the longest time. Now we know his name is Alken. And I still have to get your flag on there for you. He's from Turkey, so he wants the flag on there. I got to add that to the system.
But he said he was thinking along the same lines and suggested decompiling, recompiling, references, and doing a compact and repair. Basically, the troubleshooting sequence. Run down the troubleshooter.
Whenever you got any weird problems, always run down the troubleshooter. There's a whole long list of stuff here. I'll put a link to it down below.
Make a backup first, compact and repair, go into the VBA editor, click Debug Compile once in a while, but definitely do it this time. Check Tools, References for anything marked missing.
And if it's still acting weird, decompile and then recompile the database. Instructions for decompiling are on that troubleshooter.
And, of course, check your startup code and your form load events for an actual Return statement, in case it happens to be there.
And if it's still acting up, take two aspirin and call me in the morning. Or post something on here and we'll keep digging into it.
Next up, we got Kenneth. And he's asking whether he can calculate standard deviation from data in Access, preferably in a query, or whether he needs to export everything to Excel first.
Now, you definitely don't have to export the data to Excel just to calculate it. You can calculate standard deviation yourself from the underlying formula if you want to.
And John Davy, one of my moderators, actually posted the population and sample formulas in the thread and explained the difference between dividing by N versus N minus 1. He did a lot of stuff in here. That's pretty cool. He's got a little chart here with the stuff, and that's, yeah, I love it. Love you, John.
And then Kevin Yip brought up something that I really like. You can actually use Excel's calculation functions from Access through automation.
So if you've got Excel installed, Access VBA can create an Excel application object and call functions from Excel's function library. And Kevin posted a sample right here. I'll zoom in for you guys. And that's really cool.
Now, there's an important limitation that Kevin also pointed out. You can't simply turn that into your own custom aggregate function and drop it into Access the way you'd use Sum or Count.
You'd have to gather the values into an array or otherwise process the records and then pass those values to the function.
And he also pointed out that SQL Server has a standard deviation function. So if your backend is SQL Server, that's another option and you don't need Excel at all.
But this discussion reminded me of something that I've wanted to cover for a while, which is using Excel's calculation library from Access.
I've done a bunch of videos already on Excel automation where Access creates workbooks and fills in cells and formats sheets and saves files and so on. But I haven't really done a lesson specifically about borrowing Excel's function library.
So I'm putting that on my developer list.
And if you want to learn a little bit about Excel automation, check out this video. That's where I show you how to actually create an Excel workbook file from Access. It's really cool stuff. A lot you could do with it. I've only scratched the surface.
All right, next, let's head over to YouTube. And in case you missed it, my channel is 20 years old now. It just passed my 20th birthday.
Next year, my YouTube channel can drink in most states. And it already can drink over in Europe. That's why it likes to go to England to visit Alex as much as it can.
But I just posted this yesterday, and I've already got a whole bunch of people on here saying thanks and thank you and congratulations and all that. And I really appreciate all your support.
Thank you so much, everybody, for being part of this journey and for letting me do what I enjoy doing, which is making good content for you guys. So thanks.
First up on YouTube, we got Steve, who took my laziness one step further.
This video is about putting a shortcut on your Quick Access Toolbar to be able to go into the form module without having to go to Design View first.
So he added Open Form Module to his own custom right-click menu, but only when he's in development mode. So now he saves himself another quarter of a second every time he wants to get into the form's VBA.
I approve. Those quarter seconds add up. So Steve, by 2047, you'll have earned yourself an entire coffee break.
All right, next up, we got Georgia telling me to slow my horses down.
Well, I don't have any horses. I'd love to have a horse, but I can't afford one, first of all, and I can't handle the upkeep and the maintenance, and I don't want to shovel poop.
It's bad enough I've got two black labs I live with. I got to clean up their poop all the time.
Anyways, a couple of you have mentioned that some of my shorts do sound like I'm talking pretty fast.
Now, here's what's happening. YouTube Shorts have a three-minute limit, and sometimes I'll record one of my regular videos and it comes out to three and a half or four minutes, occasionally four and a half, and my processing software will speed it up to squeeze it under three minutes.
Now, if it's only a few seconds over, if it's even like three minutes fifteen or three thirty, you'll barely notice it. But if it's got to squeeze four and a half minutes into three, then I start sounding like Alvin and the Chipmunks.
Now, I can automatically chop out those little pauses with silence detection instead, but I personally hate those videos. I can't stand that. They sound choppy and unnatural.
So I'd rather speed it up a little bit and preserve the natural pauses, but I'll try to keep an eye out for it.
If one sounds ridiculously fast, then maybe I'll just have to make two shorts out of it. I don't know. But that's why I sound like I've had six cups of coffee, because I normally don't talk that fast.
But thanks for bringing it up.
Next up, I'm Just Saying 5505 wants to know how to get the most current record at the top of a list automatically instead of the first record created, the oldest.
Well, the way that Access continuous forms work is that normally new records go on the bottom. But if you want to flip it, I do have a video that tells you how to do just that. There you go. Go watch that one.
And funny enough, the URL for this one is 2112. Yes, rock on. But check this video out. It'll explain how to do that.
Next up, we got Imran. He asked a question about Fitness Database 8 in the Fitness Database series. It builds an Access database to track things like fitness workouts, nutrition, all that stuff. I got more coming on those. People have been asking. There's more Fitness Database videos coming. I've been busy on other projects.
And Imran says he likes the database, but he notices that I sometimes start by laying things out in Excel, and he's asking if I could show how to actually build the fitness database itself in Excel.
Well, first, thanks for your question. But the important distinction here is that when you see me using Excel at the beginning of an Access video, I'm not actually building the database in Excel first.
I'm basically using Excel like you would graph paper. It's a quick visual way for me to sketch out the fields and to figure out how I want the tables organized.
In fact, I did start tracking a lot of my own fitness information in Excel. And if all you want is a simple meal list or a workout log or something like that, Excel is perfectly fine. There's no reason to have to build all that into a database.
But once you start wanting foods related to meals and exercises related to workouts and measurements and people and dates and all these different pieces connected together, that's where you really want to use a relational database. That's what Access was designed for.
So I wouldn't try to reproduce the whole fitness database in Excel. You can certainly learn Excel and use it for the simpler pieces. And I've got Excel courses if you need them.
But if you want to build an actual application around the related data, that's when you move to Access.
So yes, I do have a full Excel course too. So check it out. Level 1 is free, just like my free Access class. There you go.
All right, next up, we got ZVIG1. He's commenting on one of my video quizzes, and he's saying, "What happened with the weekly Q&A?"
And, of course, Cruisins, Sweets and Treats replied that I've been on a run of video quizzes lately.
And yeah, I know. I've gotten a few comments about this. And I talked about this a little bit in the last Quick Queries 105.
And it's been about a month since I've done one of these, which is definitely longer than I intended. But I've been spending a lot more time on production of my bigger projects lately, my Access developer lessons.
I've been working on the SQL Server course, my handbooks. I just finally finished putting together all the handbooks for all my developer lessons. That's been a big project. And I got a bunch of other stuff going on behind the scenes.
Quick Queries and TechHelp aren't going away. I'm just not getting as many of them out right now while I'm trying to finish up some of these bigger projects.
And that's why you've also been seeing so many of these little video quizzes. They're quick for me to produce, and they keep something fresh coming up on the channel every day.
So you guys seem to have fun with them, and I like having fun making them.
Plus, if I go too long without posting anything, I actually start getting emails asking if I'm okay. So this way, you guys at least know that I'm alive and I haven't been kidnapped by Klingons. Or Ferengi, that could be even worse.
But yeah, I'm honestly having a lot of fun making these quizzes. And it's not just that they're easy and quick. I built a whole Access database that does most of the production work for me.
I can literally feed it the transcript from one of my classes, and then AI helps generate the quizzes. It generates the questions and the answers. It puts together the screenshots for me.
It literally goes into my video at the timestamp it needs to and pulls out the screenshot and puts it in the database automatically for me. I built that whole system.
And so now I've got the production time for one of these quizzes down to about 10 minutes. And most of that is me recording it.
I refuse to do AI narration, folks. You will always hear my voice. Trust me.
The screenshots, the images and stuff, that might all be AI-generated, but what you're hearing is me. No AI is going to mess up as much as I goof up my audio. So don't worry about that.
But I may actually do a video showing you how I built my system because it's actually pretty cool. I use Access to automate as much of my production work as I can.
PowerPoint still handles some of the one-off slides like this one. This is PowerPoint. Here's PowerPoint, image, PowerPoint.
But for stuff that I can automate in Access, I will. And then I'll switch over to PowerPoint if I need something unique.
So no, the weekly Q&A and TechHelp aren't dead. It might just not be weekly anymore. There might be fewer of them for a while while I get these larger projects finished.
But in the meantime, I'm going to keep doing the quizzes because they're fun, they're quick, and they apparently also serve as proof of life. So I'm okay.
I'm also going to start doing some shorter explainer videos more often too. Like I've been doing the error messages. Seriously, those are pretty easy to do, and those are something people always search for.
You get a problem and you don't know what's going on with your database. You go to Google and you type in, "MS Access, whatever the error message, error 3021."
And hopefully my goal is that I show up right there. There I am right there. See? Oh, look at that. I rock that keyword.
And that's my goal. I want to be the guy that shows up whenever you search for anything Microsoft Access-related.
So that's what I'm going to be spending my time on too, making good searchable videos for you guys. So I can not only help me, but it helps you. I want to be able to answer your questions.
So if someone types into Google or asks an AI, "Hey, what's this problem?" I want my videos to show up so I can answer that problem for you. It's a win-win. Win for you, win for me, win for Google because then they get advertising dollars when you look at ads on their site.
Anyways, so yeah, I'm still good. So, as you can see, we've got Quick Queries coming at you right now.
Next up, we got Amos asking why I haven't made a video specifically showing how to build an employee database.
There are hundreds of specific database types I could build. Employee database, inventory, I've done. School databases, medical databases, property management databases. I might do one of those.
What I'm really trying to do, though, is teach you how to use the Lego pieces. I want to teach you the individual building blocks: how tables work, how relationships work, how to build queries, how to build forms, how to build reports, VBA.
Once you understand the pieces, you can assemble them into whatever kind of database you need.
An employee database isn't really fundamentally that different from a customer database or a student database. You got people, you got contact information, you got related records, you got maybe departments, positions, attendance, payroll, whatever your particular business needs.
But those pieces are all the same. You're just rearranging them differently.
Now, if there's something specific you'd like to see in an employee database, absolutely post a comment. Let me know. Tell me, don't just say "employee database." That's like when people say, "My database doesn't work." Well, okay, what specifically isn't working?
Tell me what you need to do. If there's an interesting feature or a problem that I haven't covered before, then I could certainly make a video on that topic.
But my goal isn't necessarily to teach you how to build a specific employee database. It's to teach you enough Access so you can build yours.
Like my Fitness series. I'm teaching all kinds of cool tech tips and tricks and techniques and stuff in that Fitness Database series.
A lot of people are saying to me, "Well, I don't care about fitness. I don't want to track calories and track workouts."
That's not the point. The point is I'm showing you all kinds of cool stuff to do in Access. The fitness is just the background of it. That's just the backdrop.
You could take a lot of those techniques from that Fitness Database and apply them to building an employee database.
So have fun. And if you got a specific problem, let me know, and maybe I'll make a video on it.
Oops, wrong button.
I got two buttons. I got two hotkeys that I always use. My left hand hits one hotkey for stopping and starting recording of the video that you're hearing right now. And my other one is for my voice dictation when I'm actually voice dictating text, like typing in a forum or something.
I do this. There's that. I'm using Whisper Flow. And then when I'm done, I do that and it turns it off.
And I always forget which one I'm doing and I hit the wrong one.
I got a Stream Deck too. I should probably use that, but I don't know. The buttons are too chicklet-like for me. I like my laptop keyboard better for most things.
Anyways, next up, we got John.
John asks why I put the control type at the end of my control names. Remember, chkCustomer, for example, instead of chkCustomer, which would group all the checkboxes together alphabetically.
And that's a perfectly valid way to do it, John. If that works better for you, then absolutely do it that way. That's the beauty of naming conventions.
The important thing is that you've got a convention and you're consistent with it. That's the tricky part, consistency.
Now, the reason why I prefer everything at the end is that I don't necessarily want all of my checkboxes together. I might want all my first name stuff together. I might have FirstNameLabel, FirstNameText, FirstNameCheckbox, FirstNameCombo, whatever.
So I want all my first name stuff grouped together when I drop down the format list. Instead of all my labels being over here and then all my text boxes over there and all my checkboxes over here.
It's the same reason I don't like traditional Hungarian-style notation. tblCustomer. I've always preferred CustomerT, CustomerQ, CustomerF, and so on.
And that's just the convention that I settled on when I started working with Access, and I've been using it for 30-plus years, so it's second nature to me now. And I'm not changing it. That's how I'm doing it.
I'll be doing it that way. If you want to watch my videos and learn from me, you're going to see it that way. That's just how I do it.
There's no Access law that says my way is correct and yours is wrong. If you're working for yourself, use whatever makes the most sense to you. If you're working with a team, then use whatever convention the team has agreed on.
And if you're following along with my classes, then obviously it'll be easier if you use mine. But tomato, tomato, potato, potato. Be consistent. That's all I really can say.
There's no right or wrong way to do it.
And honestly, the reason why I started putting T's and Q's and F's and stuff on the end of all, like CustomerTable, CustomerForm, CustomerQuery, is because in the old, old versions of Access, like version 2.0 when I started working with it, no, I never used version 1, I started with version 2. I think it was like 1994.
But when you were going through the wizard to build a form, and you had to pick the control source, or the record source, excuse me, you had to pick what table or query the data was coming from, they didn't separate them back then.
They put all of the tables and queries in the same combo box dropdown. So you would see Customer, Customer, or Customers twice. No idea which one was which.
So that's when I started on my own putting T on the end of my tables and Q on the end of my queries so I could tell them apart.
Now, it's not a problem anymore. They fixed that issue. But that's why I started all those years ago putting T's and Q's and stuff on the end. And it still works great.
There's still often a lot of times, like when I'm writing VBA, when I'm coding, that it's important to be able to look at a recordset declaration or something and say, "Okay, Select Star From CustomerT." I know that's my customer table.
So I kept with it. It makes more sense to me than tblCustomer. I never liked Hungarian notation.
So there you go.
And I did it again. I just did the same thing again. Two questions in a row.
All right. Next up, we got Georgio, who says, "What you want to do is call this guy in the form's Open event," and he's asking which guy.
Good question, Georgio.
This guy is the MoveDisplayFields function that we built earlier in the full video. You call that function from the form's On Open or On Load event so the field layout gets applied as soon as the form opens.
Now, this is one of the drawbacks of making shorts. See, all of my shorts are short excerpts taken from my longer TechHelp videos.
This one's about a minute and 41 seconds long. I have my AI try to determine which segments of these videos would make good shorts that stand on their own, and it's not always perfect.
Sometimes it does a great job at picking out little segments that I can use for shorts, but it's not always perfect.
So when you hear me say something occasionally like, "This guy," then that's the thing I was probably talking about right before this short mentioned.
And I actually introduced it about five minutes earlier in the full video.
So if you ever run into this, check the full video. You'll always find a link to the full video in the description below the short. And that way you'll be sure to get all the context leading up to that particular clip and you'll know who this guy is.
And yes, I'm half Italian, so I can say that.
And here's the full video. I'll put a link to it down below.
Next up, we got Steve, who, first of all, says he always rethinks his designs after watching my videos. That's the point. I'm here to give you ideas, things you can try and change and do.
It's like back in the day, every time I learned a new technique on how to do something cool. Like, oh, when I first learned a recordset, I'm like, "Damn it. Now I got to go back and redesign half my database to use recordsets."
That's right. Every time you learn something new, you want to go and add it and do new things.
And Steve is saying, have I rethought my decision on using tab documents versus overlapping windows?
No. What you're seeing, Steve, is the AI-generated screenshots that I'm using. All of these screenshots that I put in these images, like with the little penguins and the Star Trek stuff in the background, this is all AI-generated.
And I've tried to tell the AI, "Hey, stop using tab documents. I don't use them." I literally have told it to do this.
And you'll see in some of the screenshots I used earlier in today's video, like this one and that one and this one, these are all AI-generated. So these are not my databases.
Now, I do have some coming up that I haven't recorded yet. And I've been trying to tweak the prompt to use. And you can see here, I got it using regular overlapping windows. See, there's a better one.
Although that one looks very Windows XP.
And yes, I'm doing a video on RegEx regular expressions pretty soon.
That's the old tab document.
But yeah, no. So all of those AI images, I'm trying to get the AI to use my standards, but it's tough trying to get it to tweak it.
I'm surprised that it can reliably generate the screenshot without me having to do much. So I'm very happy with the results. I've given it a whole bunch of sample screenshots to work with, and it just tries to do its best to put it together.
I'm not kind of complaining that it's using tab documents. But no, I have not rethought my decision on this. I will die on this hill. I prefer overlapping windows. I always have.
I want to be able to have multiple things on the side of each other. If I want to move around, that's just my personal preference.
And again, it goes back to naming conventions. That's how I like it. So that's how I'm going to build it.
Next up, we got another field selector video. This was a popular video.
Cyberman1964 is basically asking, can I make those field selection checkboxes disappear when you don't need them?
Yeah, absolutely. The checkboxes are just regular controls. There's nothing saying they have to stay visible all the time.
These little guys right here, just make a little button down here that hides them. Say, StateCheckbox.Visible = False. And then make another one that shows them. Or make another checkbox that turns all these ones on and off.
There's all kinds of things you can do. You can make a little Choose Fields form that opens up that controls that stuff too. Just remember, you got to reference them across the different forms.
I left them visible in the video because I wanted to keep the example simple and concentrate on the actual field selector technique.
But once you got the Legos, you could put them together however you want.
If you're building this for an actual end user, I'd probably hide those somewhere and then display them when the user clicks a button to customize the form. But it's completely up to you.
Yes, absolutely. And you can still utilize their values even if the checkboxes themselves are hidden. The fact that they're not visible doesn't mean that you can't use them.
I reference hidden forms' fields all the time. And no, it's not a silly question. Remember, there are no such things as silly questions. Only silly people like me.
Next up, we got Namesh. He's asking about my No Current Record video.
He says, "Can we use the RecordCount property instead of checking for both BOF and EOF?"
Yes, you can use RecordCount, but remember, there's a catch. With a recordset, RecordCount is not necessarily reliable immediately after you open the recordset. You have to visit the end of the recordset first.
So you have to issue a MoveLast. Then Access knows how many records are in the recordset. Then you can come all the way back to the beginning with MoveFirst and do what you got to do.
So if you want to go to the end and then come all the way back, yeah, you can use RecordCount.
Now, if all you want to know is whether or not the recordset is empty, I prefer checking BOF and EOF.
Now, if immediately after opening the recordset, both of those are true, you know you've got no records. You don't have to navigate anywhere.
And I got a whole separate video that explains why you want to check for both of them.
If you're moving around inside a recordset, you got to check for both of them to make sure that you have no records.
If you just open the recordset, then you can just check EOF because Access will usually start you on the first record. Pretty reliably start you on the first record.
If there are records, and if there are no records, then you'll be at BOF and EOF. So picture it like two little spring blocks that move together. If there's no records, then they push together and BOF and EOF are both in that little box. They're both true.
That's usually how it works. Go watch this video. I explained it better there.
Next up, we got JCWinn. She's asking for more in-depth videos about using Access to SharePoint.
She's already using SharePoint lists at work and recognizes some of the drawbacks, but SQL Server isn't an option for her environment.
Now, I already did a SharePoint seminar. I think I did this back in 2020. About an hour long, it's got five lessons. Covers all the basics: connecting Access, setting up your site and your lists, permissions and distribution, tracking and security, all that stuff.
It covers pretty much all the SharePoint material that I think is important for Access users.
If you're running into some of the reasons that SharePoint isn't working for you, those are the reasons that I have as well. SharePoint has never been my favorite Access backend.
In fact, right on the seminar page, I tell people that if your organization isn't already standardized on SharePoint, I highly recommend SQL Server instead. I find SQL Server to be more flexible, more powerful, and frankly, easier to work with.
Now, in your case, you said your employer won't let you use SQL Server, so I completely understand why SharePoint might be the tool that you've got to work with. Sometimes you've got to deal with what you've got to deal with.
If your whole organization is already using SharePoint, it can certainly get the job done.
But I personally won't be doing another series of SharePoint videos. Microsoft has changed the SharePoint web interface since I recorded that seminar. Since I said on the seminar page, I don't currently plan to update it.
For me personally, SharePoint is pretty much a dead end as far as new Access videos go. Power Apps is something that I might do more with when I have some time after I get a lot of this other stuff off my plate first.
When I have a choice, I'd much rather connect Power Apps and Access to SQL Server. I hope someone's been in, because SQL Server is definitely more flexible than SharePoint.
Talk to your employer. Maybe they could set you up with your own SQL Server that you can use. It's not hard. If they got IT guys setting up SQL Server, they could set you up a database easily. Trust me.
That's just my opinion.
Definitely check out the SharePoint seminar. Maybe everything that I've got in that seminar will help get you working.
The underlying Access concepts haven't changed much. They're still useful. Even though Microsoft's SharePoint interface on the web has changed since 2020, the way that you work with it is pretty much still the same stuff.
Good luck with that.
Next up, we got Chris. He's talking about my Hide Access Part Two video.
This is where I show you how to hide the Access interface so your users see more of your application and less of Access itself, including the ribbon.
And Chris says the technique didn't hide the ribbon for him on Microsoft 365 and asks whether there's an updated version.
Well, Chris, I'm using Microsoft 365 myself, and this has not changed. The same VBA command still works. DoCmd.ShowToolbar "Ribbon", acToolbarNo.
Microsoft still documents the ShowToolbar method, and this technique is still perfectly applicable to the current version of Access.
So if it's not working for you, I don't think you need an updated version of the video. I think you should go back and check the steps and make sure that the code is actually running and that you've got Ribbon spelled correctly.
There's all kinds of little things it could be. Make sure that the database is trusted. It'll have to be in a trusted folder.
Try putting that one line behind a button and then clicking on it. If that hides the ribbon, then the command itself is fine. And then the problem is probably wherever you're calling the code from.
That's how I troubleshoot it.
But if you're still stuck after checking everything, post a comment in the details or in the forums on my website and maybe we'll try to give you some help with that.
If I had a nickel for every time I had a comment or heard this from someone who said that they thought Microsoft discontinued Access, I'd be able to buy myself a nice steak dinner. I'm serious. I get this at least twice a week.
All right, next up, we got K.R. Moglu. K.R. Moglu, K.R. Moglu. I think. I don't know. It's hard with these usernames.
Anyways, he's not really asking a question here, but he's been watching my videos for a long time and wanted to say some very kind things about Alken Toyken, who's been helping people in my forums lately. I mentioned him earlier in today's video.
He says he knows Alken's work from Turkey and has a lot of respect for his Access VBA and Microsoft Office knowledge.
He also says that Alken has helped him solve a problem in just a couple of hours that even Stack Overflow couldn't help him with. Oh, okay, that's how he phrases it.
And yeah, I've really appreciated having Alken around too. He's been very active in my forums lately on my website and has been really helping a lot of people out.
One thing that I love is when experienced Access developers come to my community on my website and share what they know.
I've got people of all levels on my website, from complete beginners all the way up to developers who've been doing this stuff for decades. And everybody benefits when the experienced people are willing to help the newer folks.
And so Alken definitely is welcome on my site. I appreciate everything that he's contributed so far. And I thank you, K.R. Moglu, for helping to call him out and give him a shout-out, and I'll make sure that he sees this.
I've always thought that I love when experienced developers like to help the beginners. And I've always felt myself that the best way to learn something yourself is to teach it.
You can learn it and then do it and then you become proficient with it. But the way to really become an expert in something is to teach it to someone else.
I've become a much better developer myself once I started teaching it to other people. Because your brain thinks in a different way when you have to digest it in your mind and then regurgitate it in a way that is understandable.
And I hope I do that well. I think that's one of my superpowers.
But it's a whole level of proficiency to be able to help someone else. And it makes you a better understander of that knowledge yourself.
So thanks for the kind comment.
All right, we're going to finish today with CaramelCream85. I love that handle. Caramel cream reminds me of cream soda.
This is a comment on my video about whether you should replace Access with a web application.
Caramel said their company was just acquired and apparently their new uptight IT guy, as they put it, is using the acquisition as an opportunity to holler about Access like it's going to eat him alive when he's not looking.
Well, maybe we should serve him up to Access with some fava beans and a nice Chianti.
But seriously, yeah, there's an industry-wide push now toward everything's got to be in the cloud. Cloud this, cloud that.
And I understand why. Companies have remote workers, people traveling, people on tablets and phones, and everybody wants to get their data from everywhere.
But needing remote access does not automatically mean we have to get rid of Microsoft Access.
I've done tons of videos on this showing different ways to use Access databases remotely.
If you're on a tablet, one of the easiest solutions is Remote Desktop. You're still running Access on a Windows machine. You're just controlling the Windows machine remotely from the tablet.
I do this all day long myself when I'm in the, let's say, the throne room and I have to get on my database. I just pop up my tablet. There you go. I use it all the time when I'm traveling. It works great.
If you need multiple people in different locations working with the same data, another option is keeping Access as the front end and just moving the tables to SQL Server in the cloud.
Now everybody can work with the same centralized data, and you still keep all your Access forms and reports and queries and VBA you spent years building.
Now, if the requirement is specifically, "This application has to run entirely inside a web browser," then yeah, that's different.
It doesn't natively turn your forms and reports into web pages, although I am working on something to do that. I got something coming. Just hold on a couple more weeks or months.
But at that point, you're talking about something completely different. You're talking about building a web application. Power Apps or some other browser-based front end.
But that's a business requirement that should be evaluated on its merits, not just throwing away a perfectly good application because someone decided to walk in the building and yell, "Cloud! Gotta go to the cloud, everybody! We need the cloud!"
And I specifically sympathize with what you said at the end. Your IT guy apparently knows he can distribute the database the way he wants, but he wants you to document every facet of the process first.
Now, that's reasonable if he's responsible for supporting it. Like I mentioned at the beginning of the video, when your IT guy leaves, he wants someone that knows what's going on.
If you do something a certain way in Access, you're building it, let's say, and you leave, he needs to know what you're doing. I get it from a company standpoint.
But there's a difference between making you document your system and treating Access like it's kryptonite, like it's radioactive and it's going to kill everybody.
I got an entire section on my website about using Access online and remotely. I got several videos, multiple different TechHelp videos on the subject. There's a Quick Queries where I've talked about it.
So that's the link. I'll put a link down below. Anybody dealing with the same situation.
So that's, yeah, there's a lot of Access haters out there. That's just because they don't know how great it is. And that's sad.
But good luck with your IT guy. If you need help feeding him to the Access demons, they're hungry.
All right, once again, make sure you're liking, subscribing, and all that good stuff.
Make sure you stop by my website, check out what's new. I'm always adding new videos and stuff. Alex has been posting some videos, and we got all kinds of cool stuff on there. So check it out.
And don't forget to check out my Captain's Log, where I write about whatever ideas happen to float into my noodle.
If I had to pick one favorite Captain's Log article from the last couple of weeks, I'd pick the one about letting AI fly the plane.
Do you want to let AI run your business completely? What happens if the AI doesn't work and you're in the cockpit by yourself and the plane's going down?
You want to be able to know how to fly that plane yourself when you need to. I think so.
I'll put a link to that one down below. It's a good article.
All right, be sure to stop by my merch store. Pick up a hat, pick up a coffee mug, pick up a mouse pad, and all that good stuff.
I got my book on Amazon. I'm working on new ones.
I just finished rebuilding my handbook generator, so I got some really cool handbooks finished for my developer series. I'm thinking about redoing my beginner ones too because this is really cool stuff.
Same-looking field, but they're just, I don't know, cleaner.
I got the source code in them all. I got these little Cool Tip from Rick boxes that I put in a lot of them. I got the quizzes all in here. So it's good stuff. I'm very, very happy with it.
If you need help, post your questions in the forums on my website.
I only take a little bit of time every week to go through the YouTube comments, and I don't usually spend a lot of time on those, but on my website, yeah, that's where I have time. Not only me, but all of the moderators and other students and everybody who helps out.
Again, Access Developer Network, if you need help. Make sure you get on my mailing list.
So there you go. That was a long one.
That was over an hour. I haven't had an hour-long Quick Queries in a while. I'm making up for not having done one in almost a month now.
But I'm going to try to get back to doing these a little more regularly. I can't promise I'm going to be every Friday. Sometimes, over the summer especially, I've just been busy. But things are starting to normalize a little bit more.
But that's your Quick Queries.
Hey, if you got questions of your own, you know what to do. Post them down below, or post a comment and just say hi. How you doing?
Say, "Yes, I'm crazy enough. I made it through an hour-long Quick Queries video, and I'm still here and I'm listening."
And I love to hear from you guys. So thank you very much.
But that's going to be your TechHelp Quick Queries video for today. I hope you learned something.
You know what I'm going to say: Live long and prosper, folks. I'll see you next time. Intro In today's Quick Queries we will discuss what to do when the person who built your Microsoft Access database leaves the company, including protecting the working system, locating files and dependencies, testing backups, documenting business processes, and deciding when internal staff or an outside developer can help. We will also cover Datasheet View, template databases, VBA troubleshooting, standard deviation, Excel automation, naming conventions, SharePoint, remote Access use, and other questions from the community. Quiz Q1. A working Access database's original developer has left the company. What should be the initial priority?
A. Protect the system and understand what it depends on
B. Immediately rebuild the application as a web app
C. Add requested new features before anything breaks
D. Delete old data to make the database smaller
Q2. If users cannot enter orders or print invoices, what should happen before planning modernization?
A. Start training all staff in VBA
B. Move all tables to Excel
C. Address the immediate failure first
D. Replace every form with a web page
Q3. In a typical multi-user Access application, what is usually stored in the front end?
A. Only the business records
B. Forms, reports, queries, buttons, and VBA code
C. Only backup copies of the database
D. Only linked Excel spreadsheets
Q4. What might be stored separately from the Access front end in a shared application?
A. The business data tables
B. The Access ribbon
C. The VBA editor
D. The Windows desktop settings
Q5. What does an ACCDE file generally prevent ordinary users from doing?
A. Viewing reports
B. Entering new records
C. Editing the application's design and VBA code
D. Printing invoices
Q6. Why is it important to identify database dependencies?
A. They make the database run faster
B. The database may rely on folders, spreadsheets, email accounts, printers, or scheduled tasks
C. They automatically create backups
D. They eliminate the need for documentation
Q7. Why should a business test restoring a backup instead of merely confirming that backup files exist?
A. Backup files always contain viruses
B. A backup may not be usable until it is successfully restored
C. Restoring a backup permanently deletes production data
D. Access cannot open backup files directly
Q8. Where should database changes, repairs, and investigations normally be performed?
A. Directly in the live production database
B. In a test copy of the database
C. In a blank Excel workbook
D. Only on an employee's personal laptop
Q9. What type of documentation can regular database users help create?
A. Business process documentation showing how daily tasks are performed
B. The complete VBA source code
C. Windows device driver documentation
D. SQL Server installation documentation
Q10. Why can an employee who understands the business be valuable when supporting an Access database?
A. They automatically know all VBA programming
B. They know how real business processes and exceptions are supposed to work
C. They can eliminate the need for backups
D. They can convert the database to a website without training
Q11. What is a realistic role for an internal employee who is learning Access?
A. Immediately rewrite every part of a complex VBA application
B. Handle simple issues and serve as an internal point person
C. Remove all security from the database
D. Replace all consultants permanently
Q12. When hiring an outside Access developer to evaluate an inherited database, what should the business request?
A. A vaguely defined estimate with no deliverables
B. A guarantee that the database will be replaced
C. A defined assessment scope, spending limit, and written findings
D. A promise to add new dashboards before reviewing the system
Q13. What should a good developer do first when taking over an existing Access application?
A. Preserve what works and identify risks
B. Delete all old forms and queries
C. Convert every table to a spreadsheet
D. Disable backups to avoid confusion
Q14. Why should a business be cautious about replacing a working Access database simply because someone recommends "the cloud"?
A. Cloud services cannot store data
B. A replacement does not automatically document business rules or workflows
C. Access cannot connect to remote data
D. Web applications do not require testing
Q15. What is one important cost of replacing an existing business application?
A. New systems do not need data migration
B. Employees do not need retraining
C. Existing business rules and special workflows must be understood and recreated
D. Reports automatically transfer without changes
Q16. What should the company own and control rather than leaving solely with one employee?
A. Credentials, backups, application files, and support information
B. Only the employee's personal email address
C. Only the desktop wallpaper
D. Only the Access navigation pane settings
Q17. When is Datasheet View often a good choice in Access?
A. When users need a simple grid for browsing, sorting, filtering, and resizing columns
B. When a highly customized data-entry interface is required
C. When VBA must be disabled
D. When records should never be displayed
Q18. Before creating a clean template copy of an Access database, what should you be careful not to delete?
A. Transactional data such as orders and customers
B. Lookup tables, settings, and required configuration data
C. Old backup files
D. The database file extension
Q19. What is a useful troubleshooting step when VBA behaves strangely and opening the VBA editor seems to temporarily fix it?
A. Check references, compile the project, and consider decompiling and recompiling
B. Rename all tables to random names
C. Delete the VBA project
D. Export all forms to PDF
Q20. Why might checking BOF and EOF be preferable to using RecordCount when testing whether a newly opened recordset is empty?
A. RecordCount is not always reliable until the recordset has been navigated
B. BOF and EOF only work with Excel files
C. RecordCount cannot be used in VBA
D. BOF and EOF automatically add records
Q21. What is the main purpose of a consistent naming convention for Access controls and objects?
A. It makes the database work without relationships
B. It helps developers identify and organize objects more easily
C. It prevents users from entering records
D. It removes the need for comments in VBA
Q22. If an organization needs remote access to an Access application, what is one option that can preserve existing Access forms and VBA?
A. Use Remote Desktop to control a Windows computer running Access
B. Convert all tables to text files
C. Send the database by email every day
D. Disable networking on all computers
Q23. What is another option for allowing users in different locations to work with the same data while keeping Access as the front end?
A. Store the shared data in SQL Server
B. Give each user a separate unrelated copy of the data
C. Print all records and mail updates to users
D. Move all forms into a Word document
Q24. If a business requires an application to run entirely in a web browser, what does that generally mean?
A. Access forms automatically become web pages
B. The business is considering a different type of application, such as a web app
C. Access reports can no longer be printed
D. No business requirements need to be reviewed
Answers: 1-A; 2-C; 3-B; 4-A; 5-C; 6-B; 7-B; 8-B; 9-A; 10-B; 11-B; 12-C; 13-A; 14-B; 15-C; 16-A; 17-A; 18-B; 19-A; 20-A; 21-B; 22-A; 23-A; 24-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary In today's Quick Queries video, I answer a question that many small businesses eventually face: what should you do when the person who built and maintained your Microsoft Access database leaves the company?
If the database is still working, there is no need to panic or immediately replace it. A functioning Access application gives you time to make sensible decisions. The first priority should be protecting the system, understanding what you have, documenting the essential business processes, and arranging ongoing support.
Many businesses end up relying on an Access database that was built over several years by someone who happened to be good with computers. It may now manage customers, orders, invoices, inventory, scheduling, reports, or other important business operations. When that person leaves, everyone may be afraid to touch the database because nobody understands what is happening behind the scenes.
The good news is that you do not necessarily need to become a programmer yourself, hire a full-time IT employee, or throw away an application that is still doing its job. You do need to make sure someone is responsible for the database going forward.
First, determine whether you have an immediate failure or a planning problem. If users cannot enter orders, print invoices, access customer information, or perform other essential tasks, then that specific problem needs to be addressed immediately. Do not begin a large modernization project while critical work is stopped.
However, if the database is currently operating normally and the concern is simply that nobody knows how to maintain it, then you have a continuity and planning issue. Your first goal should be preserving the current system and learning enough about it to support it safely. New features, dashboards, and cosmetic improvements can wait until you understand the system that is already in place.
Before anyone changes anything, identify what the database consists of. In a typical Access application, the file that users open may contain forms, reports, queries, buttons, macros, and VBA programming. This is usually called the front end.
The actual data may be stored elsewhere. In a shared Access system, the tables may be located in a separate back-end Access file on a network drive or server. The data might instead be stored in SQL Server, SharePoint, Excel files, or another external source. You need to know where the application files are located, where the data is stored, who has access to it, and how backups are handled.
You should also find out whether you have an editable development copy of the database. Some developers distribute an ACCDE file, which prevents users from changing forms, reports, and VBA code. That is often a good practice for regular users, but the business should also retain the original editable ACCDB development file. If the only editable copy was on the former developer's computer, recovering that file should be a priority.
Look for everything surrounding the database as well. An Access application may depend on linked spreadsheets, import folders, export folders, shared network paths, email accounts, printers, scheduled tasks, external databases, web services, or files located on a particular employee's computer.
For example, a database may import a spreadsheet every month from a specific folder. It may generate invoices and email them from an employee's account. A report may pull information from a file stored on an old workstation. These are dependencies, and they are often discovered only after something stops working.
Make a simple inventory of what people know. Identify regular imports and exports, reports that are run daily or monthly, email functions, linked files, network folders, printers, and other outside services. You do not need to understand every technical detail immediately, but you should know what the system depends on before those dependencies disappear.
Backups are essential. You need backups of the application and the data, wherever they are stored. More importantly, you need to test that those backups can actually be restored. An untested backup may not be useful when an emergency occurs.
Any investigation, repair, or change should be performed in a test copy whenever possible, not directly in the live production database. Production is where the real work happens, so it should be protected. Keep at least one known-good backup, record where it is stored, identify who can access it, and note what data it contains. Keep an off-site copy as well, whether that is in cloud storage or another secure location.
Your employees already possess useful documentation, even if they do not think of it that way. Ask key users to show how they perform their daily tasks. Have them demonstrate how an order is entered, how an invoice is printed, how a customer receives special pricing, how returns are processed, and what reports management needs at the end of the month.
Record these walkthroughs or write down the steps. This is business-process documentation. It explains how people use the system to run the business. It is different from technical documentation, which explains how tables, queries, forms, reports, and VBA code work together. A future developer will eventually need both kinds of information.
Do not wait until documentation is perfect before seeking assistance. Perfect documentation is rare. Start with what your staff knows, because their explanations may reveal that an unusual-looking button or report is actually essential to a critical business process.
One of the best long-term solutions is to identify someone inside the company who is interested in learning the basics of Access. This does not have to be the business owner. It could be an office manager, bookkeeper, operations employee, power Excel user, or someone who enjoys learning technical skills.
Someone who already understands the business has a significant advantage. They know what an order should do, what an invoice should contain, what happens at month-end, and which procedures are important. A consultant may know Access very well, but still need time to understand how your particular business operates.
An internal employee can begin by learning the basics of tables, queries, forms, reports, and simple database maintenance. My free Access Beginner 1 course is a good starting point for someone who wants to see whether learning Access is a good fit.
Keep expectations realistic. Learning enough Access to make minor adjustments is not the same as becoming instantly capable of maintaining a large application with extensive VBA programming. A beginner may be able to handle simple corrections, routine data tasks, and basic changes over time, while an experienced developer handles complicated repairs and programming. This combination often works very well.
If nobody internally is interested in taking responsibility, or if the database is especially complicated, then it may be time to find an experienced Access developer. Taking over an existing Access application can be more difficult than building a new database, because the developer must understand old design choices, hidden business rules, existing VBA code, external dependencies, and years of accumulated changes.
A good consultant should not immediately try to sell you a complete replacement system. The first job should be to understand what exists, preserve what works, identify risks, and help you make informed decisions.
When speaking with a possible developer, ask whether they have experience taking over existing Access applications. Ask how they communicate, how they handle service requests, what information they need from you, and how available they are for urgent problems. If your business needs same-day assistance during business hours, discuss that before you have an emergency.
A detailed technical assessment may cost more than a brief consultation, and that is not necessarily unreasonable. An unfamiliar Access database may contain years of forms, reports, queries, VBA modules, hidden business rules, and external connections. Investigating all of that can take time.
Treat the assessment as a defined purchase. Ask what the developer will examine, how the fee is determined, what spending limit applies, and what you will receive at the end of the assessment. Useful deliverables might include a short system overview, a list of important dependencies, immediate concerns, missing files or credentials, backup recommendations, and suggested next steps.
If the scope is vague, ask questions. If the price seems unreasonable, get another opinion. Compare the actual work being proposed, not just the final number on the estimate.
Be cautious about turning a maintenance concern into an unnecessary replacement project. You may hear that Access is old, everything needs to be moved to the cloud, or a completely new system is required. Those statements may be true in some cases, but they are not automatically true simply because an Access developer left the company.
Moving an application to the cloud does not automatically document your business rules, preserve your workflows, or make the new system easier to support. If someone recommends replacing the database, ask what specific problem is being solved. Is Access failing to meet your actual business requirements? Are there serious design problems? Are there limitations that cannot reasonably be addressed? Or does the developer simply prefer to work with a different technology?
The same standard applies in reverse. Continuing with Access should also be based on whether it still serves the business well. If the system is stable, meets your requirements, and can be supported, there may be no reason to replace it.
A replacement project has costs beyond new screens and modern-looking interfaces. Someone must understand the existing rules, move the data, test the new system, train employees, and reproduce all of the small but important workflows that may not have been documented. A newer interface does not automatically mean you have an equivalent business system.
The practical advice is to assess the existing Access system first, then decide whether to maintain, improve, migrate, or replace it. Do not rebuild simply because someone believes the word "cloud" solves every problem.
Use this situation to prevent the next knowledge gap. Assign an internal point person to coordinate database issues, organize documentation, and communicate with outside developers. That person does not need to write code. They simply need to know where the important materials are, who to contact, and what the business needs from the system.
Make sure the company, not an individual employee, owns access to necessary files, credentials, backups, email accounts, support contracts, cloud services, and other resources. Do not let critical business information exist only in one person's private account or memory.
As the system changes, update your documentation. Keep basic operating instructions for staff, and maintain enough technical information that a future developer can begin investigating without starting from zero.
If you are the person who currently understands the database, consider what your co-workers would need if you were unavailable tomorrow. A little preparation can make a major difference.
For a small business with no full-time IT budget and no desire to become an Access developer, the most realistic solution is often a combination of internal and external support. An internal employee can provide business knowledge and basic coordination, while an outside Access developer handles complex maintenance, repairs, programming, and technical assessments.
If you need to find an Access consultant, my Microsoft Access Developer Network includes listings for consultants, developers, tutors, and other professionals who may be able to help with your database. I also recommend reviewing general IT offboarding procedures when employees leave, including passwords, email access, cloud accounts, and company data ownership.
In the rest of this Quick Queries session, I answer a variety of questions submitted through my website, YouTube, email, and other channels.
One topic is whether to use Datasheet View or continuous forms in Microsoft Access. Datasheet View is excellent when you need a simple grid of records that users can browse, sort, filter, and resize. It requires very little design effort and can be especially useful for users who are accustomed to working in Excel.
I often prefer continuous forms because they give me more control over the design. I can choose exactly where controls appear, add buttons and combo boxes, apply conditional formatting, include images, and create a more polished interface. For data entry and custom interfaces, continuous forms are often the better choice. For basic browsing and manipulation of records in a grid, Datasheet View can be ideal. Neither approach is automatically better than the other.
Another question involves creating a clean template version of an existing database. If your database is complete and contains real data, you can make a copy, remove the transactional data, compact and repair the database, and use it as a blank template for future use or distribution.
Be careful not to delete necessary reference data, lookup tables, settings, configuration records, states, countries, cities, or other information the application depends on. The goal is to remove production data such as customers, orders, invoices, contacts, and transactions while retaining the records required for the database to operate.
One approach is to maintain three working copies. Keep a development copy with test data, a full backup copy containing real data, and a clean template copy containing only setup and reference data. You can also create routines that remove production data automatically while leaving essential configuration records intact.
Another discussion concerns the "Return without GoSub" error in Access VBA. A stray Return statement can compile successfully but fail at runtime. However, if simply opening and saving a VBA procedure makes the error disappear, the problem may be related to a stale or corrupted compiled VBA project, a missing reference, or corrupted database metadata.
The recommended troubleshooting sequence is to make a backup first, compact and repair the database, open the VBA editor, run Debug Compile, and check Tools - References for anything marked as missing. If the problem continues, decompile and recompile the database. You should also inspect startup code and form events for an actual unintended Return statement.
Another question asks whether standard deviation can be calculated in Access without exporting data to Excel. The answer is yes. You can calculate standard deviation using the underlying mathematical formulas. You need to understand whether you are calculating a population standard deviation or a sample standard deviation, because the formulas differ based on whether you divide by N or N minus 1.
You can also use Excel's functions from Access through automation if Excel is installed. Access VBA can create an Excel application object and use Excel's calculation library. However, you cannot simply turn that into an Access aggregate function and use it exactly like Sum or Count. You must collect the values, such as in an array or recordset process, and pass them to the appropriate Excel function.
If your data is stored in SQL Server, SQL Server also provides standard deviation functions, which may be a better choice than involving Excel.
I also discuss using shortcuts to work more efficiently in Access. Adding commands such as opening a form's module to custom menus or toolbars can save time. Small improvements may seem minor, but they add up during frequent development work.
A viewer commented that some short videos seem to move too quickly. YouTube Shorts have a time limit, and sometimes a longer video segment must be sped up slightly to fit within that limit. I prefer preserving natural pauses rather than cutting every bit of silence, because excessive silence removal can make speech sound choppy and unnatural. However, when a segment must be compressed too much, it may be better to create separate short videos rather than making the speech difficult to follow.
Another question asks how to display the newest records at the top of a list rather than placing new records at the bottom. Access continuous forms normally show records in the order determined by their record source. By sorting the form's underlying query or record source in descending order by an appropriate date or ID field, you can display the most recent records first.
I also explain that I sometimes use Excel while planning an Access database, but that does not mean I am building the database in Excel first. I often use Excel like graph paper to sketch fields, organize potential tables, and think through the structure.
Excel is perfectly suitable for simple lists, workout logs, meal tracking, and similar straightforward tasks. However, when you need related data, such as foods connected to meals, exercises connected to workouts, measurements, people, dates, and other interrelated information, a relational database like Access becomes the better choice.
I have been posting more video quizzes lately while working on larger projects, including Access developer lessons, SQL Server training, and updated handbooks. Quick Queries and TechHelp videos are not going away, but they may not always appear weekly while I complete these larger projects.
The video quizzes help keep fresh content available, and I have built an Access database that automates much of their production. It can use transcripts, help generate questions and answers, and capture screenshots from appropriate timestamps. I still provide the narration myself. I do not intend to replace my voice with AI narration.
I also discuss why I do not build a separate tutorial series for every possible database type, such as employee databases, school databases, medical databases, or property management databases. My goal is to teach the building blocks of Access: tables, relationships, queries, forms, reports, and VBA.
An employee database is not fundamentally different from a customer database or student database. The details may vary, but the building blocks are similar. You may have people, contact information, departments, positions, attendance, payroll, and related records. Once you understand the components, you can assemble them into the system you need.
If there is a specific feature you would like to see in a particular type of database, describe the actual problem or requirement. A request for an "employee database" is broad, but a specific need, such as tracking attendance, managing job positions, recording certifications, or handling employee status changes, may lead to a useful lesson.
Another topic involves naming conventions. Some developers prefer prefixes such as chkCustomer, while others may prefer suffixes such as CustomerCheckbox. Both approaches are valid. The most important rule is consistency.
I tend to put type indicators at the end of object names because I often want related items grouped alphabetically. For example, I may want FirstNameLabel, FirstNameText, FirstNameCheckbox, and FirstNameCombo together in a list. Other developers may prefer grouping all checkboxes, text boxes, and labels together. Use the convention that makes the most sense for your work or follow the convention used by your team.
My preference for names such as CustomerT, CustomerQ, and CustomerF comes from earlier versions of Access, where tables and queries were not always clearly separated in selection lists. Adding T, Q, and F made it easy to identify whether an object was a table, query, or form. Although Access has improved since then, I continue using this convention because it remains useful when reading VBA code and working with recordsets.
I also clarify that short videos are sometimes excerpts from longer lessons. A short clip may refer to a function or concept that was explained several minutes earlier in the full video. If a short seems to begin in the middle of a thought, check the link to the full lesson for the complete context.
Another viewer asked whether I have changed my opinion about tabbed documents versus overlapping windows in Access. I still prefer overlapping windows. Some screenshots used in recent content were generated with AI assistance, and the AI sometimes creates tabbed document interfaces even when I ask it not to. Those screenshots do not reflect a change in my own preferences.
I prefer overlapping windows because I like being able to arrange multiple forms, queries, reports, and other objects side by side. Other developers may prefer tabbed documents. As with naming conventions, use the approach that works best for you and your environment.
I also explain that controls such as checkboxes can be hidden when they are not needed. A checkbox can be used for internal settings or user preferences even when it is not visible. You can create a button or separate customization form that shows or hides a group of controls. Hidden controls and fields can still be referenced by VBA code.
A question about checking whether a recordset is empty leads to a discussion of RecordCount, BOF, and EOF. You can use RecordCount, but it may not be reliable immediately after opening a recordset. In many cases, you must move to the last record first before Access knows the full record count.
If all you need to know is whether the recordset contains records, checking BOF and EOF is often simpler. When a recordset is empty, both BOF and EOF are true. Immediately after opening a recordset, checking EOF may often be sufficient because Access normally starts on the first record if records exist. However, understanding both BOF and EOF is important when moving through records.
Another viewer asks for more coverage of Access and SharePoint. I have already created a SharePoint seminar covering the basics of connecting Access to SharePoint, setting up sites and lists, permissions, distribution, tracking, and security.
SharePoint can be useful when an organization already relies on it, especially when SQL Server is not available. However, when I have a choice, I generally prefer SQL Server as an Access back end because it is more flexible, more powerful, and easier to work with for many applications.
Microsoft has changed parts of the SharePoint web interface since the seminar was created, but the underlying Access concepts remain useful. I do not currently plan to create a new SharePoint series. Power Apps may receive more attention in the future, particularly in combination with SQL Server.
I also answer a question about hiding the Access Ribbon. The VBA technique using DoCmd.ShowToolbar to hide the Ribbon still works in current Microsoft 365 versions of Access. If it does not work in a particular database, verify that the code is actually running, that the database is trusted, and that the Ribbon name is spelled correctly. A useful troubleshooting step is to place the command behind a temporary button and test whether it works there. If it does, then the issue is likely where the code is being called from.
I appreciate experienced Access developers who help newer users in the Access Learning Zone community. Teaching is one of the best ways to improve your own understanding. When you must explain a concept clearly to someone else, you think about it differently and often gain a deeper level of proficiency yourself.
Finally, I discuss the common pressure to replace Access simply because an organization wants everything in the cloud. Remote access does not automatically require abandoning Access.
One option is Remote Desktop. Access can remain on a Windows computer while the user controls that computer remotely from a tablet, laptop, or other device. This can work very well for remote workers and travelers.
Another option is to keep Access as the front end while moving the data tables to SQL Server, including a cloud-hosted SQL Server. This allows multiple users in different locations to work with the same centralized data while preserving existing forms, reports, queries, VBA code, and other application features.
If the requirement is specifically that the application must run entirely in a web browser, then you are discussing a different kind of application. Access forms and reports do not automatically become web pages. A browser-based solution may involve Power Apps or another web development platform. That should be evaluated as a real business requirement, not as an automatic reaction to the existence of an Access database.
Documentation is reasonable when an IT department is responsible for supporting a system. The issue is not whether Access should be documented. Every important business system should be documented. The issue is whether Access is being treated fairly as a business tool or being rejected simply because someone prefers a different technology.
Access remains a capable platform for many business applications. The best decision depends on your specific needs, your users, your data, your remote-access requirements, your support resources, and the condition of your current system.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic List Managing an Access database after developer leaves
Identifying Access front ends and data back ends
Finding editable ACCDB development files
Documenting database dependencies and credentials
Testing backups and restoring a known good copy
Using test copies instead of production databases
Capturing staff business process knowledge
Training an internal Access point person
Hiring an Access developer for system assessment
Defining assessment scope and deliverables
Avoiding unnecessary Access replacement projects
Planning long-term database support and documentation
Datasheet View versus continuous forms
Creating clean Access template databases
Troubleshooting "Return without GoSub" errors
Compiling, decompiling, and repairing Access databases
Calculating standard deviation from Access data
Using Excel functions through Access automation
Sorting continuous forms with newest records first
Access control naming conventions
Using BOF, EOF, and RecordCount in recordsets
Hiding and showing form controls with VBA
Access and SharePoint as a database backend
Hiding the Access ribbon with VBA
Using Access remotely with Remote Desktop
Using SQL Server cloud back ends with Access Article If the person who built your Microsoft Access database has left the company, do not assume you need to replace the database immediately. If it is still working, you have time to make careful decisions. Your first priority should be protecting the system, understanding its basic structure, and creating a realistic support plan.
A working database is not automatically a crisis. It becomes a crisis only if it fails and nobody knows where the data is, how the application is configured, or how to restore it. The goal is not to turn the business owner into a programmer overnight. The goal is to make sure the business can continue operating if something changes or breaks.
Start by determining whether you have an immediate failure or a maintenance concern. If staff cannot enter orders, print invoices, access customer information, or complete another critical task, focus on that specific problem first. Do not begin a major redesign while daily operations are blocked. If the database works normally but nobody understands its internal design, then you have a continuity and planning issue rather than an emergency.
Before anyone changes anything, identify what you actually have. Many Access applications are split into a front end and a back end. The front end usually contains forms, reports, queries, buttons, menus, and programming logic. The back end usually contains the tables with the actual business data. In a multi-user environment, the front end may be installed on each employee's computer while the back end is stored on a shared network location.
The data may be stored in another Access file, a server database, a shared folder, or another system. It is important to find out exactly where it lives. You should also identify the location of every copy of the application, including any version used for development or testing.
One important question is whether you have an editable copy of the database. Sometimes a developer distributes a compiled version of an Access application so users cannot modify forms, reports, or programming. That is often appropriate for daily use, but the business should also possess the original editable development copy. If the only editable copy was on the former employee's personal computer or account, recovering it should be a priority.
You should also look for everything connected to the database. Access applications often rely on outside files and services. They may import spreadsheets from a specific folder, export reports to a shared drive, send email through a particular account, retrieve files from a network location, or connect to printers and scheduled tasks. A system can appear to work perfectly until the first month-end process fails because the folder, account, or password belonged to someone who left.
Create a simple inventory of known dependencies. Ask staff whether they regularly import files, export data, email invoices, run special reports, use shared folders, or follow unusual procedures at certain times of the month. You do not need to understand every technical detail right away. You simply need to know what the database expects to find in order to function.
Backups are essential. You need backups of both the application and the data, especially if they are stored separately. More importantly, you need to test whether the backups can actually be restored. A backup that has never been tested is only a hope, not a recovery plan.
Keep at least one known-good copy of the database in a protected location. Record where the backup is stored, who has access to it, and what date the data represents. Maintain an offsite copy as well, such as a secure cloud storage location or another physical location. If the business suffers hardware failure, theft, fire, ransomware, or accidental deletion, an offsite backup may be the difference between a minor inconvenience and a major loss.
Never experiment with the live production database. Production is the copy that staff use to perform real work. Any investigation, repair, upgrade, or change should be performed on a test copy first. If someone needs to troubleshoot a problem, they should make a backup, copy the database, and work on the copy. This reduces the chance that a well-intentioned repair attempt creates a larger problem.
Your employees already possess a great deal of useful documentation, even if nothing has been written down. Ask key users to demonstrate how they perform their jobs. Have someone show how an order is entered, how an invoice is produced, how returns are handled, how a special price is applied, or how month-end reports are created. Record these walkthroughs or write down the steps.
This type of documentation is business-process documentation. It explains what people do with the system and why they do it. That is different from technical documentation, which explains how tables, forms, queries, reports, and programming logic fit together. A future developer will eventually need both, but business-process documentation is often easier to collect immediately because your staff already knows how they use the system.
Do not wait until documentation is perfect before seeking help. Perfect documentation is rare. Even a rough list of procedures, reports, important forms, and recurring tasks can help a new developer understand which parts of the database are critical to the business.
Consider whether someone inside the company could become the database point person. This does not necessarily mean they must become a full-time programmer. An office manager, bookkeeper, operations employee, or advanced spreadsheet user may be able to learn enough Access to handle simple issues, communicate with outside support, and understand how the system fits the business.
Someone who already understands the business has a major advantage. They know what an order should look like, which reports management needs, and what happens when something unusual occurs. They may not be able to immediately maintain a complex application with advanced programming, but they can often learn the basics of tables, queries, forms, reports, and simple troubleshooting.
A practical approach is often to combine internal knowledge with outside technical support. An employee can serve as the contact person, keep documentation organized, test basic procedures, and explain business needs. An experienced Access consultant can handle programming changes, complex repairs, design reviews, and database maintenance.
If you need outside help, look for someone with experience taking over existing Access applications. Inheriting an unfamiliar database can be more difficult than building a new one because the application may contain years of accumulated changes, hidden business rules, old reports, unusual dependencies, and programming logic that is not obvious at first glance.
A good consultant should not immediately insist on replacing the system. Their first job should be to understand what exists, preserve what works, identify risks, and help you make informed decisions. Ask prospective consultants whether they have taken over existing Access systems before, how they communicate with clients, what information they need from you, and how they handle urgent support requests.
Be realistic about availability. An independent consultant may be excellent, but not necessarily available immediately when something fails. If your business requires same-day support during working hours, discuss that requirement before an emergency occurs. A support arrangement should clearly define response expectations, hourly rates, spending limits, and who has authority to approve changes.
A technical assessment may cost money, but it can be a sensible investment. Do not treat an assessment as a vague expense. Define what the consultant will examine and what you expect to receive. Useful deliverables might include a basic system overview, the location of the application and data files, a list of external dependencies, notes about missing credentials or files, immediate risks, and recommended next steps.
If an estimate seems high, ask what work is included. A detailed review of an unfamiliar application can take significant time. The important question is not simply whether the number is large. The important question is whether the scope is clear and whether you receive useful information in return. If necessary, get another opinion and compare the actual work proposed.
Be cautious about turning a maintenance concern into an unnecessary replacement project. You may hear that Access is outdated, that everything must move to the cloud, or that a web application is automatically better. Those statements may or may not be relevant to your business.
A replacement should solve a real business problem. Perhaps the current system cannot support remote users, cannot meet security requirements, cannot handle the volume of data, or has serious design limitations. Those can be legitimate reasons to consider a different platform. However, replacing an application simply because the original developer left is not always justified.
A new system must still capture the existing business rules, move the data accurately, reproduce essential reports, test every critical process, and train employees. Many businesses discover too late that the old system handled special situations that nobody remembered until those features disappeared. A newer-looking interface is not automatically an equivalent business solution.
The same standard applies to keeping Access. Continue using it because it meets your actual needs, not merely because it is familiar. If it remains reliable, secure enough for your environment, maintainable, and appropriate for the work it performs, maintaining it can be a sensible decision.
Use this situation to prevent the next knowledge gap. Assign someone internally to coordinate database questions and maintain documentation. Make sure the company, not an individual employee, owns access to all important files, email accounts, passwords, backups, cloud services, and support contracts. Do not allow critical business systems to depend entirely on one person's memory or personal account.
As changes are made, update the documentation. Keep simple instructions for regular users and enough technical information for future support personnel to begin troubleshooting without starting from zero. The goal is not to create a massive manual that nobody reads. The goal is to make sure important knowledge is not trapped inside one person's head.
If the Access database still works, you have options. Protect the system, locate the data, secure the backups, identify dependencies, document how staff use it, and decide who will be responsible for coordinating future support. An internal point person combined with an experienced outside developer is often the most practical solution for a small business.
The departure of the original developer does not mean you have lost the database. It means the business needs to take ownership of the system that has been supporting it all along. Primary Topics inherited Access database support, application and data inventory, backups and restore testing, editable ACCDB versus ACCDE files, external dependencies and credentials, internal Access point person, hiring Access consultants, avoiding unnecessary replace Secondary Topics Datasheet View versus continuous forms, template database copies, VBA Return without GoSub troubleshooting, standard deviation calculations, Excel automation, recordset BOF EOF checks, naming conventions, SharePoint limitations, hiding the Access ribbon, |
