Quick Queries #105
By Richard Rost
4 hours ago
Should You Replace Access With a Web Application? In today's Quick Queries we will discuss when an Access application should be extended rather than rebuilt as a web app, including split databases, SQL Server backends, remote desktop, and focused web or mobile access. We will also cover the VBA Date and Now difference, refreshing a sandbox database from production data, troubleshooting corruption after power loss, and safer ways to delete records from an open Access form. Arthur from Houston, Texas (a Gold Member) asks: We've used our Access database for about 10 years, and it still works well for everyone in the office. We may be hiring a couple of remote employees who need some of the same data. I keep seeing ads saying we should replace Access with a modern web app. Do we really have to rebuild everything? LinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, replace Access with web app, Access SQL Server migration, remote Access users, Access frontend with SQL Server, Access over internet, Access mobile access, split database, ACCDB 2GB limit, Date vs Now VBA, Access database corruption, Recordset.Delete continuous form
More InformationTranscript Have you got a Microsoft Access database that's been working great for years? But now someone's telling you that you have to scrap it and rebuild the whole thing as a web app? Maybe you've got a couple of remote employees coming onboard, and suddenly everybody thinks it's time to push the big, shiny, modernize-everything button.
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 whether or not you really need to replace Microsoft Access with a web application. We'll look at the important difference between your Access application and the database file where the data is actually stored, what remote access really means, and why adding a web or mobile option doesn't necessarily mean throwing away 10 years' worth of forms, reports, VBA code, and business rules that already work just fine.
There are definitely valid situations where a web application is the right choice, but "Access is old" isn't one of them. So before you spend a bunch of money rebuilding something that isn't broken, you need to know what problem you're actually trying to solve.
In addition to that, we've got some answers to questions from YouTube comments, my forums, emails, and lots of other stuff.
All right, let's get to it.
Today's top question comes from Arthur in Houston, Texas, one of my Gold members. Arthur says, "We've used our Access database for about 10 years, and it still works well for everyone in the office. We may be hiring a couple of remote employees who need some of the same data. I keep seeing ads saying we should replace Access with a modern web app. Do we really have to rebuild everything?"
Well, no, Arthur. In a nutshell, you definitely don't have to rebuild everything. I get this all the time.
In many cases, you can keep the Access application that's already working and just extend it to handle your remote users. But let's take a look at why people recommend replacing Access and exactly what your options really are.
You don't need to throw away a mature Access application just because somebody says web applications are more modern. If your database works, your employees are happy with it, and it meets the needs of the business, then it's already doing its job.
A business application is a tool. If your favorite hammer is still driving nails straight, you don't replace it with a shiny new hammer just because the box says, "New hammer."
Remote workers may create a legitimate new requirement, but that doesn't automatically mean rebuilding everything on the web. It means we need to ask the most important question in this whole discussion: What business problem are we actually trying to solve?
Maybe you need remote access. Maybe you need stronger data security. Maybe you need a couple of phone-friendly screens. Those are all different problems, and they all have different solutions.
Replacement should be the result of business requirements. It should never be the starting assumption just because someone wants to sell you a replacement. Most advertisements, consulting firms, and stuff that I've seen out there offering Access replacements are just doing it for that reason. They're trying to sell you something you may not need.
Here's where a lot of these discussions go off the rails. People say "Microsoft Access" when they really mean the ACCDB database file. Those are related, but they're not the same thing.
Microsoft Access is a database system, yes, but it's also a rapid application development environment. In plain English, it lets you build a complete business application fairly quickly.
Your Access application may have tables that store data, and it's got queries, forms, reports, and stuff like that. But when someone says, "Replace Access," your first question should be, "Which part?"
Are we replacing where the data is stored? Are we replacing the forms that employees use to enter information on the screen all day? Are we adding a browser interface for a few people?
Those are three very different projects with three very different price tags.
Thinking of Access as just a database file is like looking at a restaurant and saying that it's just a refrigerator. Sure, the refrigerator matters, but it's not the kitchen, the staff, the menu, the cash register, or all that stuff.
I like to separate it into three separate decisions.
First, where should the data live? For a small setup, it could live in an ACCDB file on a local area network, or it could live in a server database such as Microsoft SQL Server. That SQL Server data could be on premises or in the cloud.
Second, what should office employees use? The people who are actually in your office may continue using their familiar Access forms, reports, queries, VBA, and all that stuff without changing a thing.
Third, do some users need a remote interface? Is that remote interface on a Windows machine or some other type of device, like a mobile device, tablet, iPhone, or whatever? If so, you can build specifically for them.
These decisions don't have to happen together. Moving the tables to SQL Server does not require throwing away the Access front end. Building a small web portal does not require rebuilding 100 Access forms. Keeping your Access database for your office users doesn't prevent you from using those other technologies, too.
This is the architectural idea that gets missed in a lot of marketing. They present one giant red button labeled "Replace Access." Real life usually isn't like that.
Real life is more like remodeling a house. Maybe you do need a bigger garage, but that doesn't mean you have to bulldoze the living room.
One of the common myths you'll hear, and I see it all the time on different websites that are trying to sell you other solutions, is that Access can't scale past two gigabytes. That's written by people who don't know Access.
Yes, an ACCDB file has a practical file size limit of two gigabytes. That is true. But you can chain multiple files together, and you can connect the front end to multiple backend files.
That's a limitation of using an ACCDB file as your data-storage backend. That's not a limit on the Access front end itself.
Access can connect to SQL Server and other databases using linked tables and ODBC connections. SQL Server then handles the actual data storage, security, backup, and larger data volumes. Access can still provide your forms, reports, queries, VBA, and all that stuff that you're familiar with.
So if someone tells you that your application has to be rebuilt because Access has a two-gigabyte limit, ask them, "Do you mean the front end, or do you mean the ACCDB backend file?"
In many cases, the answer is simple. Move the tables to SQL Server, relink the Access front end, test everything carefully, and let users continue working in the Access application they already have and understand.
That alone can be a significant upgrade without a complete rewrite.
Another myth is that Access is only for one person or a small team of people. Nope.
A properly designed multi-user Access application normally uses what's called a split database. Each user gets their own local copy of the front end, which contains the forms, reports, queries, VBA code, and the rest of the application. The backend contains the shared tables.
For a small office, that backend can simply be an ACCDB file on your local network. As your needs grow, you can move those same tables to SQL Server without throwing away the Access front end.
There are limits. An ACCDB backend isn't designed for huge numbers of users, advanced server-level security, or direct use over the public internet. But that doesn't mean Access is single-user. It just means that, as your needs grow, you need to choose the right backend architecture.
No, you don't have to email database files around.
Now let's get to the situation raised in the original question. You've got a successful Access application, and you're thinking about adding a couple of remote workers. That doesn't automatically mean it's time to rebuild everything.
Suppose you've got 20 people in the office already using Access successfully, and then you hire two remote salespeople. If those remote users need the full application, one option is Remote Desktop or a virtual desktop environment.
I personally like Chrome Remote Desktop. I use it all the time when I travel. This way, they can securely run the existing Access application remotely, almost as if they were sitting at a computer in your office.
Another option is to move your backend tables to SQL Server and give the remote workers their own Access front end that connects to that SQL Server over the internet. Their front end can be tailored to just the features they need. You don't have to upgrade the whole thing.
Maybe they just need to look up customers and leads, update some contact information, and review orders. You can build them their own front end that does just what they need.
Access runs on their Windows computers while connecting securely to the SQL Server backend over the internet. Meanwhile, your existing office staff can continue using the same Access application they already have.
Everyone works with the same central data, but the front end can be different depending on what each group needs.
The important thing is, don't try to open an ACCDB backend directly across the internet, especially using something like Dropbox or Google Drive. That's not what it was designed for.
For remote access users, use something like remote desktop software or move the data to a proper server-based backend like SQL Server.
Mobile access is another legitimate reason to add technology. Access is a Windows desktop application. It doesn't run natively on an iPhone, an Android phone, a tablet, or any of that stuff.
That's not an insult. That's just the nature of the product.
But before rebuilding everything, ask what the mobile users actually need. A warehouse employee with a phone might not need your entire 10-year desktop application. They might need three things: scan a barcode, change a quantity, and press Save.
That's a small, focused mobile workflow. Build a mobile screen or a web page that does those things well. Big buttons, minimal typing, maybe a camera barcode scanner. Perfect.
Don't try to force a gigantic desktop application onto a phone screen just because the phone exists. Don't rebuild the other 140 forms that the warehouse employee will never touch.
This is the theme you'll hear me repeat: solve the actual requirement. A mobile need is not automatically an "everything needs to be mobile" requirement. Just because some people need remote access doesn't mean you have to migrate the entire Access database to a web application.
This is one that I've seen happen many times in my 30-year consulting career. A 10- or 15-year-old Access application is rarely just a simple collection of tables and forms. It's usually a museum of hard-earned business knowledge.
There may be some VBA code in there that generates monthly emails, some validation rules that prevent expensive mistakes, some complicated queries that someone refined five years ago. You've got specialized reports for accounting, imports from other systems, exports for this vendor, and all those little improvements that happened because someone said, "Hey, can we add one checkbox here?"
Those are always the little things that annoyed me. You finish a database for a client, and they're like, "Yeah, but can we add this little thing?" Okay, sure, fine.
Then there's the strange but important workflows. Maybe accounting needs a special procedure on the third Thursday of the month. Nobody remembers why it was added, but everyone knows the business falls apart if it doesn't happen.
That's not a joke. These details are often the application.
Conversion and migration tools can be useful. Some are impressive. But if a vendor says they can automatically convert 80% of your application, you've got to ask a very important question: Which 80%?
That remaining 20% might contain 80% of the complexity. Remember the old 80-20 rule.
Tables are relatively easy. Rebuilding some forms to look the same is easy. Recreating real-world behavior, testing it, training users, and discovering missing edge cases, that's where the project gets expensive.
When you search for Access alternatives, you'll find all kinds of products, and many of them are perfectly good tools. But they often do completely different jobs. Some replace the database backend, some replace the front end, and some are development platforms or admin tools.
They aren't necessarily replacements for Microsoft Access as a whole.
Access does a lot. Remember, Access gives you tables, queries, forms, reports, programming, and automation, all in one package. Replacing it might mean replacing different pieces with different tools.
Don't just ask, "What's the best Access alternative?" Ask, "What part of my Access system actually needs to change, and why?"
Remember, "Access is old" isn't a reason. Windows is old, too. We're still using Windows.
To be fair, there are absolutely situations where replacing your Access front end does make sense. If you have hundreds or thousands of geographically distributed users and most of them need browser access, then a web application might be the right direction.
If you're building a customer-facing application where outside customers need accounts, self-service screens, and access from everywhere, then a web platform is probably a better fit.
If your application is truly mobile-first, meaning the phone or tablet is the primary device for most users, then build for mobile first.
Sometimes an organization has standardized on a particular platform. Maybe they have development staff, security policies, hosting systems, or internal standards built around .NET, Power Platform, or another environment. That can be a valid business reason, too.
But the point isn't to say that Access must stay forever. The point is that "Access is old" is not a business requirement. "The web is newer" is not a business requirement.
Use the right tool for the job. Make sure that you're identifying the actual job before buying a whole new toolbox.
For many businesses, including my own, the best path is evolution instead of revolution.
Access is excellent for rapidly building a business application. Then, when multiple users need it, split the database so each person has a local front end and everyone shares the backend.
Then, when the data grows and the user count rises, or security and administration requirements become more serious, move the backend tables to SQL Server.
Keep Access as the front end for the employees who still benefit from it. They don't need to relearn everything overnight, and you don't need to recreate every working feature.
Then, when you add some remote workers, give them a lightweight version of your Access database or build a web screen for what they need to do.
If customers need to view their orders, build a customer portal. If managers need charts, add a dashboard. If warehouse staff need barcode scanning, add that.
Once your data is in a proper server database like SQL Server, multiple front ends can coexist peacefully. Access can use the data, web applications can use it, ASP.NET can use it, PHP can use it, mobile apps can use it, dashboards can use it, and all kinds of other systems can use it, too.
This is exactly why I'm building my SQL Server for Access Users class: to help Access developers move their data to SQL Server while continuing to use Access front ends that they already have and know.
Let's wrap this one up. If your Access application works, don't replace it just because somebody says you need something more modern.
Start with your business requirements, choose the right architecture, and change only what needs to change. Like Lone Star says, bring only what you need to survive.
Sometimes that means upgrading the backend. Sometimes it means adding web or mobile access. Yes, sometimes replacing Access really is the right choice.
Make that decision because the business needs it, not because someone is trying to sell you something or someone tells you that Access is old and obsolete.
Use the right tool for the job. Architecture first, marketing second.
If you want to learn more about a lot of the topics that I talked about, including splitting your database, connecting Access, setting up an online database, Access on a phone, and all this stuff, you'll find links to all these videos down below.
Also, be sure to check out my SQL Server for Access Users Level One. It's completely free. You'll find that on my website and on my YouTube channel. Level Two is coming out very soon.
Also, check out my Access SQL Server Online Seminar. This seminar is all about getting your Access data into SQL Server online so your Access users can connect to it from anywhere in the world.
All right, let's see what else we've got in the mailbag today.
We've got a question from Kevin. Kevin, I have to apologize because I wrote this down for Quick Queries as from Kevin, but apparently I didn't think that Future Richard would need to know which Kevin.
I've got several Kevins who are regulars, including Kevin Robertson, one of my moderators, who I love dearly. So, whichever Kevin you are, good question and lousy note-taking on my part.
Here's the question: "I've noticed that in VBA, Date and Now both return the current date or time, but Now has parentheses and Date doesn't. If I type Date with parentheses, the VBA editor often removes the parentheses. So why is Date treated differently from Now? Is Date not actually a function?"
Now that I'm thinking about it, it's probably not either of the Kevins that I'm thinking of because they probably would know this one already. But for everybody else, and for the Kevin who asked this, this is one of those little VBA quirks that you can stare at for 30 years and never stop to ask why.
I've often wondered this myself. I looked into it some years ago.
Now behaves like a conventional function with no arguments. Date is a little weirder because it's carrying around some extra baggage from the old BASIC days.
Date doesn't just return the current system date. It can also be used as a statement to actually set the computer's date. Time works the same way.
In the Immediate Window, you can open it up and type Date to get today's date, Time to get the current time, and Now to get both.
Here's the fun historical part. VBA also supports saying something like Date = 1/1/90, and that's not assigning a value to a variable named Date. That's actually telling the operating system, "Congratulations, it's January 1, 1990 again."
Believe it or not, that used to be useful. Before virtual machines and all the testing tools we have today, developers would sometimes change the computer's clock to test things like month-end processing, year-end reports, date rollovers, expiration dates, and all those date-sensitive features.
Modern Windows isn't particularly thrilled about applications trying to casually change the system clock. You could demonstrate this by saying Date = 1/1/90, and you can't do it. Permission denied.
You're just trying to tell VBA to set the system date, but Windows is telling Access, "No." Windows security steps up like a bouncer and says, "Ah, can't do it."
That's why the VBA editor leaves Now with the parentheses alone, but if you type in Date with parentheses, it changes it right back to Date.
There's some very old BASIC history hiding behind that tiny little set of parentheses.
I don't recommend that you try running Access as administrator just to prove that you can change the system date. Don't do it.
Next up, over in the forums on my website, we've got a question from Stefan. He's got two copies of his database. One is his live production database that he uses every day, and the other is basically a sandbox where he can experiment without worrying about breaking anything.
I know all about breaking stuff in your database, believe me.
The problem is the production database keeps getting new data, so eventually his sandbox gets out of date. He wants to know the best way to periodically bring that sandbox back up to date without keeping the two databases constantly synchronized.
Alex jumped in with links to my Sync Remote Database video and my Access Web Sync seminar, which are definitely worth checking out if you need actual synchronization. John also mentioned keeping a log of changes.
But I think Alex asked the most important question here: Are we just adding records, or can records also be edited and deleted? Because that makes a huge difference.
For Stefan's particular situation, though, I wouldn't overcomplicate it. If this second database really is just a sandbox, my first choice would be to split the database if it isn't already split.
Keep your production data in one backend, and keep your experimental forms, reports, queries, and VBA in a separate sandbox front end.
Then, whenever you want fresh data for testing, make a backup of your sandbox backend and replace it with a fresh copy of the production backend. Boom, you're current again.
If you've got test records in your sandbox that you need to preserve, that's where things get a little more interesting.
You could append only new production records based on the primary key. But once you start saying, "I need new records, plus changed records, plus deletions, and then both databases might have been edited," now congratulations, you're wandering into the synchronization swamp.
Don't follow the lights.
Sam and Thomas brought up exactly that problem further down the thread. Once both databases can change independently, now you've got to think about conflicting edits, related records, deletes, primary keys, timestamping, change tracking, and deciding which version wins.
I could easily do two hours on replication IDs and all that stuff.
Kevin Yip, who I'm not sure is probably not the Kevin Yip from the previous question because I think Kevin Yip would probably already know that, pointed out that SQL Server has replication and other tools for just this kind of thing.
As I mentioned somewhere else in the thread, SQL Server also gives you change tracking, row versioning, change data capture, and all kinds of other stuff.
But true two-way replication can become a rabbit hole very quickly. You really just have to ask yourself what you really need.
My advice is: Don't build synchronization when a simple refresh will do.
For Stefan's situation, I'd keep it simple and periodically just replace the sandbox data with a fresh copy of the production data.
If you really do need synchronization, check out these two videos. I'll put links down below. I'll also put a link down below to this entire thread on my website if you want to read the whole thing.
Next up, we've got a good cautionary tale from Donald. He was working in Access when his power went out for about 90 seconds. His laptop stayed running on battery, so you'd think, no big deal.
But when everything came back up, Access started throwing weird file-not-found errors whenever he tried to edit his VBA code.
Donald did exactly what I would recommend: start working through my Access Troubleshooter. When he got to the decompile step, though, he ran into another problem. Windows Terminal had put him in PowerShell instead of the regular Command Prompt, and the command didn't work the same way there.
As a token pointed out, in PowerShell, you need the ampersand before the executable path. That's a handy little tip to remember.
Matt also mentioned that after a power outage, he sometimes has to completely power cycle his Dell docking station, not just restart the laptop.
That's a good point because your laptop battery might keep the computer itself alive, but if you've got docks, external drives, monitors, routers, and all that other hardware connected to your system, they all need power, too.
That's why I usually recommend getting a good UPS system for everything that's connected to your computer. Plus, all those other devices, if they're not behind a power surge protector, can send a surge through the USB port, the networking cable, and all that stuff.
This is a pretty good thread. There's lots of good stuff in this one, too.
As a token actually spent some time trying to reproduce a similar problem. He eventually found that one particular form had become corrupted. By creating a new database and importing the objects one at a time, which is in my checklist, he was able to isolate the bad form while saving everything else.
That's a great troubleshooting technique when Compact and Repair and decompile don't solve the problem.
That's basically what happened to Donald. He eventually narrowed the damage down to three forms that happened to be open when the power went out.
He removed everything else and moved everything into a fresh database. Then he rebuilt those damaged forms and their VBA.
Fortunately, he made a backup copy. Backup, backup, backup, like I always say.
His backup was only about 10 minutes old, so he didn't lose much work.
But the big lesson here is that Access databases can get corrupted. Power failures can happen. If Access is in the middle of writing something, then you've got issues.
It's not just Accessing. Any file can get corrupted. I don't care if it's an Excel spreadsheet or a text file. If power goes out while it's in the middle of writing, it doesn't matter. You're going to get a corrupted file.
Access is just one big, complicated file that has a lot of stuff in it, so you've got to be careful.
Get yourself a UPS. They're not expensive. Get good surge protection. Most importantly, make frequent backups.
I keep multiple rotating backups myself because storage is cheap and my time is not. A 10-minute-old backup is an inconvenience. A six-month-old backup is the beginning of a drinking problem.
If you want to read the full thread, I'll put a link to it down below. Of course, don't forget to check out my Troubleshooter. Lots of stuff on there.
You can go through and check out if you've got problems with it. Again, I'll put links to everything down below.
By the way, in the thread, I gave the two UPSs that I recommend. I've always liked CyberPower. I've tried all the other ones. These are my favorites.
YouTube just started doing something where we can tag products on Amazon in our videos. I have never done it yet. I haven't tried it. Today is going to be my first time.
I'm going to tag both of these. We'll see how it works.
Full disclosure: I only recommend products that I use myself, and I've got several of both of these. If you're interested in a good UPS, check it out. Click on my link, and I think I'll make like three cents. Thanks, I appreciate it.
All right, heading over to YouTube now.
First up, we've got a little tip from Xavier. Most of you probably know about the Format Painter, but here's a little trick you might not know.
If you click it once, you can copy formatting to one control. If you double-click it, it stays active and you can keep applying that format to as many controls as you want.
When you're done, press Escape. That's a nice little time-saver.
Thanks for sharing, Xavier. I covered this in one of my beginner lessons somewhere, but it's a very handy tip. It works in Word and Excel and all those, too.
Next up, we've got a comment from Cyberman, who has commented before. He's saying that he's sorry to say, but he has to be honest: He doesn't find the video quizzes that I've been doing very interesting to watch, and he stopped watching my videos since I keep posting these almost every day of the week.
I completely understand. The video quizzes aren't going to be everybody's cup of tea, and that's perfectly fine. I do appreciate you taking the time to tell me, especially since you were so nice about it. No hard feelings whatsoever.
I started making these because I've had a lot of people over the years ask me for more quizzes and other ways to test what they've learned.
Personally, I like these kinds of things myself. If I'm scrolling through YouTube Shorts or TikTok or whatever, and somebody gives me one of those five geography questions or five questions about astronomy, I'll stop and play along.
I love those little quizzes for the things that I follow. Ten questions about Rush or Star Trek? Yeah, I'm going to play along.
That's why I started making them in the first place. So far, the response has actually been really good.
I'm seeing lots of people in the comments posting their scores and talking about it, including people whom I've never seen in the comments before.
The engagement is up, views have been good, and it seems like people are enjoying them. I'm getting a lot of positive feedback.
While they're definitely not for everybody, there does seem to be an audience for them.
There's also an educational purpose behind them. Remember, each quiz is based on one of my actual classes.
The one that you're looking at here that you commented on, for example, is Access Developer Level One, Lesson Five.
If you've already taken that class, it's a quick way to see how much you remember. If you haven't taken it, maybe you get three out of five wrong and think, "Hey, I didn't know that. Maybe there's something in this class that I could learn from."
But for me, on my end of it, there's also another reason that I'm making them, and that's simply time.
A video quiz takes me maybe 15 to 20 minutes to produce, start to finish. A good 10- to 15-minute-long TechHelp video can easily take me three or four hours by the time I research it, build the database, record it, edit it, upload everything, and get it published.
For the last five or six years now, I've spoiled you guys. I've made over a thousand TechHelp videos. For a long time, I was putting out something substantial almost every day.
But the problem is that's basically all I've had time to do. I've got a lot of other stuff I want to work on, too.
I've got an Access Developer course I want to keep moving forward. I've got my SQL Server for Access Users series that I'm still working on. I've got other courses and projects that people have been waiting for.
There's only so many hours in the day. Apparently, Microsoft still hasn't added that 30-hour-a-day option to Windows yet. I'm still waiting for that.
That's why I've been changing up the balance of what I've been putting on my YouTube channel.
My goal moving forward is to produce one good, solid, substantial TechHelp video every week with an extended cut for the members. I'll try to do a Quick Queries video like this toward the end of the week, usually Friday or Saturday.
During the rest of the week, I can put out a bunch of these shorter quizzes while I'm working on other stuff like my developer courses and my SQL Server courses.
That lets me keep fresh content coming to my YouTube channel and my website without spending every waking hour making TechHelp videos. Frankly, it keeps me sane.
If the quizzes aren't your thing, I completely understand. Skip them. I won't be offended.
The regular TechHelp videos and Quick Queries aren't going anywhere. I'm just trying to find a schedule that lets me keep making those while also having time to build other stuff that people have been asking me for.
Again, I appreciate the feedback. No hard feelings at all.
When someone tells me they don't like something I'm doing, if you're respectful about it like this, I'm always happy to hear it.
It's just like when you guys teach me new things. I do a video, and you're like, "Well, you can also do it this way." I'm like, "Wow, yeah, that's pretty cool. Thanks. I appreciate that."
I love learning from you guys. I want to hear your feedback. I want to move the channel in ways that you guys want to see. I want to produce stuff that you guys want to watch.
By the same token, there's only so many hours in the day, and I've got bills to pay. I've got regular courses that I have to produce to get things moving on my end, too.
YouTube doesn't pay the bills, which is why I've got to start working on more courses.
Thanks again, Cyberman. I appreciate the feedback.
Everybody else, I'm curious. What do you think? Are you sick of the video quizzes, or do you like them? Post down below. Let me know.
I love them myself. I like doing them. I have fun with them, and they're not very time-consuming at all. I love putting them together every day. I'll sit down and make five of them in like an hour, and I've got stuff going out all week.
Since we're talking about viewer feedback, here's another good one.
I've heard from several people about this topic over the last six months or so, not just this one. But EJ basically said that some of the AI-generated women I've used in the thumbnails in the past have looked a little more dressed for a night out than for a professional office.
They also said that while sex appeal might attract attention, it could also turn some viewers away. They suggested keeping the images a little more professional and diverse.
Fair criticism.
For a while, I was experimenting with using an attractive woman as the subject for some of my AI-generated thumbnails. I tried to keep the model consistent across multiple videos, like a spokesperson would be.
I never specifically told the AI, "Hey, give me plunging necklines and bare arms." I'd usually just say something like, "Attractive professional woman."
Sometimes, I actually did have to scale the AI back a little bit. But I did get a few comments about it, including from my wife, which is generally a pretty good indication that maybe it's time to adjust the warp core.
If you've noticed my thumbnails over the last couple of months, I have toned that way down.
If I'm using a person, I've been trying to use just a regular person. Here's last week's, and the week before. I personally rather like this one. This was a good one.
As you can see, I've changed how I do the title slides based on viewer feedback.
I'm still going to use attractive people in the images, men or women, but I'm trying to keep everybody looking more appropriate for the subject matter instead of looking like they're headed to happy hour immediately after the database meeting is over.
There actually was a practical reason I experimented with it in the first place. Anecdotally, a couple of those thumbnails seemed to get more views. I thought, "Well, maybe there's something to this."
Like you said, sex sells. Attractive people have been used in advertising since advertising was invented.
Eventually, I did some actual YouTube thumbnail split testing. I put the attractive woman up against a more ordinary office-worker version to see what happens.
Statistically, there wasn't much difference.
If it's actually not bringing in a lot more viewers, and some viewers find it off-putting, that's a pretty easy decision. I toned it down, and that's what I've done.
EJ also mentioned the Star Trek uniforms and suggested that maybe those should only be used occasionally. We're going to have to agree to disagree on that one.
Star Trek stays. Penguins stay. Those are not negotiable terms of the treaty.
Not everybody watching is going to be a Trekkie, obviously, but that's a big part of my personality.
I'm not going to make every thumbnail look like it came out of a Star Trek production office, but occasionally, I'm going to put some Star Trek stuff in there. There are going to be penguins in there. That's just who I am.
If you're offended by penguins or Star Trek, well, you can hit the unsubscribe button right now.
Point taken on dialing back the sex appeal. That's already been done, and I think the newer images are better for it.
Now, if I happen to put a penguin in a Starfleet uniform teaching Microsoft Access, wait a minute, that's a good idea.
Behold, I give you Professor Penguin, dean of Starfleet Database Academy. His mission: to boldly normalize where no table has been normalized before.
See what you made me do.
Back to our regularly scheduled programming.
The next one is from Zawilius. I think I'm pronouncing that right. Zawilius has apparently decided that it's his turn to quiz the teacher. Trusted teacher. Now I've got a reputation to maintain.
Basically, he's got a filtered continuous form, and he's deleting records one at a time using Recordset.Delete.
When he gets down to the last record, he's finding that BOF and EOF aren't behaving the way he expects. Even ChatGPT apparently threw up a tantrum and called it an Access problem.
He's asking Trusted Teacher for the final verdict.
I just played around with this myself, and I can reproduce some weird behavior.
After deleting a record, I basically got BOF False and EOF False. Then Access immediately told me, "No current record," when I tried another delete.
There is definitely some funky cursor behavior going on here.
But as your trusted teacher, my answer is: Why are you doing it this way? Do you have a specific reason why you're doing it this way?
Personally, I don't like using Recordset.Delete against a recordset of an open form. If I'm working with a separate recordset in the background and looping through records, that's different.
But with a bound form that's sitting there on the screen, I'd rather let the form be the form and do the delete separately.
Here's TechHelp Free Template. Here's a continuous form. It doesn't matter if you're filtered or whatever. You could filter by New York or whatever.
You've got records here. Add a Delete button. In the Delete button, very simply, here's what I would do.
Execute an SQL statement in the background:
Delete from CustomerT where the CustomerID equals CustomerID.
You could even put a check up here. Make sure that you've got a CustomerID.
If Nz(CustomerID, 0) = 0, then Exit Sub.
You don't have a CustomerID. If someone's down here, for example, and they try hitting Delete, you can give an error message if you want.
But if they're in here, that's going to background delete it, and then Me.Recordset.Requery leaves the cursor kind of where it was.
It moved it here. It didn't requery the whole list, so you didn't lose your filter and all that stuff, too.
That's why I like using Me.Recordset.Requery.
That's the way that I would do it with an open form. Don't use Recordset.Delete.
Even a RecordsetClone delete can be troublesome.
That's how you do it. The recordset disappears, Access rebuilds the recordset, your existing filter stays in place, and now if you want to check whether there are any records remaining, you're checking a freshly requeried recordset instead of one whose current record you just deleted out from underneath it.
Your observation is valid. You will get some funky behavior from Recordset.Delete if it's against an open form.
But rather than fighting with it and fighting with BOF and EOF and record pointer states and all that stuff, just don't use Recordset.Delete in this way in the first place.
Sometimes the best way to fix a weird problem is to stop doing the weird thing.
If you've got a form open, delete this way. If it's in the background, you can use Recordset.Delete.
If you're looping through a recordset and, as you're coming across records, you're deleting them, and then you're just moving one way through the recordset, don't go backwards.
There's lots to it. I spend so much time in my full Developer course going over recordsets. There's lots to it.
Want to learn more about recordsets? Start here and then see how deep the rabbit hole really goes.
I literally spend probably 20 hours on recordsets in my Developer course.
While you're here, if you like my videos and you want to stick around, hit Like and Subscribe. It helps more people find my videos, it helps the channel grow, and I appreciate it. Thanks.
Make sure you stop by my website and check out what's new. There are a lot of video quizzes. I get it. I understand.
There's lots more stuff coming out, too. I'm working on Access Developer 61 right now. I just haven't put 60 up on there yet. I just have to put the bookends on it, the intro and the review lessons.
But 60 is done. 61 is coming out soon. SQL Server Level Two is coming out soon.
That's why I've been doing all these video quizzes, so I've got time to work on all this other stuff.
I'm working on my Access Web Publisher. I've got lots of projects. Lots of irons in the fire.
Check out my Captain's Log, too. I haven't had a lot of time lately to write for the Captain's Log. This is one of those things that's a passion project. I write when I get some spare time, and spare time lately has been in pretty short supply.
But I did write a new article this morning called "John Henry Versus Tom Sawyer." It's inspired by an email that I got from a former student.
It's basically about our relationship with AI and technology.
John Henry represents the old idea of competing against the machine and trying to beat it through sheer skill and hard work. My argument is that today, it's probably the wrong approach.
Learn the machine. Use it as a tool. But keep your judgment, creativity, curiosity, and humanity in the driver's seat.
Be a modern-day Tom Sawyer, not someone who's fighting the machine. Don't rage against the machine.
I worked in some Star Trek, too. Check it out. If that sounds like your kind of thing, stop by the Captain's Log. Give it a read. I think you'll enjoy it.
Of course, while you're there, pick up a T-shirt, pick up a hat, pick up a plushie. I'm still looking for plushie penguins. I can't find anybody that does custom penguins.
But Merch Store has got some cool stuff in it. I'm going to try to tag that UPS. Let me know if you find it.
Pick up a copy of my book. It's on Amazon. Stop by the forums. Access Developer Network if you need someone to help you with your database.
Make sure you get on my mailing list. All that good stuff.
There you go. Now you know why you probably don't need to throw out your Microsoft Access database just because somebody tells you that you need a shiny new web app.
You learned why Date doesn't behave quite like Now, why sometimes the best database synchronization strategy is just not to synchronize it at all, why deleting records from underneath an open form's recordset can leave Access wandering around in the field wondering where the hell it is, and, most importantly, why you should make sure you get good backups and get a UPS so that a power outage doesn't wreck your work.
That's going to do it for today's Quick Queries. I hope you learned something and had a few laughs, and maybe picked up a trick or two you can use in your own databases.
Thanks for watching, and thanks to everybody who posted questions and comments.
Post your comments down below for next week's Quick Queries, whether you've got actual questions, feedback, complaints, comments, or constructive criticism. I like to hear all of it.
Until next time, live long and prosper, my friends. Have a good weekend. Intro In today's Quick Queries we will discuss when an Access application should be extended rather than rebuilt as a web app, including split databases, SQL Server backends, remote desktop, and focused web or mobile access. We will also cover the VBA Date and Now difference, refreshing a sandbox database from production data, troubleshooting corruption after power loss, and safer ways to delete records from an open Access form. Quiz Q1. What is the best first question to ask before replacing a working Access application with a web app?
A. What business problem are we trying to solve?
B. Which web framework is newest?
C. How many colors should the new interface use?
D. Which consultant charges the lowest rate?
Q2. Which statement best describes Microsoft Access in a business environment?
A. It is only a file format for storing tables
B. It is a rapid application development environment that can include tables, queries, forms, reports, VBA, and automation
C. It can only be used by one person at a time
D. It is designed only for websites
Q3. Which three decisions should generally be considered separately when planning an Access modernization project?
A. Data location, office user interface, and remote user interface
B. Font choice, logo design, and screen colors
C. Browser choice, printer choice, and email provider
D. Computer brand, monitor size, and keyboard layout
Q4. What does the 2 GB size limit primarily apply to?
A. An ACCDB file used as a data backend
B. All Access applications, regardless of backend
C. Every SQL Server database connected to Access
D. Every Access report
Q5. What is a common way to support larger data volumes while keeping existing Access forms and VBA?
A. Move the tables to SQL Server and link the Access front end to them
B. Convert every form into an Excel worksheet
C. Email a copy of the database to every employee
D. Store the ACCDB file in a shared cloud-sync folder
Q6. In a properly designed multi-user Access application, what should each user normally have?
A. Their own local copy of the front end
B. Their own separate copy of the shared backend tables
C. Their own copy of the SQL Server database
D. Their own email account for sending database records
Q7. What typically belongs in an Access front end?
A. Forms, reports, queries, VBA code, and application logic
B. Only the shared production tables
C. Only backup files
D. Only user passwords
Q8. Why should an ACCDB backend generally not be opened directly across the public internet?
A. It was not designed for reliable direct internet file access
B. Access cannot display tables remotely
C. ACCDB files can only store one record at a time
D. Access does not support any type of remote user
Q9. Which is a reasonable option for a remote employee who needs the full existing Access application?
A. Use Remote Desktop or a virtual desktop environment
B. Put the backend file in Dropbox and open it directly
C. Send the employee a new backend copy each morning
D. Convert all forms into PDF files
Q10. If remote salespeople only need to look up customers, update contact information, and review orders, what is often a sensible approach?
A. Give them a smaller Access front end tailored to those tasks
B. Rebuild every existing form as a web page immediately
C. Give them direct write access to the production backend file
D. Require them to use the office computer in person
Q11. What is the recommended approach for a warehouse employee who needs to use a phone for a limited workflow?
A. Build a focused mobile interface for tasks such as barcode scanning and quantity updates
B. Rebuild the entire desktop application on one phone screen
C. Install the Access desktop application on the phone
D. Give the employee access to all administrative forms
Q12. Why can rebuilding a mature Access application be more difficult than it first appears?
A. Existing applications often contain years of business rules, validation, reports, imports, exports, and special workflows
B. Access tables cannot be copied to another database
C. Web applications cannot use databases
D. VBA code always converts perfectly to web code
Q13. Why should you be cautious when a vendor claims it can automatically convert most of an Access application?
A. The unconverted portion may contain the most complex and important business logic
B. Automated conversion always deletes every table
C. Access forms cannot be recreated in other systems
D. SQL Server cannot store converted data
Q14. Which situation is a strong reason to consider replacing an Access front end with a web application?
A. Hundreds or thousands of geographically distributed users need browser access
B. A small office is happy with its current Access forms
C. One employee wants a newer-looking icon
D. The application has been in use for several years
Q15. What is an example of an evolutionary approach to modernizing an Access system?
A. Move data to SQL Server, keep Access for office staff, and add web or mobile tools only where needed
B. Delete the existing system and start over before identifying requirements
C. Convert all reports to screenshots
D. Keep all data in separate spreadsheets for each department
Q16. What can multiple front ends do once data is stored in a server database such as SQL Server?
A. Access, web apps, mobile apps, dashboards, and other systems can use the same central data
B. Only Access can connect to the data
C. Only one user can access the data at a time
D. The data can no longer be used by reports
Q17. In VBA, why does Date behave differently from Now?
A. Date can also be used as a statement to set the system date
B. Date is a variable created by Access users
C. Now can only return the current date, not the time
D. Date requires an argument in all situations
Q18. What is the practical lesson about trying to use Date = 1/1/90 in modern Windows?
A. Windows permissions may prevent an application from changing the system clock
B. It changes only the date inside the current Access database
C. It automatically creates a backup first
D. It changes the date in all SQL Server tables
Q19. If a sandbox database only needs occasional fresh production data, what is often simpler than building synchronization?
A. Replace the sandbox backend with a fresh copy of the production backend
B. Build two-way replication for every table
C. Manually retype production records into the sandbox
D. Keep both databases permanently disconnected
Q20. Why can two-way synchronization become complicated?
A. You must handle conflicting edits, changes, deletions, related records, and which version wins
B. Access does not allow records to be edited
C. Primary keys are not needed in synchronized databases
D. Backups are no longer possible
Q21. When deleting a record from a bound continuous form, what approach was recommended instead of using Recordset.Delete on the open form recordset?
A. Run a separate DELETE SQL statement and then requery the form recordset
B. Delete the entire table and recreate it
C. Close Access before each delete
D. Convert the form into an unbound report
Q22. Why can Recordset.Delete against an open form's recordset cause problems?
A. The form's current-record pointer can be left in an unusual state after deletion
B. It permanently disables filters
C. It always deletes all records in the table
D. It prevents SQL statements from running
Q23. What is a good troubleshooting technique when an Access database may be corrupted and standard repair steps do not solve the issue?
A. Create a new database and import objects one at a time to isolate the damaged object
B. Delete all VBA modules first
C. Rename every table
D. Turn off all backups
Q24. What is the most important protection against data loss from power failures or file corruption?
A. Maintain frequent, rotating backups
B. Avoid using forms
C. Use only continuous forms
D. Never compact a database
Q25. Why is a UPS useful for an Access workstation and related equipment?
A. It can help prevent sudden power loss and protect connected equipment
B. It converts an Access database into SQL Server
C. It automatically repairs corrupted forms
D. It eliminates the need for backups
Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A; 21-A; 22-A; 23-A; 24-A; 25-A
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 address a common concern for long-time Microsoft Access users: whether a successful Access application must be thrown away and rebuilt as a web application simply because remote employees are being added or because someone says that web apps are more modern.
The short answer is no. A mature Access application does not need to be replaced just because it has been in service for many years, someone wants remote access, or a consultant is promoting a web-based replacement. If the application works, employees understand it, and it meets the business's needs, then it is already doing its job.
A business application is a tool. You do not replace a tool that works simply because a newer-looking version is available. The important question is not, "Should we replace Access?" The important question is, "What business problem are we trying to solve?"
Remote workers may create a new requirement. Perhaps the company needs secure access from outside the office. Perhaps it needs a few mobile-friendly screens. Perhaps the business needs better data security, greater storage capacity, or easier administration. Those are all legitimate requirements, but they are different problems and may require different solutions.
One of the biggest sources of confusion is that people often use the phrase "Microsoft Access" when they really mean an ACCDB file. Microsoft Access is not just a database file. Access is also a rapid application development environment that lets you build complete business applications relatively quickly.
An Access application can include tables, queries, forms, reports, VBA code, automation, validation rules, imports, exports, and years of business logic. When someone says, "Replace Access," I recommend asking exactly what they want to replace. Are they talking about the data storage? Are they talking about the forms employees use every day? Are they asking for a browser interface for a small group of remote workers? These are separate projects with very different costs and risks.
Thinking of Access as nothing more than a database file is like thinking a restaurant is just a refrigerator. The refrigerator is important, but it is not the kitchen, the staff, the menu, the cash register, or the procedures that keep the restaurant operating.
I like to separate this issue into three decisions.
First, where should the data live? In a small office, the shared data can live in an ACCDB backend file on a local area network. As needs grow, the data can be moved to a server database such as Microsoft SQL Server. SQL Server can be hosted locally in the office or online in the cloud.
Second, what should office employees use? Employees who work in the office may be perfectly well served by the Access forms, reports, queries, VBA code, and workflows they already know. There is often no reason to force them to learn an entirely new application just because the backend data has been upgraded.
Third, do some users need a remote or mobile interface? If so, what type of device will they use? A remote worker with a Windows computer has different needs from a warehouse employee using a phone or tablet. Those users may need a smaller, purpose-built interface rather than a complete replacement of the existing Access application.
These decisions do not have to happen all at once. Moving data from an ACCDB backend to SQL Server does not require replacing the Access front end. Adding a small web portal for customers does not require rebuilding every Access form. Keeping Access for office employees does not prevent the organization from also using web applications, dashboards, mobile apps, or other technologies where they make sense.
This is the architectural idea that gets lost in a lot of marketing. Some vendors present a big red button labeled "Replace Access." In reality, most businesses need an evolution, not a revolution. It is more like remodeling a house. If you need a larger garage, you do not have to bulldoze the living room.
Another common myth is that Access cannot scale past two gigabytes. There is some truth behind this, but it is often stated incorrectly. An ACCDB file has a practical size limit of two gigabytes. However, that is a limitation of using an ACCDB file as the backend data store. It is not a limit on the Access front end itself.
An Access application can link to SQL Server tables through ODBC. SQL Server can handle larger data volumes, backups, security, administration, and server-level processing. The Access front end can still provide the forms, reports, queries, and VBA automation that employees already use.
So if someone says your Access application must be rebuilt because Access has a two-gigabyte limit, ask whether they mean the Access front end or the ACCDB backend file. In many cases, the practical solution is to move the tables to SQL Server, relink the Access front end, test carefully, and let employees continue using the application they already understand.
That can be a substantial improvement without the expense and risk of a full rewrite.
Another myth is that Access is suitable only for one user or a very small group. That is not correct. A properly designed multi-user Access application normally uses a split database architecture.
Each user has a local copy of the front end, containing forms, reports, queries, VBA code, and other application objects. The shared backend contains the tables. For a small office, that backend can be an ACCDB file stored on a local network. As the business grows, the same tables can be moved to SQL Server without discarding the Access front end.
There are certainly limits. An ACCDB backend is not intended for huge numbers of simultaneous users, advanced server-level security, or direct use over the public internet. But that does not mean Access is a single-user product. It means you need the right architecture for your situation.
You should not email database files back and forth, and you should not attempt to open a shared ACCDB backend directly across the internet. Services such as Dropbox, Google Drive, OneDrive, and similar cloud file-syncing systems are not proper solutions for hosting a live multi-user Access backend. Doing so can lead to performance problems, conflicts, and database corruption.
If you have an office full of Access users and add only a few remote employees, rebuilding the entire system as a web application may be unnecessary. One option is to use Remote Desktop or a virtual desktop environment. I personally use Chrome Remote Desktop when traveling. In that situation, the remote worker is effectively using a computer in the office from another location. The Access application runs in the appropriate environment, and the remote worker sees and uses it as if they were sitting at their office desk.
Another option is to move the backend tables to SQL Server and give remote workers a separate Access front end designed specifically for the features they need. For example, remote salespeople may only need to look up customers and leads, update contact information, and review orders. They may not need access to every screen, report, administrative tool, or accounting feature used by office staff.
The office staff can continue using the full Access application. The remote employees can use a smaller Access application tailored to their responsibilities. Both groups work with the same central SQL Server data, but each group has an interface appropriate to its job.
Mobile access is another valid reason to add technology. Microsoft Access is a Windows desktop application. It does not run natively on iPhones, Android phones, tablets, or similar mobile devices. That is not a flaw. It is simply the type of application Access is designed to be.
Before rebuilding an entire business system for mobile use, determine what mobile users actually need. A warehouse employee using a phone may only need to scan a barcode, change an inventory quantity, and save the update. That is a focused workflow. A simple mobile screen or web page with large buttons, minimal typing, and camera-based barcode scanning may be ideal.
There is no reason to force a massive desktop application onto a small phone screen simply because phones exist. If mobile users need three screens, build three good mobile screens. Do not rebuild 140 forms that those users will never open.
This is the central idea throughout this discussion: solve the actual business requirement. A need for mobile access does not automatically mean every feature must become mobile. A need for a couple of remote workers does not automatically mean every Access form must be rebuilt as a web application.
A mature Access application is often much more than a group of tables and forms. After ten or fifteen years, it usually contains a great deal of hard-earned business knowledge. It may have VBA code that generates monthly emails, validation rules that prevent expensive mistakes, complicated queries refined over years, specialized accounting reports, imports from external systems, exports for vendors, and countless small improvements added because someone needed one additional checkbox, one special report, or one unusual exception.
Those small details are often the real application.
There may also be strange but important procedures that nobody remembers creating. Perhaps accounting has a special month-end workflow on the third Thursday of each month. Nobody may remember why it exists, but everyone knows the business has problems if it is skipped. Those unusual rules and edge cases are easy to overlook during a rebuild, but they are frequently the most important parts of the system.
Conversion and migration tools can be useful. Some are genuinely impressive. However, when a vendor claims that they can automatically convert 80 percent of an Access application, the important question is which 80 percent. The remaining 20 percent may contain most of the complexity.
Creating tables in a new platform is usually straightforward. Rebuilding forms that look similar may also be manageable. Recreating years of business behavior, validation, reports, automation, exceptions, testing, user training, and undocumented workflows is where the cost rises rapidly.
When looking at Access alternatives, remember that many products perform different roles. Some products replace the data backend. Others provide web front ends. Some are development platforms, reporting tools, dashboards, or database administration products. They may all be useful, but they are not necessarily replacements for Microsoft Access as a complete system.
Rather than asking, "What is the best Access alternative?" ask, "What part of my Access system needs to change, and why?"
"Access is old" is not a business requirement. Windows is old too, and businesses still use it every day. "The web is newer" is not a business requirement either. Technology should be selected because it solves a defined problem.
There are absolutely situations where replacing the Access front end makes sense. If you have hundreds or thousands of geographically distributed users who need browser access, a web application may be the right direction. If you are creating a customer-facing application where outside customers need accounts, self-service features, and access from anywhere, then a web platform is probably a better fit.
If the application is truly mobile-first, where phones and tablets are the primary devices used by most employees, then it makes sense to design specifically for mobile use.
An organization may also have valid technical reasons for standardizing on a particular platform. It may already have developers, security policies, hosting systems, deployment processes, and internal standards built around .NET, Power Platform, or another environment. That can be a legitimate business reason for moving toward another platform.
The point is not that Access must remain forever. The point is that you should make the decision based on actual business needs, not marketing pressure.
For many businesses, including my own, the best approach is evolution instead of replacement.
Access is excellent for rapidly developing a business application. When multiple users need it, split the database so that every user gets a local front end while all users share the backend data. When data volumes increase, user counts rise, or security and administration requirements become more serious, move the backend tables to SQL Server.
Continue using Access as the front end for employees who benefit from it. They do not need to relearn their jobs overnight, and you do not need to recreate every feature that already works.
When remote workers are added, give them a lightweight version of the Access application or build a small web interface for the specific work they need to perform. If customers need to see their orders, build a customer portal. If managers need charts and key performance indicators, add a dashboard. If warehouse staff need barcode scanning, create a focused mobile tool.
Once the data is stored in a proper server database such as SQL Server, many front ends can coexist. Access can use the data. Web applications can use the data. ASP.NET applications, PHP applications, mobile applications, dashboards, reporting tools, and other systems can use the same central database as well.
That is why I am building my SQL Server for Access Users class. The goal is to help Access developers move their data to SQL Server while continuing to use Access front ends they already understand.
The main lesson is simple. If your Access application works, do not replace it simply because someone says you need something newer or shinier. Start with business requirements, choose the appropriate architecture, and change only what truly needs to change.
Sometimes that means upgrading the backend. Sometimes it means adding web or mobile access. Sometimes a complete replacement is the correct decision. Make that decision because the business needs it, not because someone is trying to sell you a replacement or because they claim Access is obsolete.
Use the right tool for the job. Architecture first, marketing second.
I also answer several questions from viewers and forum members in this Quick Queries session.
One question concerns the difference between the VBA Date function and the Now function. Both can return current date and time information, but they behave differently in the VBA editor. Now appears to behave like a normal function with no arguments, while Date may have parentheses removed automatically by the editor.
The reason is historical. Date does return the current system date, but it can also be used as a statement to set the computer's date. Time works similarly. In older BASIC environments, developers could assign a date directly through the Date statement to change the system date for testing purposes.
That was useful when developers needed to test month-end processing, year-end reports, expiration dates, leap years, date rollovers, and other date-sensitive features. Before virtual machines and modern testing tools, changing the computer clock was a common way to simulate future or past dates.
Modern Windows security does not generally allow applications to casually change the system clock. If you try to change the date through VBA without the appropriate permissions, Windows will reject the action. This historical dual purpose is why Date is treated differently from Now by the VBA editor.
I do not recommend running Access as administrator simply to experiment with changing the system date. There are better and safer ways to test date-sensitive applications.
Another question involves maintaining a sandbox copy of a production database. The situation is common: someone has a live production database used every day and a separate copy where they can test changes without risking the live system. The challenge is that production data continues to change, so the sandbox eventually becomes outdated.
The best solution depends heavily on whether records are only being added or whether records can also be edited and deleted. That distinction matters because synchronization becomes much more complicated when both databases can change independently.
If the second database is truly just a sandbox, I recommend keeping things simple. Split the database if it is not already split. Keep the production data in one backend file and keep the experimental forms, reports, queries, and VBA code in a separate sandbox front end.
Whenever you need fresh testing data, make a backup of the sandbox backend and replace it with a current copy of the production backend. This gives you updated production data without trying to maintain ongoing synchronization between the two databases.
If there are test records in the sandbox that must be preserved, the problem becomes more complicated. You might append only new production records based on primary keys. However, once you need new records, edited records, deleted records, independent changes in both databases, conflict handling, related-record handling, timestamp tracking, and rules for deciding which change wins, you are dealing with a true synchronization problem.
That can become complicated very quickly. SQL Server offers features such as replication, change tracking, row versioning, and change data capture. Those features can help in certain situations, but two-way synchronization and replication require careful planning.
My general advice is not to build synchronization when a simple refresh will solve the actual problem. For a sandbox database, periodically replacing the test data with a current production copy is usually much simpler and safer.
Another question comes from a situation involving a power outage. A laptop continued running on battery power, but after the power returned, Access began displaying unusual file-not-found errors while VBA code was being edited.
The recommended first step in a situation like this is to work through a proper Access troubleshooting process. That may include Compact and Repair, decompile, creating a fresh database, importing objects, and isolating corrupted forms or reports.
One practical detail is that Windows Terminal may open PowerShell rather than the traditional Command Prompt. Commands that work in Command Prompt do not always work exactly the same way in PowerShell. For example, PowerShell may require an ampersand before an executable path in certain situations.
Power failures can affect more than the laptop itself. A laptop battery may keep the computer running, but docking stations, external drives, monitors, routers, network equipment, and USB devices may lose power. In some cases, a docking station may need to be completely power-cycled rather than merely restarting the laptop.
This is why I recommend using a good UPS system for your computer equipment. A UPS provides battery backup during brief outages and can protect against sudden shutdowns. Good surge protection is also important because power problems can affect devices through USB connections, network cables, and other attached equipment.
Access databases can become corrupted if power fails while Access is writing data or application information to disk. This is not unique to Access. Any file can be corrupted if the power goes out while it is being written. Access files are particularly complex because they can contain tables, forms, reports, queries, code, and other objects in one file.
In the situation discussed, the damage was eventually narrowed down to forms that were open when the outage happened. By creating a fresh database and importing objects one at a time, it was possible to identify the damaged objects, preserve the undamaged parts of the application, and rebuild the affected forms and VBA code.
The most important lesson is to make frequent backups. I recommend keeping multiple rotating backups because storage is inexpensive and your time is valuable. A backup from ten minutes ago is an inconvenience. A backup from six months ago can become a disaster.
I also share a small productivity tip involving Format Painter. Most people know that clicking Format Painter once allows them to copy formatting to one control. If you double-click Format Painter, it remains active so you can apply the same formatting to multiple controls. When you are finished, pressing Escape turns it off. This works in Access and in other Microsoft Office applications such as Word and Excel.
I also respond to feedback about video quizzes. Some viewers enjoy the quizzes and use them to test what they remember from my classes. Other viewers prefer longer instructional videos and may not be interested in seeing quiz content frequently.
I understand both perspectives. The quizzes are not intended to replace regular TechHelp videos or Quick Queries. They provide an additional way for students to test their knowledge, discover topics they may want to learn, and interact with course material.
They also take far less time to produce than a full TechHelp lesson. A short quiz can often be created in fifteen or twenty minutes, while a detailed ten- or fifteen-minute tutorial may take several hours of research, database design, recording, editing, uploading, and publishing.
Producing shorter quiz content during the week allows me to continue releasing regular TechHelp videos, Quick Queries sessions, developer lessons, SQL Server content, and other larger projects. There are only so many hours in a day, and I need time to create the more extensive courses and projects students have requested.
My goal is to continue publishing one substantial TechHelp video each week, along with a Quick Queries video toward the end of the week when possible. Shorter quizzes can fill in the remaining days while I work on longer courses and development projects.
If quizzes are not your thing, you can simply skip them. The longer TechHelp and Quick Queries content is not going away.
I also discuss feedback concerning AI-generated thumbnail images. Some viewers felt that certain images looked more appropriate for a night out than for a professional educational video. That is fair criticism.
For a period of time, I experimented with using an attractive, consistent AI-generated spokesperson in thumbnails. I never intended to create inappropriate images, but AI image generation can sometimes exaggerate clothing styles or appearances beyond what is suitable for the subject matter.
After receiving feedback, including feedback from my wife, I adjusted the thumbnails. When I use people in thumbnails now, I aim for more ordinary, professional-looking people who fit the educational topic. I may still use attractive people, men or women, but I want the images to look appropriate for an Access lesson rather than an after-work social event.
I also tested thumbnail variations to see whether more glamorous images actually improved performance. The results did not show a meaningful difference. If a style does not substantially improve views and some viewers find it distracting or off-putting, then it is an easy decision to make the thumbnails more professional.
That said, Star Trek references and penguins are still part of the channel. They are part of my personality and will appear occasionally. I am not going to make every thumbnail look like it came from a Star Trek production office, but there will still be Star Trek references, penguins, and the occasional bit of science fiction humor.
Another technical question involves deleting records from a filtered continuous form using Recordset.Delete. When the final record is deleted, the form's recordset can display confusing BOF and EOF behavior. Access may indicate that BOF and EOF are both False and then immediately report that there is no current record when another delete is attempted.
There can be unusual cursor behavior when deleting a record directly from the recordset behind an open bound form. The issue is not necessarily that the observation is wrong. The behavior can indeed be confusing. My recommendation is to avoid using Recordset.Delete against the recordset of an open form unless there is a specific reason to do so.
When working with a bound form on screen, I prefer to let the form remain responsible for displaying records while performing the deletion separately with an SQL delete operation based on the current record's primary key. Before deleting, you should confirm that the primary key contains a valid value. After the delete, requery the form's recordset so Access rebuilds the displayed data while preserving the form's existing filter where possible.
This approach avoids trying to manage BOF, EOF, record pointers, and current-record states after deleting the current record from underneath the form. Once the recordset has been requeried, you are working with a fresh recordset rather than one whose current record was just removed.
Using Recordset.Delete is still appropriate in some cases. For example, if you are working with a separate background recordset and looping through records programmatically, it can be useful. However, when deleting records from an open bound form, an SQL delete followed by a requery is generally a cleaner and more predictable approach.
Sometimes the best way to solve an unusual problem is to stop doing the unusual thing that caused it.
I also mention several projects and resources available through Access Learning Zone, including the Access Developer course, SQL Server for Access Users Level One, future SQL Server lessons, the Access SQL Server Online Seminar, troubleshooting resources, database synchronization resources, the Access Web Publisher project, the Access Developer Network, the forums, the mailing list, and the Captain's Log.
In the Captain's Log, I discuss the relationship between people and AI technology. I use the contrast between John Henry and Tom Sawyer as a way to explain my view. John Henry represents the idea of competing directly against a machine through sheer skill and effort. In the modern world, I think that is often the wrong approach.
Instead, learn the machine and use it as a tool. Let technology help you work more effectively, but keep your judgment, creativity, curiosity, and humanity in control. Technology should support your work, not replace your thinking.
The key lessons from this Quick Queries session are that you do not need to replace a working Access application merely because someone says web apps are modern, Date and Now behave differently in VBA because of historical BASIC behavior, a simple sandbox refresh may be better than complex database synchronization, deleting records from an open form's recordset can cause confusing behavior, and reliable backups, surge protection, and a UPS can save you from losing important work during a power outage.
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 When to keep Access instead of rebuilding for web
Access front ends versus database back ends
Moving Access tables to SQL Server
Access architecture for remote users
Remote Desktop for Access users
Avoiding Access back ends over the internet
Adding focused web and mobile workflows
Risks of rebuilding mature Access applications
Date versus Now behavior in VBA
Using Date to set the system date
Refreshing a sandbox database from production
Access corruption after power failures
Access backups, UPS systems, and surge protection
Using Format Painter on multiple controls
Deleting records from an open continuous form Article A long-running Microsoft Access application does not need to be rebuilt as a web application simply because remote employees are joining the company or because someone says that web applications are more modern. The first step should always be to identify the actual business requirement. Remote access, mobile access, larger data storage, stronger security, and customer self-service are different needs, and each may call for a different solution.
It is important to separate Microsoft Access, the application users work with, from the database file where the data is stored. An Access application may include tables, queries, forms, reports, macros, VBA code, validation rules, imports, exports, automated emails, and many years of accumulated business logic. Replacing all of that is not the same thing as moving the data to a different database server or adding a web page for a small group of users.
Think of the system as three separate decisions. First, decide where the data should live. For a small office, the shared tables may be stored in an Access database file on the local network. For larger data volumes, stronger security, better backups, or remote connectivity, the tables may be moved to a server database such as SQL Server. Second, decide what office employees should use. In many cases, they can continue using the Access forms, reports, queries, and automation they already know. Third, decide whether certain users need a remote, browser-based, or mobile interface. Those users may need only a small subset of the overall application.
One common misconception is that an Access application cannot scale because an Access database file has a size limit. The file size limitation applies to the Access database file used as the data-storage backend. It is not necessarily a limitation of the Access front end. Access can connect to SQL Server and other database platforms through linked tables and ODBC connections. In that arrangement, SQL Server stores and manages the data, while Access continues to provide forms, reports, queries, and VBA-based business logic.
For a multi-user Access application, the normal design is a split database. Each user has a local copy of the front end containing forms, reports, queries, and code. The shared backend contains the tables. Keeping each front end local helps prevent performance and corruption problems that can occur when multiple people open the same front-end file across a network.
As requirements grow, the backend can be moved from an Access database file to SQL Server without necessarily replacing the Access front end. This can improve scalability, backup procedures, administration, data security, and support for remote users. Users may not even notice much change beyond the fact that the data is now stored on a more capable server.
Remote employees do not automatically require a web rewrite. If remote workers need the full Access application and use Windows computers, remote desktop or virtual desktop access may be a practical solution. They can run the existing application on a computer inside the office or in a hosted environment, while controlling it remotely from their own device. This avoids exposing database files directly over the internet.
Another option is to place the data in SQL Server and give remote users a smaller Access front end tailored to their specific duties. For example, remote sales staff may only need to view customer records, update contact details, review orders, and enter leads. They may not need the accounting reports, administrative screens, and specialized tools used by office personnel. A separate front end can give them only the features they need while still using the same central data.
What should not be done is opening an Access backend file directly across the public internet or attempting to share it through services such as Dropbox, OneDrive, or Google Drive while multiple people work in it. Access database files are not designed for that type of use. It can lead to slow performance, locking problems, conflicts, and file corruption. Use remote desktop access or a proper database server for remote connectivity.
Mobile access is another valid reason to add technology, but it should be approached carefully. Access is a Windows desktop application and does not run natively on phones or tablets. However, that does not mean every form and report must be rebuilt for mobile devices. Identify what mobile users actually need to do. A warehouse employee may need to scan a barcode, adjust inventory, and save the change. A manager may need to approve an item or view a dashboard. A customer may need to check an order status.
These are focused workflows that can often be handled by a small web page or mobile-friendly application connected to the same central database. Trying to duplicate a large desktop application on a phone usually creates a poor user experience and greatly increases the cost of the project.
A mature Access application often contains much more value than people realize. The visible forms and tables may be relatively easy to recreate, but the real complexity is frequently hidden in validation rules, reports, automated tasks, special queries, import and export processes, error handling, and unusual procedures that were added over many years. A small feature that seems unimportant may be essential to a monthly accounting process or a vendor integration.
Automated conversion tools can help with parts of a migration, especially tables and basic forms. However, the difficult work is understanding the existing business rules, reproducing the behavior accurately, testing unusual cases, training users, and correcting the missing details that are only discovered after the new system goes live. The final portion of a conversion project often contains most of the cost and complexity.
There are situations where replacing the Access front end with a web application makes good business sense. A browser-based system may be appropriate when hundreds or thousands of users are geographically distributed, when most users need access from anywhere, or when the application is customer-facing. If customers need accounts, self-service features, online forms, or access from many types of devices, a web platform may be the better choice. A mobile-first business process may also justify building specifically for phones and tablets. Organizational standards, security policies, existing development staff, and hosting requirements can also be legitimate reasons to choose a different platform.
The key is to base the decision on actual requirements, not on the age of a product or a marketing claim. "Access is old" is not a business requirement. "We need secure customer access through a browser" is a business requirement. "Our remote staff need to update customer records from home" is a business requirement. "We need to support much larger data volumes and centralized server administration" is a business requirement.
For many organizations, the best approach is gradual evolution. Keep Access where it works well. Split the database so users have local front ends. Move the shared tables to SQL Server when data size, user count, security, or remote access needs justify it. Add a focused web portal for customers, managers, or mobile workers when they need one. Once the data is stored in a proper server database, multiple front ends can use the same information. Access, web applications, dashboards, mobile tools, and other systems can coexist without forcing a complete rewrite.
When maintaining a separate sandbox copy of a database for experimentation, do not assume that full synchronization is necessary. If the sandbox exists only for testing changes to forms, reports, queries, and code, a practical approach is to keep the production data separate from the experimental front end. When fresh test data is needed, make a backup of the sandbox data and replace it with a recent copy of the production data.
Synchronization becomes much more complicated when both copies can be edited independently. At that point, the system must account for new records, changed records, deleted records, conflicting edits, related records, primary keys, timestamps, and rules for deciding which version of a record should win. Before building synchronization, determine whether a simple refresh of the test data will meet the need. Often, it will.
When deleting records from an open bound form, it is usually safer to let the form remain responsible for its own displayed recordset. Rather than deleting the current record through the form's recordset and then trying to manage record pointers, beginning-of-file and end-of-file states, use a separate background delete operation based on the record's primary key. Then refresh the form's recordset so the form rebuilds its view while preserving the current filter where possible. This avoids the confusing behavior that can occur when a form's active record is deleted out from underneath it.
Access applications, like any files being actively written to disk, can be damaged by power failures, network interruptions, hardware problems, or unexpected shutdowns. A laptop battery may keep the laptop running, but connected docks, external drives, network equipment, and monitors can still lose power or behave unpredictably. A good uninterruptible power supply and surge protection can reduce the risk of damage to connected equipment.
Frequent backups are even more important. Keep multiple rotating backups rather than relying on a single old copy. A backup from a few minutes ago may cost a little rework. A backup from months ago may mean losing important changes, data, or development work. If a database becomes corrupted, common recovery steps include compacting and repairing it, decompiling the application, creating a new blank database, and importing objects gradually to identify the damaged form, report, or module. Having a recent backup makes this process far less stressful.
The overall lesson is simple: do not rebuild a working Access application unless there is a clear business reason to do so. Improve the architecture where needed. Move the data to a server database if appropriate. Add remote, web, or mobile tools for specific users and workflows. Preserve the valuable business logic that already works. Choose the right tool for the problem instead of assuming that every problem requires a complete replacement. Primary Topics
Access front end versus data backend, Access to SQL Server migration, remote Access users, web and mobile interfaces, split database architecture, Access scaling myths, sandbox database refresh, VBA Date versus Now, Access corruption recovery, deleting r Secondary Topics
Remote Desktop, Chrome Remote Desktop, ODBC linked tables, ACCDB size limits, SQL Server change tracking and replication, UPS and backup practices, PowerShell command execution, Format Painter, YouTube thumbnail feedback |
