Quick Queries #79
By Richard Rost
32 days ago
DoCmd.Echo Command Dangers & Safe Usage in VBA
In this Quick Queries video, we discuss the risks and safe use of the Echo command in Access VBA, addressing how improper use can freeze your application and offering safer alternatives and error handling strategies. We also answer viewer questions on topics such as hosting Access databases online, optional query filters, saving query layouts, SQL Server setup basics, exporting all VBA code for searching or backup, sharing front end files, and distribution tools like J Street Access Relinker. Additional discussions include the continuing relevance of Access, handling Windows installation issues, and using wildcards for flexible query filtering.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, DoCmd.Echo, query filters, Winhost, SQL Server setup, Speed Up Loops, Me.Painting, error handling VBA, append query parameter prompt, wildcard query criteria, SaveAsText VBA, front end sharing, SQL Server Express, Network Attached Storage, Access Updater Template, J Street Access Relinker
Intro In this Quick Queries video, we discuss the risks and safe use of the Echo command in Access VBA, addressing how improper use can freeze your application and offering safer alternatives and error handling strategies. We also answer viewer questions on topics such as hosting Access databases online, optional query filters, saving query layouts, SQL Server setup basics, exporting all VBA code for searching or backup, sharing front end files, and distribution tools like J Street Access Relinker. Additional discussions include the continuing relevance of Access, handling Windows installation issues, and using wildcards for flexible query filtering.Transcript There is one dangerous VBA command you can use to make your Access database look like it's running faster, but if you do not use it right, you can lock up the whole application.
It is Friday, folks. Welcome to another TechHelp Quick Queries video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.
Today, we are going to talk about that command, what it actually does behind the scenes, and how to use it safely so you get the performance boost without freezing up your database. We have also got questions from my website forums, from YouTube, and some other sources about optional query filters, why Access is still a modern solution, front end sharing mistakes, exporting all your VBA code, SQL Server setup basics, and more. So let us jump in.
We are going to lead off today with a post from my forums, the Access Developer Forum on my website, from Donald, one of my platinum members. He shared a story about a client who tried to stop flicker by dropping the DoCmd.Echo False into their code without testing it first, and ended up freezing their Access screen.
This thread turned into a great discussion about how Echo actually works, how to recover if you lock yourself out, and some safer alternatives. I will put a link to this down below if you want to read the whole conversation.
I talk about using the Echo command in my Speed Up Loops video, but I also point out that it can be dangerous because if you turn Echo off and do not turn it back on again, or your code has an error in it, you are going to lock up your system. Well, it is going to appear locked up. Your Access database is going to appear locked up. This is one of those things that developers learn the hard way.
Echo off can absolutely help reduce flicker and speed things up when you are doing heavy screen updates. Just do not have an error in the middle of your loop.
Donald did mention also that you can usually still, if you are in developer mode, because usually when this happens, you are tinkering with it yourself, and it has not gotten to the end user yet. If you lock it up on your own, you can try to switch to the VBA editor. If you still have that open, open up the Immediate window and just type in Echo True, and that might restore the sanity of your Access application. Might.
Of course, always back up first before playing with Echo.
Kevin added a really important tip too, which is that Me.Painting = False only stops the redraw on the current form, not the entire application, so that can be the safer option.
I also like Darren's suggestion about wrapping this stuff in error handling, so you always force the screen back on if something fails.
Bottom line: Echo is powerful, but do not use it until your code is fully tested and you have got a clean exit path.
Next up, we have Ray, one of my Gold members. He is looking into Winhost, which is my Internet provider. I highly recommend them. He noticed that they can host Microsoft Access databases, so his question was, can you upload your Access backend file to a web host like that and connect your desktop front end to it over the Internet, kind of like you would with SQL Server?
That is a great question from Ray, and I used to get asked this all the time, and it is a really big point of confusion for a lot of people.
Yes, web hosts like Winhost can store an Access database file on the server. You can upload an MDB file or an ACCDB file, and it will sit there just fine. But in a web environment, the database sits on the same server as the website, and the server code can connect to it locally, so your ASP code, whatever you are using for your web language. This works because everything is running in the same place, on the same machine.
What you generally cannot do, I emphasize, cannot do, is connect your Access front end directly to that hosted ACCDB file over the Internet. Access is file based, as we have talked about in a lot of these videos. It expects LAN-style file sharing with fast locking and direct disk access. Over HTTP or FTP, that kind of connection is just too slow and very prone to corruption, especially with multiple users.
So if your goal is remote data access, SQL Server is the right tool for the job. That is exactly what it is built for, and hosting providers like Winhost do a solid job with SQL hosting. That is where my website is hosted. And it is not expensive, folks. You can get a website with SQL Server hosting for five bucks. Come on, five bucks! It is cheap. And they are good too. Unlike a lot of the big Internet providers I used to be with, I am not going to drop names here, but these guys are good. They run a nice, fast service. They get back to customer service emails right away. So very happy with them. I will put a link down below if you want to check them out.
And yes, I am an affiliate, so I will get a couple bucks if you sign up. But trust me, they are worth it. I do not endorse or recommend any services or products that I do not personally use myself. And I have had my website with these guys now for, I think, five years or so. So well worth it.
And if you want to get your Access database online with SQL Server, this is the seminar for you. I basically teach you exactly that: how to take your Access database, how to set up SQL Server on the web and connect your stuff to it. Just check this out. I will put a link down below.
Next up, I have a question from David, one of my Gold members. David ran into something strange while working with queries. He would build a query, it would run fine, and then he would resize the query window, so it only showed the field he was using. But when he reopened it later, the data looked broken and Access would prompt for field names, forcing him to rebuild the query from scratch.
And this is a good one from David because it looks like resizing the query caused the problem, but that is actually not what is happening. As Sammy pointed out, Access does not reliably save layout changes in Query Design View. The column widths, the window size, all that sort of stuff - that is cosmetic. It does not affect the query logic itself. So resizing the window is not what is breaking your data.
What is usually happening is that something in the query design got altered, like a field reference, an alias, or an underlying table change, and maybe you did not notice it until you reopened it.
Donald added a great practical workaround too. If you really want a saved layout for viewing data, build a simple form in Datasheet View instead of opening the raw query. Forms do remember layout, column order, widths, all that stuff. So use queries for data logic, forms for presentation, and remember your end users should never be working with queries or tables directly. Your end users should only have forms and reports.
Next up, we have a question from Raymond, one of my platinum members. This question is about my SQL Server course that I just started, exploring the server interface. For those of you who are following along with that, I got a whole bunch of questions that I am saving. I am going to do it as a lesson at the end of Level One for the SQL Server course. There are tons of questions.
But since that course is still running, I am going to do a quick queries style Q and A there instead of putting all those here, because I know a lot of people here are just Access users and do not care about that. You should, but this question is pretty generic, so I am going to include it here because you might be curious.
Raymond said for the longest time he thought SQL Server was basically its own operating system running on special machines. After seeing it installed alongside Windows and Access, he wanted to know how it really works. Can you run SQL Server on a normal PC, share it across a network, and how does that compare to something like a NAS, a Network Attached Storage device?
Great question. A lot of people picture SQL Server as this big mysterious box sitting in a locked server room somewhere with all kinds of cables and stuff running out of it. But no, SQL Server is just an application that runs on top of Windows. You can absolutely install SQL Server Express on a regular desktop PC and have 10, 20, 30, or even more users connecting to it across the network depending on the workload.
Alex made a couple of good points in the thread too. In larger environments, you will often see it installed on Windows Server hardware or even virtual machines for performance, licensing, or management reasons. Typically, you separate things out. The database lives on the server, the users connect from their PCs, and the admins might use SSMS from a different workstation.
Matt added some stuff here about the confusion between the different types of devices. As far as NAS goes, that is a totally different animal. A NAS is basically just file storage. It is like a hard drive that just sits there. SQL Server is a database engine actively managing the data - queries, transactions, locking, security, all the heavy lifting that Access back ends struggle with when you start getting lots of users.
Great question, thanks.
I just wanted to share something that one of my moderators, Kevin, posted. He had a clever coding thought. He wants VB newline to have an optional argument so you can specify how many blank lines you want instead of chaining VB newline over and over again. So he built a small custom function to handle it. I will just show this with you guys here. There you go. That is Kevin's code. It is really very simple, but I have done this myself a million times. You just go MyNewline, how many iterations, and then it just loops. How many times have I had to put like three new lines at the end of something or whatever?
These are just little ideas of how a tiny bit of coding can really make things much, much better in your database. It is one of those small quality of life improvements that just make your code cleaner. Sometimes the best developer tips are not huge architectural things but these little reusable utilities that save time and make your code easier.
All right, let us head over to the YouTube comments.
Who said "the YouTubes"? We are going back to the 90s or something when they used to use that "information superhighway" phrase when the Internet was new. Someone said something about "the YouTube." It is a series of tubes. I could Google it, but I do not feel like it. Some old senator or something who is way out of touch. Anyway, so that just kind of stuck with me.
The first question comes from Isaiah, who says he ran an append query successfully the first time, but after deleting the record and trying again, Access started asking him for a parameter value.
My regular listeners will know what that is all about. He is not sure what changed. This usually happens when Access cannot find something the query is looking for. Usually, it is a field name, a control name, or a table that got changed or you changed the field name in a table. So when you try it again, Access treats it like a parameter because it does not recognize it. It is trying to say, "Hey, I am looking for this field. What did you do with it?" So open the query up in Design View, check every field and criteria reference carefully. One small typo, a renamed field, will trigger the parameter prompt, and it is almost always something simple. I will be honest, it is usually a spelling error.
I said my regular viewers will know what this is about because this comes up a lot. I have a whole video on it.
Darryl Awesome says when he grows up, he wants to be just like me. That is fantastic.
In this video, I mentioned that I just got a new screen when I moved into my new house a few years ago. It has been three years now. The bigger screen that I got, I got this bad boy. It is a Samsung 49-inch Odyssey. It is a widescreen. I am blind in one eye, so I have to be really up close to the screen. But this allows me to spread things out so I do not have to cram. Looks like that monitor is no longer available; it is a couple of years old now. I am not surprised. But here is one. I paid a lot more. I think I paid like $1,200 for mine three years ago, but this one is close. If you are curious, I will put a link down below. Use my Amazon affiliate link. I will get like three dollars. Hope that answers your question.
Cricket Cool Fever says, "Please do not die before this course is finished." Yeah, I will do my best. I have been working out, as you guys have seen from my fitness database series. By the way, people have been asking me, yes, I am going to be getting back to it. It is not gone. We have a lot to do with the fitness database still, so it is coming back.
A side story or side quest, I actually had a customer a few years ago who was an MYOLP member, which is my all-you-can-eat Buffet membership - you just get access to everything. He wanted to upgrade to the lifetime membership, but he was like, "Well, how can I guarantee that you are not going to, like, get hit by a bus tomorrow?" I am like, I cannot guarantee that. I could be on a plane tomorrow and it goes down. Who knows? But I have been doing this for 30 years and my website has been online for 20. I guess it is like investing in the stock market. It is a gamble, but you have to weigh the risk. I have no plans on going anywhere. I do not plan on ever retiring. I love what I do, so I am going to be doing this until the day I die. So I will be here.
Next up, we have a question from Spreadsheet Geek. Love that name. He is using a form field as a query criteria, like I show in the Open Form filtering video. He wants to know how to leave the box blank and still run the query to show all the records instead of filtering it. In that, you could type in a state like Florida and then the query underneath looks at that form field to get the value and then just shows you the records in Florida. How do you leave it blank and still show all the records?
It is a great question. This comes up all the time. The key is if you want the filter to be optional, use a wildcard. So instead of forcing an exact value, you design the query so if the box is blank, it defaults to a star, which is basically "show everything." If there is text in the box, it will filter. If it is empty, you get the full list. It is super flexible and way more user friendly than forcing people to type something every time. So watch this video. It explains how to do it.
For all these videos, by the way, that I mention, you can either use the link here, scan the QR code, or I put links to them in the description down below the video. YouTube does a pretty good job of hiding them, but they are down there.
Next up, we have Stefan and it looks like some German. Let us hit the translate. He said the stupid AI voice is just annoying. Now, hold on a second, sir. Excuse me, sir. I have never used AI voice in my videos, and I never shall. That right there is 100 percent grade A homegrown radio voice. No robots, no voice clones, no synthetic Rick. If I ever do switch to AI audio, it will only be because I suffered some tragic, coffee-deprived, microphone-related throat injury. I do not know.
But I think I know what Stefan is actually hearing. YouTube has that auto dubbing feature now where it translates my voice into other languages using AI. Yes, some of those can sound a little robot-adjacent. The good news is it is getting way better than it used to be. If it ever sounds off, just switch back to the original track and then turn on subtitles. You can hear me talk in English, and the German subtitles are usually translated pretty well from what I have been told.
But no, I do not use AI for my voice. I do use AI for pictures, and I have done some pretty cool little mini videos, like penguins running around with black lab puppies and stuff. But as far as my voice goes, once again, sir, I assure you that my voice is 100 percent real. No AI.
Next up, Sims Learning Academy, K5U, says you are the real geek. Thanks. If there are Word and PowerPoint videos, please tag me the link.
Well, I do have some Word and PowerPoint videos, and Excel, on a couple of other topics. They are a little older right now. They are still good if you are a beginner because that stuff really has not changed much. Word and Excel are just fine. My PowerPoint lessons are a little old, though. Those need to be redone. Honestly, I do not get a lot of PowerPoint requests, but it has been on my list for a few years now. I do want to revise my Word and Excel videos, mostly because I want to show how to do VBA with those. There is some really cool VBA automation you can do with Word and Excel, especially from Microsoft Access. Things like having Access design a spreadsheet for you or taking contact information from your customer database and writing an actual document with it in Word. There is all kinds of cool stuff you can do. I will put links down below.
There is my Word class. It was recorded with Word 2007. Again, the interface has not changed much. They have added a bunch of new features, but Word is Word. Word is pretty much the same thing. So especially if you are doing beginner learners (in your username, it was K5U), for kids, definitely, this is just fine.
Excel was revised a little more recently. I think there is some 2010 when my book came out. What book, you ask? Well, yes, I actually wrote the Complete Idiot's Guide to Excel 2010 way back when. There is me standing in Barnes and Noble when my book was on the shelf. Yeah, I think you can still get copies somewhere on Amazon or one of those used bookstores. But it is still a good book. The stuff is all pretty much the same. Like I said, they have added a bunch of new features, but the old stuff still works. And yes, every now and then I have to check if it is in print, and it is. Paperback copies used - eight bucks. When I first did this, they sent me like three boxes of books, like 20 books in a box, and I gave them all away. I have one left. I think my friend Sammy got the last actual copy that I had. You are welcome, Sammy. You got the second-last copy that I had in existence. I have one more on my shelf and you are not getting that one. No one is getting that one.
One of my students, Donnie, actually mailed me his copy of my book so I could sign it and send it back to him. Donnie, I have not forgotten about it. I am looking right at it. It is sitting on my desk. I will take care of this soon. It has been there since before Christmas, I know, but I will get it out to you. Do not worry. I will send you a little something else extra too.
What else? I have some other stuff here. Oh, here is PowerPoint. PowerPoint's old. You do not want this. This is from 2003. This is when I first started doing this. There have been major changes to PowerPoint. You can see the differences there. It has got the old style File, Edit, View menus. This is good. I enjoyed this, but this really needs to be revised.
All the 101s, all the beginners, they are all free. You can check them out on my website.
Next up, Milan is back, following up on the "Why am I only seeing 99 records?" issue. He removed the Top 100 and set it to All, but the query is still stuck showing 99 rows. So he is wondering what else it could be.
There is a list of stuff, really. Check the query in SQL View. Open up a query, you can go right to SQL View, or open it up and right click SQL View. Even though the Query Designer says All, look for the word "Top" in here. Make sure it does not show up. I have seen "Top X," whatever, "Top 99," survive edits, copies, saves.
People accidentally run a different saved query. Check for stacked queries. If this query is getting its data from another query, which is getting its data from another query, any one of these could have that Top thing in it, that keyword, and it will cause the problem.
Look for words like DISTINCT, DISTINCTROW, GROUP BY, aggregate queries. If you have an aggregate query, that will limit your records. If the data is being shown, run it from the query first and make sure the query is actually returning the right number of records. Check the table. Make sure the records are actually in the table.
Verify the record source on the form. Design View, check the Record Source. Make sure it is actually the name of the query and not a SELECT statement in there with "Top 99." I cannot tell you how many times someone thought they fixed a query, but then the Record Source in the form was actually limiting the query further. That is a possibility.
It could be a filter. You might have a filter on it here under Data. I have one right here, filter on load is No, so it is not going to start with the form, but if this is set to Yes, then that will limit it as well.
In a pure Access environment, if you are not pulling records from somewhere else, like SQL Server or an Excel sheet, those are basically where it could be. If you are stuck at exactly 99, there is probably a Top clause or a filter or something hiding somewhere. You just have to trace it back. If you are working in a form, go right to the query. If it is working in a query, look at where that query is getting its data from. If any of those data sources are a query, look at those queries. You just have to peel back the layers like an onion.
Next up, we have one from MindAHacker, who is basically asking why we are still using Microsoft Access instead of upgrading to a more modern solution - the classic "Access is old" argument. I hear this one all the time. I am old too, but I am still good at a lot of things.
Look, Access has been around since the 90s. Sure. So has SQL, so has Excel, so has Windows. Longevity does not mean it is obsolete. It is battle tested. Access is still actively maintained. It is still updated. It is hands down the best rapid application development tool for desktop business systems. Period. You cannot find a better program for the desktop than Access. I challenge you, sir.
When you pair it with SQL Server as a backend, which is what I am doing in my new course, you have a full modern client-server architecture. You have a fast front end, enterprise grade data engine behind it. It is not outdated. It is efficient. So, if someone tells you Access is not modern, what they usually mean is they have never actually built anything serious with it. Mic drop.
Next up, this one comes from Leda, who says she misses hearing my real voice in English. This is the auto dubbing thing from YouTube. That is not me. I have no control over that. It is not me swapping myself out for a robot voice. YouTube automatically serves dubbed audio tracks based on your language and region settings. If you want to hear the original English version, go into the video settings, audio settings, and switch it back to English. My real voice is still there. I am not going anywhere. No AI takeover planned. At least not this week.
Next up is ZXYQ. OK, I am going with ZXYQ. He suggested doing weekend challenge assignments where students submit solutions and we compare approaches, maybe even make it a fun annual competition.
Alex, one of my best friends and moderators, always tries to get me to do Advent of Code every Christmas time. They do it, it is like a programming challenge. I just do not have time myself for stuff like that, but I love this idea because I used to do homework assignments in my regular classes.
The problem was nobody did the homework. I always felt like that teacher who walked in Monday morning all excited, and all of a sudden the whole room just suddenly finds the floor and the ceiling fascinating.
But I will tell you what, if enough of you are interested, I will consider bringing something like that back. Maybe in the Quick Queries, I will do a fun task or assignment or a challenge. You will have until next week to do it. If that sounds fun to you, post a comment down below and let me know. If there is enough people interested, maybe we will make it happen. But this time I will expect you guys to actually do the homework assignments, and we will see how many people post a comment to this. If I get enough, then maybe we will put something together.
I am all about the squeaky wheel gets the grease. In fact, that is why I started my new SQL Server courses, because I get tons of emails. "We got to get better security. We need to upgrade, we've got too many users, it's slowing things down." So I am like, all right, fine. I had other projects I was going to do first, but there is a real need for SQL Server, so that is why I am doing that. Squeaky wheel gets the grease. You guys want homework? I will give you homework. Then we will go over them the next week.
Next up, Bruce is asking if there is a way to get a full listing of all the VBA code in your database because he has got inconsistent field names across tables and it is making it harder to reference things clearly.
There is not a simple "print all my code" button in Access. The usual approach is to export your objects as text so you can search and review everything there. I cover how to do that in this video. It uses VBA, and you can use the SaveAsText command. You can loop through all the forms, reports, and modules and dump them out to text files or even one big long text file, which is great for searching, backups, even version control. You could even send objects as text - you can email someone a form.
I had a student way back where they were not allowed to send binary files, zip files, Access databases, any of that, but they could send text. So I told him, just save your object as text, email it to your buddy, and then he can just import it as text that way. That is another way to do it.
If you are fighting field name inconsistencies, that is your sign to standardize them now. Future you will thank you. I wish I would have standardized my stuff. I have fields that I named 20 years ago that I look at and think, "What was I thinking?"
I hope that helps.
Next up, we have ELM or Elm, whatever. If it is capitalized like that, usually it is an abbreviation.
He is asking if users can open the same front end from a shared drive. Can you do it? Yes. Should you do it? No, absolutely not. If multiple users open the same front end file from a shared drive, you are asking for problems - locking issues, corruption. The proper setup is one shared backend file, and then each user gets their own copy of the front end on their local machine.
I will admit, in my own office, sometimes I run a shared front end on a little not important database because I am the only one using it and I know I am only on one machine at a time. I would not open the same front end copy on two machines unless it is a database I do not care about - I have done that sometimes. But in a multi-user environment, local front ends are the way to go. This is the way.
Next up, Thomas wants to know if I have any lessons for Visual Studio. Thomas, I do not have any, but my good friend and associate Alex Headley does. He has a couple of VB.NET beginner videos on my website and they are free. You can check them out. There is a link. He did a real good job. He went through my old VB6 lessons and he converted them up to VB.NET. He covers all the new ways to do the same stuff that I did in my Visual Basic classes, which is really cool. He did a really nice job. He even put together a Level Zero class, which I really like, for people who need help understanding what .NET is, how to download it, how to install it, how to get started with the IDE. Check it out. They are on his YouTube channel, they are on my website. I will put a link down below.
Next up, we have another one here. This looks like Russian to me.
Next up, we have LH Pottery. He says his Windows 11 machine refuses to install anything - updates fail, printer software fails, everything just stalls out or errors mid install.
If it is not just Windows Update and you are seeing installs fail across the board - he posted this on my SQL Server course, but this is pretty much a generic question - it usually points to something deeper in Windows itself. How long ago did you get your machine? How long has it been running Windows? What kind of stuff have you put on it?
It could be corrupted system files, permissions issues, security blocking. Have you got a third-party antivirus on there? Get rid of that. You could spend hours chasing it, or just back up your data, do a repair of Windows if you can, and if not just do a fresh reinstall.
Honestly, I used to be like that. I used to dig around in the registry and try to find stuff - "Wow, what is causing this?" Honestly, I am at the point in my life now where time is too precious. I do not mess around. Once Windows starts acting funny and I have tried the quick stuff, you are just getting wiped. Just restore the backup or whatever - not messing with it. Just a fresh, clean Windows install, please. I would rather do that and spend an hour or two installing my applications again than mess around trying to figure out problems.
Next up, we have a question from Maurice who is asking about tools like J Street Access Relinker for automatically reconnecting front ends to backend files and how to handle front end updates and version replacements for users.
Great question, Maurice. Yes, I am familiar with J Street Access Relinker. I have never personally used it, but I know Armen from Access Day and he is a great guy. If he built this tool, I am sure it does exactly what it is supposed to do, and does it well. Looks like he has two versions on his developer downloads page, here is a modern one and then a classic one. I will put a link to this down below.
That being said, you can absolutely handle relinking yourself in VBA. I teach how to do that in the extended cut for this relink tables video. We check to see if tables have moved, prompt the user for the location, and it can relink them automatically using VBA. As far as front end distribution goes, when you make changes to your front end and want to get it out to all your users, I built this thing called the Access Updater Template. The user logs in, the database checks for a new version, pulls it down locally, and they are up and running. No manual relinking needed because the new front end already has the links built in when it copies it down from the server.
Speaking of Armen from J Street, do not forget that Access Day 2026 is officially on the calendar. You can register now. It is happening Friday, March 27, 2026 in Redmond, Washington, somewhere in the Redmond area. We do not know exactly where yet. I went last year, had a great time. I just booked my tickets for this year. Lots of solid presentations, great conversations, meet some folks from the Access team at Microsoft. They usually show up and share what they are working on. Just to be clear, I am not a presenter. I am an attendee just like everybody else, hanging out, learning, talking Access, geeking out with you guys. They are still finalizing the speaker list and they are also planning an optional dinner afterwards so everyone can get together and be nerdy.
If you are in the area or you can make the trip, definitely mark your calendar. I will share more details as I get them.
Make sure you stop by my website and check out what is new. I am always adding new videos, templates, updates, random bits of Access goodness, SQL goodness, other stuff. There is always goodness. Some kind of good.
Make sure you are on my mailing list, because I know YouTube does a pretty good job of notifying people when I drop new videos, but not always. If you want to get an email when new stuff drops, which is almost every day, but only one a day. I do not send out a lot of emails.
When you log on to my website and go to the subscribe page, you can pick what frequency you want me to bother you. If you want to get daily updates, great. If not, you can pick once a week or once a month, because that is what I want when I subscribe to mailing lists. I do not want to be bothered every day by a lot of stuff. Send me your quarterly update, that is fine.
Check out my Captain's Log where I post my thoughts about whatever I happen to be thinking about that day - sometimes Access, sometimes science, sometimes politics. You never know. Whatever is floating through my brain. Yes, I am usually in a Starfleet uniform.
Stop by my swag store, my merch store, get your t-shirts and all that stuff. I have some new stuff coming, I have some new shirt designs I am putting together in my spare time.
Grab a copy of my book off of Amazon, my Access book and my Excel book that I mentioned earlier is still available too. That is not on Kindle, though. This one is.
All right, so remember: use Echo carefully or you can freeze your whole application. Wildcard parameters make optional filters easy. Always give users their own local front end. If you need to audit your code, you can export your objects as text and search everything at once.
Post a comment down below. Let me know how you liked today's video. If you have any questions you would like me to cover in next week's Quick Queries, drop those in there too.
That is going to do it for this week, folks. That is your Quick Queries video for today, brought to you by accesslearningzone.com. I hope you learned something.
Live long and prosper, my friends. I will see you next time. Enjoy your weekend.Quiz Q1. What is the primary risk of using the DoCmd.Echo False command in Access VBA without proper handling?
A. It can reduce database performance
B. It can lock up the entire Access application if not turned back on
C. It deletes database records
D. It causes permanent data corruption
Q2. What is a recommended safer alternative for reducing screen flicker on a single form?
A. Me.Painting = False
B. DoCmd.Echo False
C. SaveAsText
D. Compact and Repair
Q3. If you do accidentally leave Echo off and your Access UI appears frozen, what might restore it if you are a developer?
A. Use Task Manager to end the process
B. Reboot the computer
C. Open the VBA Immediate window and type Echo True
D. Reinstall Access
Q4. Why should you not connect your Access front end directly over the Internet to an MDB/ACCDB file hosted by a typical webhost?
A. Access files are too large to transmit
B. Remote connections are too secure
C. Access is file-based and expects LAN-style file sharing, not slow Internet protocols
D. The front end does not support Internet connectivity
Q5. What is the right technology for robust remote database access with Access?
A. Use Google Sheets
B. Use a NAS device
C. Use SQL Server hosting
D. Host the ACCDB on Dropbox
Q6. When a query window in Access is resized and then reopened, why might Access ask for parameter values?
A. Because resizing corrupts the query
B. Because the window was too small
C. Because of changes in field names or underlying tables, not layout changes
D. Because Access does not support Design View
Q7. What is the best way to preserve user-specific data layouts for viewing in Access?
A. Store queries directly
B. Use macros
C. Build forms in Datasheet View
D. Modify the table structure
Q8. What is the main difference between SQL Server and NAS devices in terms of data access?
A. NAS devices run complex queries better
B. SQL Server is just a file store
C. SQL Server actively manages data with security, locking, and transactions
D. Both handle Access databases the same way
Q9. Why is it inadvisable for multiple users to open the same Access front end from a shared drive?
A. It uses too much bandwidth
B. It leads to locking issues and possible corruption
C. It slows down print jobs
D. It disables VBA code
Q10. What is the correct setup for a multi-user Access system?
A. One shared ACCDB for both front end and backend
B. Every user has a local front end file linked to a shared backend
C. Each user has a different backend
D. Use one backend per form
Q11. If a query in Access unexpectedly starts asking for parameter values after a table field is changed, the most likely reason is:
A. The field reference in the query no longer matches
B. Queries require refresh after each use
C. The table needs to be deleted and recreated
D. The form is not wide enough
Q12. How can you make a query criteria optional in Access so that leaving a form field blank shows all records?
A. Set Default Value to 0
B. Use a wildcard such as * if the criteria box is blank
C. Require all criteria to be filled
D. Only allow numeric criteria
Q13. When troubleshooting a query in Access that is only returning 99 records even after removing the Top 100 property, what else should you check?
A. The color scheme of Access
B. For stacked queries with their own Top or filter clauses
C. The language settings
D. The computer's network cable
Q14. How do you export all VBA code from an Access database for review or backup?
A. Print each module manually
B. Use the SaveAsText command in VBA to export objects as text
C. Use Compact and Repair
D. Run Performance Analyzer
Q15. What is a benefit of exporting Access database objects as text files?
A. They can be imported directly into Excel charts
B. They cannot be emailed
C. They allow for easy searching and version control
D. They lose all code formatting
Q16. What argument against using Microsoft Access as a modern solution does the presenter refute?
A. Access is easy to install
B. Access is slow
C. Access is outdated and not modern
D. Access does not support macros
Q17. What combination makes Access a modern client-server solution?
A. Access front end + SQL Server backend
B. Access front end + Excel backend
C. SQL Server front end + Access backend
D. Two Access backend files linked together
Q18. What is the main role of J Street Access Relinker or similar tools?
A. Compiling reports
B. Automatically reconnecting Access front ends to backend files after location changes
C. Analyzing VBA performance
D. Translating database to HTML
Q19. What should you always do before using potentially dangerous commands like Echo in your database?
A. Disable all macros
B. Back up your database
C. Increase screen brightness
D. Defragment the hard drive
Q20. What does the presenter recommend regarding homework or challenge assignments for viewers?
A. They are a waste of time
B. There is no interest and will never happen
C. If enough people show interest, he may bring them back
D. Only for paid members
Answers: 1-B; 2-A; 3-C; 4-C; 5-C; 6-C; 7-C; 8-C; 9-B; 10-B; 11-A; 12-B; 13-B; 14-B; 15-C; 16-C; 17-A; 18-B; 19-B; 20-C
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 from Access Learning Zone, we are exploring a range of Microsoft Access topics with a special focus on one particular VBA command that can make your databases look like they're running faster, but if used improperly, it can end up locking your entire application.
Every Friday, I address questions from my website, YouTube, and other places, and this session features questions about dangerous VBA practices, optional query filters, the continued value of Access in modern computing, common mistakes when sharing front end files, exporting all VBA code, setting up SQL Server, and more.
Let's begin with a question about the Echo command, which was posted on my Access Developer Forum. A member recounted trying to stop form flicker in Access by using DoCmd.Echo False in their VBA code, but forgot to turn it back on - resulting in a locked Access screen. The takeaway here is that while Echo can suppress screen updates to make things look smoother and sometimes faster, it must be used very carefully. If you forget to turn Echo back on, especially after an error occurs, you may find your database seemingly frozen. You can sometimes resolve this by opening the VBA editor and setting Echo to True in the Immediate window, but there's no guarantee. Always back up before experimenting, and consider alternatives such as setting Me.Painting = False, which affects only the current form, or wrapping your code in error handling to ensure Echo is turned back on even if something goes wrong. Ultimately, Echo is a powerful command, but only use it in well-tested, stable code with a clear exit path.
Switching gears, we looked at whether you can upload your Access backend to a web host - like Winhost, which I recommend - and then connect your desktop front end to that backend over the internet. While you can place your database file on a web server and access it via server-side code in web applications, you cannot connect your desktop Access front end directly to that file reliably over the internet. Access is designed for LAN-based file sharing and fast disk access, and using it this way is both slow and prone to corruption. For remote connections, SQL Server is built for the job, and hosts like Winhost provide cost-effective SQL Server options. If you're looking to get your data online, SQL Server is the way to go, and I have a seminar that walks you through the whole setup.
Next, one of our Gold members described a query issue. After successfully running a query and resizing the design window, the query later broke and prompted for field names. This wasn't due to window changes, since Access does not reliably save window layout or column width changes. Instead, some underlying modification - like a changed field reference or alias - probably caused the query to fail. For persistent layouts, use a form in Datasheet View. Forms remember layout settings and also keep users out of direct query or table access, which is good practice.
Raymond, a Platinum member, asked about running SQL Server on standard PCs. It's a common misconception that SQL Server is only for special hardware, but in reality, SQL Server is just an application you can install on any Windows machine. SQL Server Express works well for small workgroups, while larger environments benefit from dedicated hardware or VMs on Windows Server. The key distinction is that a NAS is simply dumb file storage, whereas SQL Server is an active data engine managing records, transactions, and security - far more powerful than what Access alone handles.
I also shared a coding tip from my moderator, Kevin. He created a VBA function to generate multiple new lines at once, as opposed to stringing together multiple VbCrLf calls. It's a small improvement, but little tools like this can make your code much cleaner and easier to maintain.
Turning to YouTube comments, one user ran into the classic append query issue where Access starts prompting for a parameter after the query worked once. This typically means a field or control name has changed and the query can't find what it's looking for. It's almost always due to a typo or renamed object somewhere in the chain. Always check your field names and criteria carefully.
Another question came up about optional query filters. The scenario: you want to use a form field as criteria in a query, but occasionally leave it blank to see all records. The answer is to use a wildcard - configure the query so that when the field is left blank, it defaults to a wildcard and returns every record. If you enter a value, the filter activates. This is a simple technique that makes your queries far more user friendly.
On the broader question of Access's relevance, some people still think Access is outdated. However, Access is actively maintained and updated, and it remains the best rapid application development tool for desktop business applications. When you combine Access as a front end with SQL Server as a backend, you get a fully modern client-server platform with robust features.
A few viewers had questions about exporting all VBA code from a database. There's no simple button for this, but you can use VBA to loop through all your forms, reports, and modules, export their definitions as text files, and use those for searching, backup, or even version control. If you often run into field name inconsistencies, it's a sign you need to standardize your schema - your future self will appreciate the effort.
Sharing front end files is another common topic. Never let multiple users open the same front end from a shared drive. This creates corruption risks and locking problems. Instead, distribute a separate copy of the front end to each user's local computer and keep only the backend on the shared location.
For users interested in coding beyond Access, my friend Alex Headley has recorded some great beginner VB.NET lessons, including clear explanations of what .NET is and how to get started in Visual Studio.
Occasionally, there are broader Windows problems, like failed installations or stalled updates on Windows 11. If multiple types of installations are failing, it's likely a deep Windows or permissions problem, sometimes related to security software or corrupted system files. While you can try to dig into it, sometimes the most efficient solution is a full backup, followed by a Windows reset or reinstall.
Questions also came in about automated relinking of frontend databases to a moved backend. While tools like J Street Access Relinker exist and work well, you can build your own solution in Access VBA. I show how to do this in one of my video tutorials, covering detection of missing tables and automated relinking. When it comes to distributing front end updates, my Access Updater Template can automate new versions for users seamlessly.
For those interested in live community events, Access Day 2026 is happening in Redmond, Washington. It's a great opportunity to meet other Access enthusiasts and Microsoft engineering staff for networking, learning, and fun.
If you want to stay updated with new Access tips, templates, and resources, head to my website and sign up for my mailing list. You can choose your notification frequency to get only the updates you want.
To sum up: use Echo only with caution to avoid locking up your database, design queries for optional filters by using wildcards, never share a single front end among users, and remember you can export and review all your VBA code for easier maintenance.
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 Using DoCmd.Echo to reduce screen flicker
How DoCmd.Echo False can freeze the Access interface
Recovering from accidental Echo False lockups
Differences between Echo and Me.Painting for forms
Wrapping Echo commands with error handling
Safely using DoCmd.Echo in VBA loops
Why hosting an Access backend file on a web host fails
Remote data access solutions with SQL Server
SQL Server basics and installing on a regular PC
Differences between NAS and SQL Server for Access
Troubleshooting Access append queries asking for parameters
Optional query filters using wildcards
Ensuring saved layouts with forms vs queries
Finding and removing Top clauses in Access queries
Resolving missing records in Access queries
Exporting all Access VBA code using SaveAsText
Best practices for Access front end sharing
Automatic relinking of tables with J Street Relinker
Front end update distribution approaches
Building custom VBA functions for quality of life (e.g., MyNewline)
How to reference form fields in query criteria
AI voice dubbing vs original voice on YouTube
Access as a modern business solution
Free beginner resources for Word, Excel, and PowerPoint VBA
Visual Studio and VB.NET beginner video links
Troubleshooting Windows installation errors
Handling field name inconsistencies across tablesArticle Today I want to address some frequently asked Access and VBA questions while offering solutions and best practices. We'll especially cover the use of a particular VBA command, DoCmd.Echo, which, if misused, can make your Access database appear to run faster but also carries risks if not handled properly.
Let's start by understanding what DoCmd.Echo does. When you work with Access forms and programmatically update lots of controls or run code that constantly updates the screen, users may notice screen flicker and delays. DoCmd.Echo False temporarily turns off screen updating, which can make loops and bulk actions seem to run much faster because Access does not waste processing power refreshing the window with every change. Once your code is done, turning echo back on with DoCmd.Echo True restores normal screen refresh. However, you must use this carefully. If your procedure turns echo off and does not restore it - whether by forgetting or because your code errors out - then your Access window will freeze, making it seem locked or broken, even though the database is still running in the background.
How can you recover if your screen gets stuck like this? If you are in developer mode and have access to the VBA editor, open the Immediate window (press Ctrl+G), and type DoCmd.Echo True. Hit Enter, and if you are lucky, your screen will come back. But if you have deployed this code to users, they likely will not have a way to do this, so always be careful.
The safest way to use Echo is always wrap it in error handling. Here is a standard VBA approach:
Sub YourCode()
On Error GoTo CleanUp
DoCmd.Echo False
' ... your code goes here ...
CleanUp:
DoCmd.Echo True
End Sub
This way, no matter what happens, the code execution will always restore screen updating. Also, consider alternatives that act more locally, such as Me.Painting = False, which just stops redrawing the current form, not the whole application. This is less risky and more focused if all you want is to prevent flicker during changes on that form only.
Now, moving on to another common question: Can you upload an Access backend (an ACCDB file) to a web host and connect your desktop Access front end to it over the Internet, similar to how SQL Server works? While some web hosts will let you upload and store Access files as part of your website, this only works when the backend and the web code (like ASP) are running on the same server. Trying to connect a local Access front end directly to a remote ACCDB file over HTTP or FTP is a recipe for disaster - it is slow and can easily corrupt your data, especially with multiple users. Access is designed for fast, local area network file sharing, not internet-based connections to a file. If your goal is true remote data access, use SQL Server, which is designed for internet and enterprise connections. Many hosts offer affordable SQL Server hosting, and linking Access with SQL Server provides a robust modern solution. It is worth considering because it avoids the reliability and speed issues that come with remote file-based Access backends.
If you want to get your Access database online with SQL Server, there are seminars and tutorials on migrating the backend, setting up SQL Server hosting, and relinking your front end accordingly.
On the topic of queries, a common concern is saving query layouts. Sometimes, after resizing or changing the field layout in Query Design View, you may see behavior like Access prompting for field names or the data appearing broken later. In reality, cosmetic changes like column widths and window sizes are not saved as part of the query logic. The usual cause for prompts or broken data is that field names or underlying tables have changed or been deleted. To save a specific field arrangement for users, create a form in Datasheet View based on the query - forms retain column order and width, while queries themselves do not. End users should access forms and reports, not raw queries or tables.
Another regular question is about SQL Server itself. Many imagine SQL Server as a mysterious, specialized system that only runs in big server rooms. In truth, SQL Server is just an application that can run on any Windows computer, from desktops to servers, physical machines or virtual machines. You can install free editions like SQL Server Express on your PC and allow multiple users to connect over your network. Unlike Network Attached Storage (NAS), which is simply file storage, SQL Server is a robust database engine that manages users, security, transactions, queries, and more - making it ideal for larger, multi-user environments since it handles tasks that Access backends struggle with as you scale.
Let's take a break for a small coding tip that can improve your VBA efficiency. Sometimes, when working in VBA, you want to insert multiple blank lines for formatting. Instead of typing vbNewLine several times, you can create a simple function that returns the desired number of line breaks:
Function MyNewline(n As Integer) As String
Dim i As Integer
For i = 1 To n
MyNewline = MyNewline & vbNewLine
Next i
End Function
Now, you can write MyNewline(3) to get three blank lines wherever needed. Little helper routines like this can tidy up code and make complex modules easier to read and maintain.
Another frequent Access question is about append queries: Why would an append query work fine once, but then prompt for parameters or fail when run again? Almost always, this means a field name, table, or reference was changed or misspelled. Access cannot resolve the reference and prompts you to enter a value. Double-check your query in Design View. Look for typos, rename mismatches, or missing tables. One simple mistake is often the root cause.
For those building search forms or filters, many people want to let users leave a field blank and still return all records. In the query's criteria row, handle this with a wildcard. Refer to the form field, and in the criteria use something like:
Like Nz([Forms]![MyForm]![MyField],"*")
With this, if the field is blank, you get all records; if it is filled out, Access filters accordingly. Using a wildcard as a default makes filters optional and user friendly.
Another troubleshooting issue crops up when you notice a query or form returning only a certain number of records, like 99, even though you expect more. This can happen if the query has a TOP clause - like SELECT TOP 99... - which limits output, or if other queries further upstream in your data chain have this restriction. Even if you set a query to "All" in the designer, check SQL View for any lingering "TOP" keywords. Also look for DISTINCT, DISTINCTROW, or GROUP BY keywords, which may affect record counts. Another culprit can be filters attached to forms - make sure you check the Record Source and Filter properties. Sometimes filters or limits are hiding further down in subqueries. Trace your data sources step by step, peeling back the layers until you find where the limit is imposed.
People often ask if Access is still modern or worth learning. Although it has been around since the 90s, longevity does not mean obsolete. Access is still actively developed and updated by Microsoft, and is one of the most rapid application development platforms for business desktop solutions. Paired with SQL Server as a backend, Access offers a fully modern client-server system. Often, those who criticize Access simply have not built complex or scalable systems with it.
Another question is about letting multiple users share the same Access front end from a shared network folder. Technically, yes, you can have many users open the same front end file over the network, but it is a bad idea. Doing so leads to locking problems and, worse, database corruption. Each user should have a local copy of the front end on their desktop, all connected to a shared backend file. This remains the "best practice" and will spare you hours of headaches and recovery work.
If you need to audit or search through all your database's VBA code, there is no one-click "print all code" feature in Access. Instead, export each object as text using the SaveAsText method. You can write VBA to loop through all your modules, forms, and reports, exporting their code to text files for backup or version control. Here's a simple approach using VBA:
For i = 0 To CurrentProject.AllModules.Count - 1
Application.SaveAsText acModule, CurrentProject.AllModules(i).Name, "C:\\Backup\\" & CurrentProject.AllModules(i).Name & ".txt"
Next i
Repeat for AllForms and AllReports by switching acModule for acForm or acReport. You can now easily search all your code with any text editor and have a copy for backup or review.
If you are tackling field name inconsistencies, there is no better time than now to standardize them. Consistent naming saves time and confusion later, especially when working with larger applications where you might forget what you were thinking years down the road.
For those interested in front end distribution, updating all user front ends as you deploy new versions can be automated. Tools like the Access Updater Template let users log in, download new versions of the front end, and stay current with minimal effort. Also, table relinking - when a backend moves or changes - can be managed with VBA or tools like J Street Access Relinker, which provide user-friendly relinking options.
If you are interested in learning more about VBA with Word, Excel, or even Visual Studio, there are free and paid lessons and classes available - look for up-to-date tutorials and keep in mind core functionality in Word and Excel have changed little over the years, so older resources are often still useful for beginners.
Sometimes, users run into issues installing software or updates, especially on Windows 11. If you see install failures system-wide - not just a specific app - it often means corrupted system files or permissions problems. You can try basic troubleshooting first like running the Windows System File Checker, but if the problem persists, backing up your data and reinstalling Windows might save you more time in the long run. I used to spend hours tracking down registry issues, but at a certain point, a clean install is faster and more reliable.
If you ever plan to attend a real-world Access event, conferences like Access Day offer great networking and learning opportunities, with sessions hosted by top experts and even Microsoft team members. It's a good chance to connect with other developers, solve problems, and share ideas.
To sum up, here are a few key takeaways. First, use DoCmd.Echo wisely and always restore it to True in your cleanup code or error handler to avoid locking up your Access UI. Make optional query filters easier by using wildcards and functions like Nz to catch blank entries. Give every user their own local copy of the front end - never share a single copy from a network folder. For searching or backing up your code, export everything as text so you can review or search easily. Finally, standardize field names and structures now; it will save you and everyone else hassle in future maintenance.
If you want to stay updated or get training on Microsoft Access, SQL Server, Word, Excel, or Visual Basic, there are free and paid resources, tutorials, and even full-length courses available. Always back up your work before experimenting, and do not be afraid to ask the community for help - the Access community is active and supportive. I hope this overview clears up some of the common issues and gives you a solid path forward as you work on your Access projects. |
