Quick Queries #95
By Richard Rost
7 hours ago
AI Responsible Use, 32 Bit vs 64 Bit Comparison, More!
In Quick Queries 95, we will discuss concerns about AI and its environmental impact, and whether adopting AI is moving too fast or is just another tool to learn. We will also talk about whether there is a real advantage to using 64-bit versus 32-bit Microsoft Access, the pros and cons of using saved queries versus SQL in a form's record source, the impact of name AutoCorrect on performance, issues around compact on close, limits on controls in forms, and handling the 255 field limit. Feedback and user questions from Reddit, YouTube, and email are also covered.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, AI environmental impact, 64-bit vs 32-bit, saved queries vs SQL, name AutoCorrect performance, compact on close, Access control limits, dynamic forms, ACCDE form zooming, referential integrity multiple back ends, 255 field limit, AI in Access, environmental responsibility, normalizing tables
Intro In Quick Queries 95, we will discuss concerns about AI and its environmental impact, and whether adopting AI is moving too fast or is just another tool to learn. We will also talk about whether there is a real advantage to using 64-bit versus 32-bit Microsoft Access, the pros and cons of using saved queries versus SQL in a form's record source, the impact of name AutoCorrect on performance, issues around compact on close, limits on controls in forms, and handling the 255 field limit. Feedback and user questions from Reddit, YouTube, and email are also covered.Transcript Do you have concerns about AI? Its environmental impact and whether we are moving too fast with this technology, or is AI just the next tool we are going to have to learn how to use?
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 whether we should stop using AI. Is it the next major technological leap, or are we moving too fast without considering the consequences? And yes, I will also explain what any of this has to do with Microsoft Access. We will also cover questions about 32-bit versus 64-bit Access, using saved queries versus SQL in a form's record source, name AutoCorrect performance, compact on close, and some interesting comments from Reddit, YouTube, and emails. Let's jump in.
Moving off today, we have NRGINs, one of the moderators over on Reddit, the MS Access Community. I have been spending a little more time over there lately, posting some of my Access Explained articles and joining in on some of the discussions.
NRGINs asks an interesting question that has been debated in the Access community over the years. Is there any real advantage to upgrading from 32-bit Access to 64-bit Access? I will leave my full reply up on the screen if you want to pause and read it. But the short answer is, for most Access users, not really. There is not that big of a difference.
In fact, I stayed on 32-bit Office myself for years. Back when Microsoft first started pushing 64-bit Office, my advice was actually to stick with 32-bit unless you specifically needed a reason to upgrade, because compatibility was better and it was still the standard, especially if you had older databases with maybe some 32-bit-specific components in them.
But now, today, 64-bit is the default installation, so now I am telling people, yeah, you probably should go to 64-bit. Either way, your forms, reports, tables, queries, and your VBA code are generally going to work the same either way.
The two-gigabyte limit is still the same. You are not going to get bigger databases or some magical performance boost just because you switched to 64-bit. There are some edge cases where 64-bit can help if you are doing heavy API work, working with very large external data sources, giant Excel spreadsheets, for example, anything that consumes a lot of RAM. Then having Access to more memory can be beneficial. But the average small business Access database, or even if you are using it as a front end to an SQL Server, most people are never going to notice the difference.
The biggest reason I recommend 64-bit today is not because it is faster, it is because that is where Microsoft is moving, and it is the default installation for new versions of Microsoft Office. Most newer databases and libraries assume 64-bit, and that is where all the future development is happening. So if you are starting fresh today, I would say, yeah, 64-bit, not because you need it, but because it is the safer long-term choice.
If you are still chugging along perfectly fine with 32-bit, if it is not broke, do not fix it. You are good until you run into a situation where something requires 64-bit, which is not that often. I know all of the new code and stuff that I post on my website, like in my classes, is all 64-bit, but there are pre-compiler directives you can use, and I have done videos on this with making your databases compatible with both 32 and 64-bit, depending on who is using it. There are all kinds of tricks you can do. But to answer the question, not that big of a difference, so I would not sweat about it.
Next up, we get Jeff from LA, one of my gold members. He says, is it better to use a saved query, or should you just put the SQL directly in a form's record source property?
In most cases, there is really no meaningful performance difference. Access is going to optimize and execute the query either way. For me, the decision usually comes down to organization and maintenance. If the same query is going to be used by multiple forms, reports, or different bits of code, then I usually save it as a query object. It gives it a name, makes it easier to document, and makes future changes simpler.
On the other hand, if it is a one-off query that is only going to be used by a single form, sometimes I will just leave it right in the record source property. Honestly, I use the query designer first a lot of the time. I know a lot of real developers go straight to SQL. I learned Access years before I actually learned SQL, so I will often use the visual designer to build and test a query first, then I will copy the SQL that it generates wherever it has got to go.
To me, it is less about performance and more about maintainability and whatever tool gets the job done fastest. One advantage of saved queries is that they are actually easier to troubleshoot. If a form will not load because of a bad SQL statement buried in the record source property, it can be harder to find. A saved query gives you a nice little named object you can go open and test by itself too. There are pros and cons either way.
Honestly, if I am writing VB code and I need something from a query, I will usually write the SQL there and keep it all in the VB code. Once in a while, it is really just a matter of time and habit or preference. There is no major benefit to one over the other.
The one benefit that I can think of for putting SQL directly in the record source is that you do not have to worry about accidentally changing it in the future by mistake. Sometimes I will have two or three forms that are based on the same query. Then I want to modify it because Form A wants something slightly different, and I will change it and not realize that there is a dependency that I forgot about for Forms B and C, so now I just broke Form C. So there can be benefits.
On the other hand, if you have a saved query and you know it is the same results used in four different reports, you might want to do it as a saved query because now you can update all four of those reports by just changing one query. So, whatever you want to do, just be aware of the differences.
Next up, we have Robert by email. He said, I have heard several Access experts recommend turning on compact on close and just leaving it enabled all the time. Is that something I should be doing or can it actually cause problems in some situations?
Well, of course, Robert, this is one of those situations where the answer is it depends, like the last couple of answers. If you are the only person using the database or if you are compacting your own local front end in a split database situation, like if you have a lot of temporary tables and stuff in your front end like I do, then compact on close is generally fine. You can keep the file size down and clean up some of the normal bloat that builds up over time.
Where it becomes a problem is in a multi-user environment. You should never be compacting a shared back end while other users might still be connected. Compacting requires exclusive access to the database. At best, you are going to lock people out, and at worst you could risk corruption if something goes wrong.
For shared databases, the best practice is to split the database and give every user their own copy of the front end. As I have said in pretty much every Quick Queries video, compact those front ends whenever you want, but usually what I do is I compact the developer version, my version, before I push it out to the rest of the network. That way they all get a freshly compacted one, unless you are the kind of guy who only pushes an update every six months and those users are also doing lots of temporary table work in their databases. Again, it depends on the situation.
I like running a compact and repair as part of scheduled maintenance, like once a week on a Sunday at 4 am, or whatever. You got a batch file that just goes through and compacts everything, or use my Access Updater backup utilities.
Another thing to remember, compact and repair is not some magical tune-up that makes Access run faster every time you click it. Its main job is to reclaim space from deleted records, reorganize the file structure, and clean up normal database bloat. It is worse if you have images and stuff that you should not be putting in your database. Most databases do not need to be compacted every single day. Once a week is plenty; some databases can go a month or longer without needing it.
Should you compact on close? Maybe. If it is a single user small database on your C drive, sure. If it is a shared back end that other people are using, no. So there you go.
Speaking of other people, I would love it if you would help other people find my videos. You can do that by hitting like and subscribe right now. It actually helps the YouTube algorithm promote my videos to other people so they can see them, and then it makes me happy. So thank you.
All right, next up, speaking of YouTube, we have JCWin talking about something that I mentioned in my specs and limits video, talking about the specifications and limitations of Microsoft Access and what the theoretical limitations are, the maximum physical limitations, and what is realistic.
JCWin said they are building a dynamic form and are worried that eventually they are going to hit the limit of 754 controls. The good news is you are probably not creating controls at runtime, as you said right here, because Access does not do that. Access does not normally let you create or destroy controls at runtime. You cannot just say make me a new text box or create another list box while the database is running and the form is open in FormView.
Access can move controls around, resize them, show them, hide them, or change their properties and captions, but the controls themselves must exist already. A good example is the kanban board system that I recently built in my developer lesson that followed the TechHelp videos. The user can have anywhere from two to nine lists on the screen to move tasks between, but I am not dynamically creating those list boxes on the fly. All nine list boxes already exist on the form. You can see here is the design view over here. Shrunk it up. Let me see if I can zoom in for you. They are all nice and tiny. When the form goes into FormView, the code determines how many of these you need and moves and resizes them to their appropriate spots.
If the user only needs four lists, I hide the other five and resize the ones that remain. That is how most dynamic Access forms are built. That 754 control limit is really about design time modifications to the form itself. Every time you physically add controls to a form in design view, Access tracks that internally. In theory, after 700 or so additions and deletions, you eventually hit the limit. They are kind of like autonumbers. You cannot get those old autonumbers back. If you make 10 records and delete six of them, those six autonumbers are gone.
Now, if you are repeatedly opening a form in design view and using VBA with CreateControl to physically add and remove controls, then in theory, you could eventually run into that limit. But if you are doing what I just did, showing, hiding, and moving or resizing controls that already exist, then you are not consuming that count.
If you really are generating forms dynamically in design view, creating a fresh copy of the form would effectively give you a new form object and should reset that count. Compact and repair might clean up some of the internal bloat, but I would not count on resetting that particular counter. Most of the time when you do something like that anyway, it is with developer tools. I have almost never opened up a form in design view and made modifications to it with code. Once in a while I have, but nine times out of ten I will just create a new form object and do the work that way.
Honestly, in 30-plus years of building Access databases, I have never run into the limit, and I have had forms with hundreds of controls on them. I have rebuilt forms, redesigned forms, and modified forms countless times, and I have never had Access tell me that I could not add another control because I had exhausted the lifetime allocation.
Also, Colin Riddington over at ISLADOGS mentioned that he has exceeded some of Microsoft's published Access limits before. That is one reason I tend to treat the specifications and limits as a guideline rather than as an absolute hard wall. Sometimes real world limits turn out to be a lot higher than what they officially document.
For the kind of dynamic form that you are describing, where you are importing different data and changing what the user sees, I do not think that is something I would worry about. If you are just reusing existing controls, then you are never going to notice that limit. If you are actually generating controls in design view, like you are switching from FormView to design view and making changes and then going back to FormView while turning echo off, there are all kinds of tricks you can play. You would still have to do a lot of creating and deleting before it ever became an issue, and if that is the case, just create a new form.
I would be curious to know more about exactly what you are doing and how you are dynamically creating that stuff, so feel free to post another comment. I will take a look at it.
Next up, Hollywood Beach Boys says that form zooming does not seem to work for ACCDE compiled files. I did some testing myself, and yes, I can confirm the behavior. The good news is that Microsoft is aware of the issue and is already looking into a fix. So if you are seeing the same thing, it is not just you. Hopefully we will see an update take care of this soon.
Keep in mind that form zooming is still a brand new feature. The Access team is actively working on it. There are still a few rough edges that they are ironing out. For example, they are still improving support for continuous forms and some other special cases. I have been using the zoom feature myself for the past couple of weeks, and I absolutely love it. It is one of those things that once you start using it, you wonder how you lived without it, instead of having to use the Windows Magnifier all the time. So yes, there are still a few little bugs to work through. They are aware of them and they are making progress, and it just shows you that yes, Access is still alive and well. They are still making updates and fixing stuff and adding new features. Huzzah.
Next up again, we have werburner, werburner, I do not know how to pronounce that. He mentioned that after turning off name AutoCorrect, his database actually ran faster. That is definitely possible. One of the things name AutoCorrect does behind the scenes is keep track of object names and relationships between forms, reports, queries, and other database objects, and all of that bookkeeping takes resources. In some databases, especially larger or more complex ones, turning it off can reduce a little bit of overhead. It is kind of like indexing fields in your tables. You do not want to over-index, because if you are indexing every single field, especially ones you do not need to index, then every time you add or update a record, Access has to update all of those indexes. It is a fine-tuning thing, where you want to index enough but not too much.
I do not want to oversell it. Most people are not going to turn off name AutoCorrect and suddenly see their database run twice as fast. In my own databases, I have never really noticed a dramatic performance difference. The main reason I disable it is that I just do not like how it is half baked. It changes some references and not others. It will update some queries and forms, but it does not touch your VBA code, so you end up with a false sense of security. If your database does seem a little snappier after turning it off, that is certainly not unheard of. I have had people tell me that before. Think of it like, that is just one of those things that if you are not really using it, or if you do not want to rely on it, turn it off. It is going to speed things up.
Next up, JCWin again, pushes back a little bit on my dislike of name AutoCorrect. They say that if Access updates most of the references automatically, is not that still better than having to track down and fix everything by hand? My issue is never that it changes too much. My issue is that it does not change enough.
If I rename a field and Access updates some of my queries, some of my forms, some of my reports, then that is great. But it leaves the VBA code, it leaves the SQL strings built in the code, it leaves macros and some other references untouched. Now I am in an awkward situation where some things have been updated automatically and some have not. So I still have to go through and verify everything manually anyway.
Personally, I would rather have one consistent process. If I am going to rename a field or a table or any object, I want to know that I need to update all the references myself. That way, nothing slips through the cracks because I assumed Access handled it for me.
Sure, if you are working on a smaller database with little or no VBA code, then name AutoCorrect can absolutely be helpful, and that is the one reason Microsoft included it. I just do not trust it enough on larger developer-level projects where a missed reference can turn into a bug that is hard to track down later. I would not say it is a bad feature. I just think it is one of those features that sounds better in theory than it works in practice, like multi-valued fields or attachments.
Next up, we have Shobin, who makes a comment that I see from time to time. He says it only seems like I respond to premium members. Actually, that is not true at all. I am responding to you right now. There is proof. In fact, most of the questions that I answer on YouTube come from non-members. You will see the little circle next to the name of a member on YouTube. In fact, earlier in this very video, I answered this question from Robert, who emailed me a question, and he is not a member.
Most of the questions that I answer on Reddit or other forum sites come from non-members. In fact, a lot of the Quick Queries topics you see each week started just as basic YouTube comments, Reddit posts, forum discussions, or random emails from people who are not members at all.
It is true that I do not provide one-on-one technical support by email. I stopped doing that years ago because there just simply are not enough hours in the day. If I answered every technical question that landed in my inbox, I would never have time to do anything else, including making videos. That is why I encourage people to post questions publicly in the forums, YouTube, Reddit, and other places where everyone can benefit from the discussion. So I do not answer individual emails usually, but I will put them in a video or make a forum post answer sometimes.
Do I answer every question? No. There are way too many of them. I answer the ones that I see, the ones I have time for, and the ones I think will help the largest number of people. In fact, some of my TechHelp videos and many of these Quick Queries segments actually started because somebody posted a really good question in a YouTube comment, and I answered it. So if you have a good question, ask it. I cannot promise I will get to it, but membership has never been a requirement for getting your question answered.
To be completely fair, if you are a gold, platinum, or silver member from my website, then yes, you are probably going to get a better chance of getting a more detailed response or a video. That is one of the benefits of membership. I do give priority to the people who help support what I do. That is very different from saying I only answer members. I answer questions from nonmembers all the time. If someone posts a good question and I think it will help a lot of people, I will answer it. But yes, some members do get priority. I think that is only fair, but no, I do not exclude anybody else. Good questions are good questions regardless of where they come from.
By all means, post your question, but do not post it as a reply to the one that you already posted, because I will not see it. I almost never see reply questions. I will see new ones because then I have to approve them. So post a new one if you have a question. If you really want a good chance of getting an answer, post it in the forums on my website, because here on YouTube, for example, it is just me. I am the only one answering questions. But on my website, I have a whole bunch of great moderators and advanced developer students and people that pitch in to help answer questions. I could not do what I do without them. They run the website more than I do. Yes, there is a visitor forum, so you do not have to be a member to post. So if you want to get a good answer to your question, that is the best chance you have right there.
Next up, we have Jackson, who thought he had a gotcha when I mentioned referential integrity with multiple back ends, because you cannot have referential integrity between multiple back ends. If you have customers in one back end database and orders in another and you want to make sure that you do not add an order for a customer that does not exist, normally, in a single database, you could set up referential integrity for that. In multiple back ends, you cannot do that, so you have to do it with VBA code. I have covered how to do that in several other videos. I think I am going to make another video on it soon, a simpler one. Thank you for bringing that up.
If any of you have ever found a gotcha in one of my videos, point it out. I love it when you guys point stuff like that out to me. I am not perfect. I appreciate the extra set of eyes because I only have one. Sometimes those comments will turn into a TechHelp video or a future Quick Queries video. I love it when you guys teach me new stuff, because there is stuff buried in Access that I do not even know about, and you sometimes bring it to my attention. I love learning. I am not one of those quote-unquote experts who says I know everything. I will be the first person to admit there is a lot I still have to learn. I am constantly learning. I consider it a bad day if I do not learn something new, whether it is in Access, programming, science, or whatever.
Next up, we have Ford Factor sharing a story about running into the Access 255 field limit. He was working with a medical research survey that had over 300 different metrics they wanted to track, and he had to redesign the database to work around that limitation. That is definitely a real limitation. I actually ran into that myself years ago with a mailing list company that I was working with, one of my clients back when I used to do consulting and database building. They had a similar problem. They were tracking all kinds of demographic information about people: gender, ethnicity, household size, income brackets, interests, buying habits, dozens and dozens of other attributes. Their first instinct was to build the database in-house. Before they hired me to come in and update it, their guy made a classic mistake. He made every single one of those things a separate field in a giant table. I see that all the time. The problem is, you will eventually run out of columns; you will run out of fields. The 255 fields per table. Honestly, if you are getting anywhere near that number, it is usually a sign that the database needs another redesign.
What I ended up doing was moving those attributes into a separate table and storing them as name-value pairs. Instead of having a gender field, an ethnicity field, a household size field, and 200 other fields, now we just have a person ID, an attribute name, and an attribute value. One record could be gender equals female, another could be ethnicity equals Hispanic, or whatever. Now you can store as many attributes as you want without being limited by the number of fields or columns in a table.
I understand why Ford Factor's situation was different because you are trying to export data to another application or a statistics package or some kind of reporting system that expects every metric to have its own column. There you might be forced into a wide table design, and in that case, the limitation becomes a real issue. But I would argue that is less of an Access problem and more of a requirement being imposed by the other program. From a pure database design perspective, once you get into hundreds of similar fields, it is often a sign that those fields should really be records in a related table. That goes back to the whole order thing: item one, item two, item three. You do not do that. That gives you the best way to make an expandable database. You and your users can add new fields without having to go into design view. You can give them the ability to add, remove, or edit fields on their own. It is just a much more flexible system.
I get it, though. I have had to work with other databases too where they are like, no, our vendor needs the data in this particular format. It has got to be this way. Well, if you have got 300 fields, can their database read that in? On your end, it is easy. You can just make a query and then export that query in whatever format they need or a CSV file or whatever. I have never, in 30 years of consulting, come up with a problem that could not be solved by properly normalizing the table for Access. Now, exporting it for some other requirements, that is a whole different story. But yes, definitely, thank you for sharing.
Finally, tonight, I received an email from John up in Windsor, Ontario, talking about data centers and AI usage and all of this stuff. I will leave it up here so you guys can read it.
John and I actually agree on a lot of this, but we come to some very different conclusions. John's concern is that AI uses a tremendous amount of electricity. Data centers consume huge amounts of power and water and all of that has environmental consequences. On that point, I completely agree. Those are legitimate concerns and they are concerns that governments, regulators, and the companies building these systems need to take very seriously.
Where I disagree is when people say the answer is to simply stop using the technology. To me, that is like saying, it is 1995, the internet uses too much electricity, stop using email and go back to fax machines, or saying cars consume fuel and create pollution, so let's stick with horses. History has shown us over and over again that once a useful technology arrives, it does not go away. The printing press did not go away. The steam engine did not go away. Computers did not go away. The internet did not go away. AI is now part of that list.
The question is not whether we are going to use it. The question is how do we use it responsibly. I absolutely believe there should be environmental standards. I think data centers should be efficient. I think companies should be held accountable for their environmental impact. I think governments should be investing in cleaner energy and protecting the environment. On that, we agree 100 percent.
I do not think the climate problem is going to be solved because Richard Rost turns off his laptop at bedtime. Yes, my computer uses electricity. Yes, your computer uses electricity. But the solutions to climate change are much larger than individual people unplugging their devices at night. This is a societal change that requires better infrastructure, cleaner energy production, smarter regulation, and long-term planning.
As a solo entrepreneur, I am a one-man band. I use AI to help me research topics, organize ideas, check my work, and create a better product for you. It does not replace me. It does not make my videos for me and it never will. It simply helps me to do my job better. In the same way that accountants adopted calculators, engineers adopted computers, and businesses adopted spreadsheets, this is another tool that people are going to have to learn how to use.
Frankly, what worries me most is not humanity. It is not my kids and grandkids. Humans are incredibly adaptable. We have survived ice ages, wars, pandemics, all kinds of environmental changes. What worries me most are the species that cannot adapt as quickly - the wildlife, the shrinking habitats, the ecosystems that disappear, and yes, penguins losing their ice. They depend on that. That is the stuff that keeps me up at night. Once a species is gone, it is gone. That is why I think environmental protection matters so much, not for humans, but for the rest of the planet. There are five or six species of penguins that are critically endangered right now.
To be a little political for a moment, just a little bit, I know you are up in Canada, but I am sure you are well aware of what is going on down here. I am very pro-science, and one of the things that concerns me right now is seeing environmental protections that took decades to put in place being weakened or dismantled. I do not care whether it is a Democrat or Republican doing it. If the science says a regulation is protecting the environment, then I will support it. I understand that regulations cost money; businesses do not like regulations because compliance costs money, but sometimes that is the price of living in a civilized society. If a company has to spend more to reduce pollution, to protect water resources, to lower its environmental impact, then that is money well spent. If businesses have to spend billions of dollars to protect the environment rather than giving out millions in bonuses to their CEO, that is what they are going to have to do. Yes, some of those costs eventually get passed on to consumers - that is reality. If ChatGPT costs me 50 dollars a month instead of 20 dollars a month because OpenAI had to meet some stricter environmental standards, use cleaner energy, or offset more of its carbon footprint, then I will pay it. I would rather spend a little more and know we are being responsible than to save a few bucks and pretend the problem does not exist.
To me, that is the real conversation we should be having - not whether we should abandon technology, but how we make sure technology is developed and used responsibly. Let's have a serious conversation about the cost of AI. Let's make sure we are building these systems properly. Let's push for cleaner energy and better environmental stewardship. I do not think the answer is to pretend that technology does not exist or to refuse to use it. The genie is already out of the bottle. Pandora has already opened the box. The better approach is to learn how to use it wisely and make sure it is developed responsibly.
Finally, John says he prefers real pictures of me over the AI-generated Star Trek versions. That is fair enough. But even my profile picture that I use in all my videos is not exactly real. That picture is probably about 15 years old, and I am pretty sure I ran it through some Photoshop when I first made it. Now we are getting into a philosophical discussion. What exactly is a real image anymore? Do you really want to see what I look like today? I do not think you do. So thanks for the email, John. Even though we do not completely agree, I appreciate thoughtful discussions like this. These are exactly the kinds of conversations we should be having as technology continues to evolve.
Before someone chimes in and asks what any of this has to do with Microsoft Access, quite a bit actually. As many of you know, I have already done several TechHelp videos showing how to integrate AI into Access applications. We have looked at connecting to AI services, generating contact, analyzing data, automating different kinds of tasks. Honestly, what I have shown so far is just scratching the surface.
In fact, one of the future courses on my roadmap is a full Microsoft Access and AI course. My SQL Server series is still my top priority at the moment. Part two is almost finished. I have a couple more things to do with it. But AI is definitely something I want to spend more time teaching.
Whether we like it or not, AI is just becoming another tool that is available to developers. Just like we learn how to use VBA and SQL and ODBC and web services and all the other technologies that came along over the years, Access developers are going to have opportunities to use AI in their applications too. I use it every day. While I respect John's concerns and I think we absolutely need to have conversations about environmental responsibility, regulation, and sustainability, I do not think the answer is to ignore technology. My approach is to learn it, understand it, teach it, and help people to use it responsibly. Whether we are talking about AI, the internet, computers, databases, airplanes, or whatever, the people who learn how to use new tools effectively will do a lot better than the people who pretend those tools do not exist.
As for me using AI to generate images, I am sorry, but that genie is out of the bottle too. I love this technology. Being able to create custom illustrations, funny scenes, visual examples for my videos and my title slides in just a few minutes is amazing. I cannot wait to see where it goes next. The day I can generate little custom video clips like this, just for fun, you know I am going to be playing with it. I will never replace, I will never use AI to completely replace my videos. I still like making my own content. I like teaching. I love recording these videos. I like the way that I am producing my videos. AI images are just another tool in my toolbox, like I teach you guys for Access. Like a camera, a microphone, or a video editing application. If it turns out this technology has environmental costs that need to be addressed, then that is something society and our governments are going to need to work on. I will continue supporting leaders who take environmental issues seriously. But asking individual people to stop using useful technology is not the solution. It is like asking me to stop driving my car. History shows that once a tool becomes useful, people are going to use it. The better approach is to figure out how to make it cleaner, more efficient, and more sustainable.
Before you go, do not forget to stop by my website, check out what is new. I am always adding new videos and other goodies. Do not forget to check out my Captain's Log where you can read more of my ramblings. In fact, today I almost put that part about AI in a Captain's Log article, but then I thought, no, that deserves full video reviews. That is why I put it here. Normally, stuff like that goes in the Captain's Log.
You can see more AI-generated images of me. I just like it because it helps to tell a story, and sometimes it is funny. Who does not want to see a picture of me reading a book on a riser with some fog in the background, just like Captain Picard? Check out my merch store. Get some t-shirts, get some hats, get some coffee mugs, or some mouse pads. Check out my book on Amazon. Stop by the forums on my website. If you need an Access tutor or developer, check out my Access Developer Network. Make sure you sign up for my mailing list and get all kinds of goodies.
Today we learned that AI is not something we can simply ignore or wish away. The better approach is to learn how to use it responsibly while pushing for smarter environmental policies and cleaner technology. We also learned that 64-bit Access is not a necessity for most users, but that is where the future is going, so it is probably a good idea if you are not there already. We also learned name AutoCorrect is often more trouble than it is worth, and that compacting your database on close really depends entirely on your situation.
Post a comment down below. Let me know how you like today's video, and if you have a question for next week's Quick Queries. I am really curious what a lot of you think about AI and environmental impact. Do you agree with me? Do you disagree? Do you agree with our friend from Windsor, Ontario? Let me know what you think. I want to read your thoughts. I love when you guys post comments like that.
That is going to be your Quick Queries TechHelp video for today brought to you by AccessLearningZone.com. I am Richard Rost. I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main environmental concern related to AI that was discussed in the video?
A. AI uses a significant amount of electricity and water in data centers.
B. AI produces harmful chemical waste.
C. AI creates air pollution through hardware manufacturing.
D. AI increases noise pollution in urban areas.
Q2. According to Richard, what is the best approach to dealing with environmental concerns caused by new technology?
A. Stop using the technology altogether.
B. Return to older technologies like fax machines.
C. Use the new technology but push for responsible development and environmental standards.
D. Wait for technology to become obsolete before taking action.
Q3. What is the main reason Richard currently recommends 64-bit Access over 32-bit for new installations?
A. 64-bit Access runs significantly faster for all applications.
B. 64-bit Access handles larger databases with no size limit.
C. 64-bit is the default and where future development is focused.
D. 32-bit Access will no longer receive security updates.
Q4. In what situation does "compact on close" pose a risk in Microsoft Access?
A. When you use a database on an SSD drive.
B. When compacting a shared back end database while others are connected.
C. When using it on a split database front end.
D. When running compact on close on a read-only database.
Q5. When is it generally best to use a saved query object versus putting SQL directly in a form's record source?
A. When the query will only ever be used once.
B. When the query is reused by multiple forms, reports, or code.
C. When dealing with simple select statements.
D. It does not matter; there is never an advantage.
Q6. Why might turning off name AutoCorrect improve database performance?
A. It disables all indexes in the database.
B. It reduces the internal bookkeeping Access must do.
C. It allows larger forms to be created.
D. It increases the speed of VBA code execution only.
Q7. What is Richard's main criticism of the name AutoCorrect feature in Access?
A. It changes too many objects automatically.
B. It increases the file size of the database.
C. It is inconsistent and does not update all item references, especially in VBA code.
D. It requires special hardware to function.
Q8. Which of the following is NOT a valid reason for hitting the 255-field limit in an Access table?
A. Having hundreds of demographic columns in one table.
B. Storing attributes as name-value pairs in a related table.
C. Designing the application to export to a system that expects one column per metric.
D. Improper normalization leading to a wide table design.
Q9. How does Richard recommend handling database referential integrity when working with multiple Access back end files?
A. Rely solely on Access's built-in referential integrity.
B. Use VBA code to ensure integrity between related tables in different back ends.
C. Ignore referential integrity altogether.
D. Use SQL Server replication.
Q10. Regarding creating controls dynamically on Access forms, what is the crucial limit to be aware of?
A. Access forms can only display a maximum of 10 controls at runtime.
B. The 754 controls limit applies to design time modifications, not runtime show/hide.
C. You cannot move or resize controls at runtime.
D. Each database can only have 754 controls in total across all forms.
Q11. What does Richard suggest is the appropriate way for society to address the high energy use of AI data centers?
A. Rely on individual users to stop using AI tools.
B. Implement stronger regulations, cleaner energy, and hold companies accountable.
C. Wait for AI to become self-sustaining.
D. Ignore the issue because technology will solve itself.
Q12. How does Richard see the role of AI in his own workflow as an instructor and content creator?
A. He uses AI to completely replace his teaching videos.
B. AI generates and uploads all of his content automatically.
C. AI is a tool that helps research, organize, and create illustrations, but does not replace his unique teaching.
D. He does not use AI at all due to environmental concerns.
Q13. What is Richard's attitude toward using AI-generated images for his videos?
A. He is against using any AI-generated images.
B. He uses them as a useful and creative tool alongside traditional methods.
C. He relies solely on unaltered real photographs.
D. He pays others to generate images manually instead.
Q14. How does Richard recommend students and viewers get their Access questions answered most effectively?
A. Email Richard directly for all technical questions.
B. Only premium members can ask questions.
C. Post questions in public forums like his website for community answers.
D. Submit questions via postal mail.
Q15. What is Richard's overall approach toward new technology like AI?
A. Avoid it due to risks and environmental concerns.
B. Learn, understand, and teach it while supporting responsible and sustainable use.
C. Wait until it is regulated by governments before using it.
D. Only use it if there is no environmental cost.
Answers: 1-A; 2-C; 3-C; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B; 11-B; 12-C; 13-B; 14-C; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I am addressing a broad mix of questions around Microsoft Access, some practical issues in database management, as well as some philosophical points about evolving technology, artificial intelligence, and their impact on the environment.
Let's begin with a popular topic in the Access community: whether it's worth upgrading from 32-bit Access to 64-bit. Over the years, there was little push to switch unless you had a specific need, since 32-bit was more compatible, especially with older components. Today, 64-bit is the default installation for Office, and heading into the future, that's where all new development is focused. Despite this, most Access users will not see significant differences between the two versions. Core functions like forms, reports, queries, and VBA code behave the same, and the file size limit is unchanged. Unless your applications work with extremely large datasets, or you rely heavily on external files (like very large spreadsheets), you will rarely benefit from additional memory access that 64-bit can offer. In summary, if you're starting fresh or updating, you should go with 64-bit simply because that is where the platform is headed. If 32-bit still serves you well, I wouldn't rush to replace it unless you encounter a specific incompatibility.
Next, there's the question of whether it is better to use a saved query or place the SQL directly in a form's record source. From a performance standpoint, there is usually no real difference. Access will optimize and execute the query either way. For me, the decision typically boils down to organization and maintainability. If multiple objects depend on the same query, I recommend saving it as a named query. This makes updates easier and helps with documentation and troubleshooting. If it is a single-use query tied only to one form, embedding it directly can be faster but be cautious about making later changes because shared dependencies can lead to surprise bugs. Personally, I use a mix of both approaches, choosing what fits best for the specific use case.
We also had a question about enabling 'compact on close' in Access. Should you turn it on by default? The answer is that it depends on your situation. If you are managing a single-user database, or each user has their own local front end in a split database environment, compacting on close can keep the database tidy. However, in a shared multi-user environment, you must never compact the back end while others are connected, since it requires exclusive access and can lock users out or even risk corruption. For shared databases, compact and repair should be run as planned maintenance, such as on a weekly schedule. In general, this feature is great for cleaning up bloat and reclaiming space, but you do not need to use it every day.
A user also asked about running into the Access limit of 754 controls per form. This is not as problematic as you might think. Access does not allow true creation or destruction of controls at runtime; you can only manipulate controls that already exist. If you design a form with several controls and just hide, show, or move them, you are not adding to the total count. That 754-control limit is tied to modifications done in design view, not during runtime. In all my years building Access applications, even with complex forms, I have never run up against this limit.
Another comment highlighted a bug with the new form zooming feature not working properly in ACCDE compiled files. Microsoft is aware of this glitch and is actively working on a fix. Since form zooming is a relatively new addition, there are still some rough edges, but improved support is in the works.
Regarding name AutoCorrect, some people notice performance improvements after turning it off. Name AutoCorrect adds overhead because it tracks changes to object names and relationships, and in larger databases, disabling it can reduce lag. However, its promise is often overstated; it misses renaming references in VBA code and SQL embedded in code, which can create more problems than it solves. For larger projects, I generally recommend not relying on name AutoCorrect, even if it helps in smaller, simpler databases.
There was also a question about why sometimes it seems I only respond to premium members. Actually, most of the questions I answer are from non-members, whether on YouTube, Reddit, forums, or email. While I do give priority to supporting members (since they help fund my work), I always try to spotlight good questions wherever they come from, especially if they help a large number of people.
There were some interesting responses regarding database design, such as hitting the 255-field-per-table limit in Access. Once you reach these kinds of limits, it's often a sign that the database needs a redesign. Rather than cramming every attribute into a single table, it's better to use related tables with key-value storage, making your structure much more flexible and scalable.
Toward the end of the video, I also tackled a thoughtful question about the environmental impact of AI and large data centers. There's no question that AI and cloud infrastructure use a lot of electricity, and I absolutely agree that environmental standards, regulations, and cleaner energy must be part of the equation. However, I do not believe the answer is to refuse to use AI or new technologies altogether. History has shown that technological advances are rarely reversed, whether it was printing, the steam engine, or the internet. Instead, the focus should be on directing development responsibly, with environmental stewardship, sound regulation, and investment in sustainability.
From my perspective, AI is quickly becoming another tool in our toolbox, just like VBA, SQL, and other technologies that have become standard. We need to learn to use such tools responsibly and advocate for their development in sustainable ways. While I acknowledge that AI and data centers create an environmental footprint, the solution has to come from smarter infrastructure, societal regulations, and robust policies, not individual withdrawals from useful technology. I support clean energy and strong environmental protections. If greater responsibility means paying a little more for cleaner AI services, I am willing to do that.
Some final notes: yes, many of the graphics in my videos are AI-generated. Just like using Photoshop, a camera, or any other creative tool, AI-generated images help tell a story more quickly and creatively.
As for how any of this connects back to Microsoft Access, I have already shown several ways to integrate AI into Access, like connecting to AI services for analysis or generating content. Looking ahead, I plan to release a full Access and AI course as part of my training lineup.
In summary, today's Quick Queries video has covered a range of topics: the pros and cons of 32-bit versus 64-bit Access, tips on query management, advice on compacting databases, how to deal with control and field limits, the real value (or drawbacks) of name AutoCorrect, and some bigger-picture discussion on AI's future and environmental impact. If you have feedback or thoughts, especially on AI and its role in technology, I always welcome your comments.
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 AI environmental impact and responsible use
Adoption of AI as a technological tool
Switching from 32-bit to 64-bit Access
Access database compatibility concerns
Choices between saved queries and SQL in record source
Maintainability in Access queries and forms
Compact on close: benefits and risks
Safe compaction practices for Access databases
Access form control limits explained
Dynamic forms and runtime control management
Impacts of Name AutoCorrect on performance
Disabling Name AutoCorrect in Access
Referential integrity across multiple back ends
Workarounds for Access 255 field limit
Normalizing wide tables with attribute-value pairs
Perspectives on individual action vs. systemic change for AI's climate impact
Integrating AI with Microsoft Access applicationsArticle Today I want to discuss several important topics about Microsoft Access and technology in general, including the environmental impact of artificial intelligence, the transition from 32-bit to 64-bit Access, saved queries versus SQL in record sources, the use of compact on close, and certain database limits and best practices. I will also touch upon the ongoing debates around using AI responsibly. While some of these are frequently asked questions (and sometimes debated in forums and user groups), others are concerns that are becoming more relevant as technology evolves.
Let's begin with a question that affects many Access users: whether there is any real advantage to upgrading from 32-bit Access to 64-bit. For most typical users, the difference is negligible. Some years ago, I stayed on 32-bit Office because it offered better compatibility, especially with older databases and 32-bit specific components. The main user experience with forms, reports, tables, queries, and VBA code does not really change between the two. The database size limit, two gigabytes per file, does not increase, nor does performance magically improve. The only real advantage to 64-bit is in certain edge cases where you are doing heavy API work, working with extremely large data sets, or dealing with massive Excel files that use a lot of RAM. For most small businesses or applications using SQL Server back ends, you will not notice a difference.
Today, most new installations of Office are 64-bit by default, and ongoing development is focused there. If you are just starting a new project or moving to a new PC, 64-bit is the safer long-term choice simply because Microsoft is shifting the ecosystem in that direction. If your system runs fine on 32-bit and has no requirements for 64-bit, there is no urgency to upgrade. However, if you encounter libraries or situations where 64-bit is required, that is the time to switch.
Another common question is whether it is better to use a saved query or to embed the SQL statement directly in a form's record source property. For most situations, there is no significant performance difference. Access will optimize and execute the SQL regardless. The decision really depends on organization and ease of maintenance. If you use the same query in multiple places, a saved query makes updating and documenting far simpler. If the SQL is only used in one place, embedding it in the record source property is fine. I often build and test queries in the query designer, then copy the SQL as needed into VBA or the form property. Saved queries are also easier to troubleshoot, since you can open them independently to test. The main consideration is dependencies: if multiple forms rely on a shared saved query and you later make a change, be mindful that this can affect other forms as well. In contrast, embedding SQL in one place keeps changes isolated but may mean duplicating logic. Ultimately, it comes down to your workflow and organizational needs rather than speed or efficiency.
The use of compact on close is another topic that generates debate. If you are the only user of the database, or if you are compacting your local front end in a split database design, turning on compact on close is acceptable. It reclaims space, cleans up some file bloat, and keeps the database tidy. However, never use compact on close for a shared back end accessed by multiple users. Compacting requires exclusive access; you risk locking others out or, in rare cases, database corruption. For shared databases, split them and give every user a copy of the front end. You can compact their front ends whenever you want, but usually I compact the developer version before pushing out updates. Automating compact and repair with scheduled scripts once a week is plenty for most databases. Compacting does not make a database inherently faster each time; it primarily reclaims space from deleted records and reorganizes the file. With large numbers of images in the database (which generally is not advised), you may need to compact more often. In short, use the feature when it makes sense for your environment, but avoid using it with shared files.
On the topic of database limits, some users worry about hitting the limit of 754 controls on a form. Access does not allow you to dynamically create or destroy controls at runtime while the form is open in form view. You can move, resize, show or hide controls, but controls themselves must exist before runtime. For example, if you want a dynamic kanban board with up to nine lists, design nine list controls but only show and resize the number you need. The 754 control limit applies to the total number of controls ever created over the form's lifetime in design view; that number accumulates as you add and remove controls. If you create or destroy controls at design time with VBA using CreateControl, you might hit that limit after intensive use. However, for simply hiding, showing, or reusing existing controls, you are not likely to encounter a problem, even with hundreds of modifications over the years. If you do get close, creating a new form removes the previous accumulated count. In my experience, I have never hit this limit in over 30 years of Access development.
A recent new feature in Access is form zooming. Some users have noticed that zooming does not yet work in ACCDE compiled files. Microsoft is aware of this bug and is working on a fix. New features like this often have a few initial quirks, but updates are frequent, showing that Access is still actively developed and supported.
Another performance tweak sometimes discussed is turning off name AutoCorrect. Name AutoCorrect records and manages object names and their relationships, which adds a bit of overhead to the database. In large and complex databases, you might see a slight speed increase with it off. More importantly, the feature is inconsistent: it updates some references automatically, like queries or forms, but does not touch VBA, embedded SQL in code, macros, or other references. This means you may gain a false sense of security and still have to check everything manually if you rename an object. If you rely on name AutoCorrect, it might save a step or two, especially on small projects, but on larger applications with significant VBA, it is safer to manage name changes directly.
Moving to database design, the 255 field-per-table limit is another hard barrier in Access. Some users, like those working with large survey data sets or mailings, eventually run into this barrier. Often the best solution is to normalize your data: rather than storing 300 metrics as 300 columns, store them as rows in a related table with a person ID, attribute name, and attribute value. This makes your design much more flexible and scalable. If you need to export a flat wide table for another application, you can always build a query to generate that shape for export purposes. Keeping your internal data normalized avoids running into limits and makes your application easier to maintain.
A frequent topic lately is the environmental impact of AI technology. Data centers for AI use significant amounts of electricity and water. These are valid and serious concerns, and companies and governments should implement standards and regulations to minimize environmental impact, invest in clean energy, and operate responsibly. Simply refusing to use AI or other new technology is not a practical solution. History shows that once a technology provides enough value, people adopt it. The focus should be on responsible use, improving efficiency, and putting in place the policies needed to make the new technology sustainable.
Personally, as someone who creates educational content, I use AI to research topics, organize ideas, check my work, and enhance my videos. AI is a tool that supports my work; it does not replace genuine teaching or content creation. Similarly, Microsoft Access developers can use AI to automate processes, analyze data, or generate content within their applications. As with every previous leap in technology, whether calculators, computers, or the internet, those who learn how to use new tools responsibly benefit the most. The evolution of technology is constant. The best approach is to learn, understand, and use these tools wisely, while also advocating for responsible development and use.
To directly connect this to Access: AI can already be integrated with Access for tasks like generating text, analyzing data, or automating business logic. The goal is to use it as a tool to make your applications smarter and more efficient, while keeping an eye on best practices and environmental responsibility.
In summary, the transition to 64-bit Access is mostly about future-proofing rather than immediate technical need. Saved queries versus SQL in record sources comes down to maintainability. Compact on close is great for single users but a risk for shared back ends. Name AutoCorrect can be turned off for better performance and consistency. Normalize your data model to avoid limits, and use AI as a tool, but be aware of its broader impacts. Technology is not going away; the challenge is to use it wisely and push for sustainability alongside innovation. If you have questions or want to learn more, there are forums and communities filled with knowledgeable users willing to help. The best way to improve your Access skills is to keep experimenting, keep learning, and ask questions as you go. |
