Quick Queries #57
By Richard Rost
7 months ago
Can Microsoft Access Really Handle a Million Records?
In this Microsoft Access tutorial I will answer common questions about Access, including whether it can handle a million records, if real businesses actually use Access, and what makes it a real database. I also respond to viewer questions about database splitting, storing images, updating subforms, migrating to SQL Server or Visual Studio, and improving the readability of your code. Additionally, I share tips on moving from Excel to Access, making better video thumbnails, and show off some fun moments with my dogs. This is part 57.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, million records, large data, split database, backend ACCDB, storage limits, images in database, attachments, concurrency, indexes, SQL Server migration, frontend for SQL Server, AutoNumber recovery, cascading combo boxes, subform requery, order of operations, appointment conflict, share database online, CBT exam database
Intro In this video, we'll talk about some of the most popular questions users have about Microsoft Access, including whether it can handle databases with over a million records, if real businesses rely on Access, and how to manage large files and images. We'll discuss best practices like splitting databases, storing attachments properly, improving performance with indexes, and key differences between Access and SQL Server. Other topics include restoring deleted AutoNumbers, updating subforms, cascading combo boxes, sharing Access over a network, and when it's time to move data from Excel to Access. We also cover scheduling tips and share a few personal stories along the way.Transcript Welcome to Quick Queries number 57, brought to you by accesslearningzone.com. I'm your instructor, Richard Rost.
What are Quick Queries? Well, I get emailed questions all the time and get comments on YouTube. I just put them all together in one video and give you my thoughts.
Today, you've got a couple of good ones. Can Access really handle a million records without crashing? Do real businesses use Microsoft Access? Is it a real database? The answers to these and a lot more - here we go!
Up to date, we got AJ by email. AJ says: I've been going back and forth with one of my co-workers about whether Microsoft Access can really handle large amounts of data. We've got about 10,000 customers, and each one of them might have dozens of orders. When you start doing the math between the customer table, the orders, and all the order detail records, we'd be looking at well over a million rows total.
My co-worker says there's no way Access can handle that much. He swears he heard from some real developers, quote unquote, that once you go past a million records Access is going to choke. Is that true, or can Access really deal with that kind of volume?
Well, this is one of the most common myths about Access, and it's flat-out wrong.
Yes, Access can absolutely handle a million records. In fact, I've got proof with my own personal database that runs my business now. Before I switched everything over to SQL Server a few years back, my order detail table alone had over 900,000 records in it. That's the table that matches the order ID with what the customer purchased. In my case, the core side, right? That's just the order detail table.
The order table on top of that, its parent, had three or four hundred thousand rows, and then customers, I have, what, 50-60,000 customers in my database. So yes, that's well over a million rows.
I've got all of my big tables split into their own back-end ACCDB files, and that particular order detail table, if memory serves, was like 40 or 50 megabytes. It was tiny. Remember, you've got a two-gigabyte file limit for each back-end file.
Now, my rows were lean. They only store the order ID, the core side ID, two numbers plus the table overhead. If you figure roughly 40-50 bytes per row, that means you could theoretically pack tens of millions of those rows into a single two-gigabyte database. You'll hit practical limits before then, but it shows you just how far Access can go if you keep your tables tight.
Now, the people who say that Access can't handle big data usually aren't splitting their databases properly or they're dumping huge files into them - you know, PDFs, images, attachments, all the things I say you don't store in your Access database. You're going to run out of space fast.
I had one customer that kept dumping images into his database and I had to show him how to do it properly.
Also, you can run out of space if you keep everything in a single back-end file or even in an unsplit database.
So, when one table starts to get pretty large, move it into its own back-end database file. Of course, store large files outside of Access. Then a million rows is nothing to worry about.
Now, there are real limits. Access isn't built for heavy multi-user concurrency, so if you get dozens of people constantly adding and editing records, Access by itself can have problems with that. SQL Server is a much better choice for that.
If you've got things like really complicated joins and no indexes, then yes, it's going to run slow. But the actual number of records isn't really the problem.
So, the short answer for your co-worker is yes, Access can handle a million records. I've done it myself. I've built databases for clients that had no problems with over a million records.
The key is to make sure your database is designed properly: keep your records lean, use indexes, split your database, maintain it with compact and repair regularly, make sure you have good backups, and if you do all those things - and your network speed is fast - you'll be surprised how well Access performs.
If you want to learn more about splitting your database, go watch this video. I'll put a link down below in the description under the video window.
This video will explain to you why you don't save images in your database and will show you how to use them instead. Basically, you want to store them in a file folder that your server has access to and then just save a link to where the file is. Watch this video for information on that.
Of course, the biggest culprit of all: attachments. Don't use them. They're bad. Unless you have a really teeny tiny database that's just for yourself, and even then, you're better off not using attachments. Keep all your files in a folder in or under your database folder. Do not put them in Access. Access isn't designed to store files inside of it - I don't know why they added this feature. Watch this video for more.
Next up, we got Samuel by email.
He said: My boss won't let me use Microsoft Access because he says it's not a real database. He keeps telling me that real companies don't use Access and that it's just a toy for hobbyists or maybe something mom and pop shops would use. What can I tell him to prove that Access is a real database that serious businesses actually use?
Well, I'm not going to name drop, but I have built database systems for companies that had thousands of employees. So yes, real companies do use it.
This is a giant misconception that's out there. First of all, Access is a real database system. It's a relational database management system. It's got tables, relationships, queries, SQL, forms, reports, and security features. By definition, that makes it a database system.
Just because it's easier to learn than SQL Server or Oracle or some other "real" databases doesn't make it any less real.
Now, Access was designed for a different purpose than those heavy-hitter enterprise platforms. SQL Server is built for thousands of concurrent users and terabytes of data. Access is meant for small to midsize teams who need something fast, flexible, affordable, and easy to work with. It shines in that environment.
Dismissing Access just because it's not designed to run Amazon or Walmart is like saying a pickup truck isn't a real vehicle because it can't haul what a cargo ship would haul.
Don't let anyone tell you that Access is only for hobbyists. Microsoft itself positions Access as a business solution. There are case studies in Microsoft's own tech community blog showing companies using Access to merge data from enterprise systems, run payroll calculations, and generate reports automatically. That's not toy work - that's real business logic driving efficiency. I like that word: efficiency.
It's true that many larger organizations just use Access as a front-end tool. Departments inside Fortune 500 companies will often build Access applications to sit on top of SQL Server because it's faster and cheaper to build front ends in Access than to wait six months for IT to spec out a custom web app. Access gives business units control while still plugging into enterprise data. Access is a fantastic front-end tool for SQL Server.
So, the answer for your boss is simple: Yes, Access is a real database. It's just optimized for a different role. If you're running a small or midsize company, then it's often the perfect solution by itself. But if you're in a bigger company, a huge enterprise, it can still serve as a front end while SQL Server carries the back end. Either way, Access is not a toy. It's a proven tool that still runs tens of thousands, if not more, of businesses worldwide.
I've got students in just about every country learning Access and building real tools with it. Just a quick Google search and I found a couple of lists of big companies that use Access, maybe not as their primary database, but as a front end to their big corporate system.
There are companies on this list like Pepsi, Red Hat, North Face, and lots of municipalities. Check them out - I'll put links down below you can click on.
Time to head over to YouTube.
James Weaver says: I stumbled upon your videos. I'm a senior SQL developer and needed a refresher on Access because I want to build a quick client database to track contacts and diary stuff for a small consulting business I'm starting. I think you are the best instructor I've ever encountered. I wish I had more like you in college. Keep up the good work.
I want to thank you, James, for your kind words. I love getting comments like this. I try to keep my videos entertaining as well as informative and educational, and I just want to let you know that your kind words are appreciated. Thank you.
I wish I had teachers like me in college. I might have actually stayed in college instead of dropping out and starting my own business, but I can't complain. I love what I do today.
Next up, Andrew Martin says: Thank you for this. He's talking about my Replace an AutoNumber video. I thought it was impossible to replace a deleted AutoNumber field. This sort of created major issues as my invoices and two jobs got deleted by mistake when a form became corrupted. I successfully managed to restore two deleted records using this. That's awesome.
I saw something online where it said to change the AutoNumber field to Number. This didn't work. Yeah, you can't do that. Once you change an AutoNumber to a regular number, you can do it once but you can't go back, and you're just stuck with it being a regular number.
That's the only use that I suggest for this technique that I show you - if you accidentally get a corruption or something is deleted that shouldn't have been deleted, you can use this technique to sneak that AutoNumber back on the table. But don't rely on it. This is a "in case of fire break glass" emergency, but it should fix that problem. I'm glad you got a solution. For everybody else who doesn't know what he's talking about, it's this video where I show you how to restore a deleted AutoNumber using an Append query.
HeLucas says on a main form he wants to get a subform that's based on a crosstab query to update to show new columns when new data is entered. It doesn't matter what data is in the subform, whether it's a crosstab query or any other kind of query. All you have to do is just requery that subform.
As far as when new data is entered, you can have that fire on any number of events: the After Insert event, the After Update event, or, just the easiest way, make yourself a Requery button.
This video should cover that. Basically, I show you how to put a start and end date here and it will requery the subform results based on your dates. You could just as easily put a button there that says, "OK, this data out here changed," click a button, and it will requery that. It's basically subformname.Form.Requery. I cover that in this video.
Is there a non-programming way to do it? I'm not sure, you're probably going to have to close and reopen the form, but I'd play with it.Let's figure that one out. I get so used to just - everything's a programming solution. I don't know. Sometimes when I make my videos, I try to, because I know how to do something with VBA, and then I try to see if I can make a version of this for the beginners too that doesn't involve programming. I try to have something for everybody.
But there's a lot of stuff you just can't do without one or two lines of code. This is literally one line of code.
deep-acc says: I am looking for VBA code to make cascading. I have one combo box field coming from a table and another coming from a query. Could you please check and help if possible?
Well, I don't offer consulting advice myself. You can post questions, and if it's a good question, I might make a video out of it. Or you can post it in the forums on my website.
But I have lots of videos on cascading combo boxes, including that series that you're watching right there. There are several other ones on my YouTube channel and on my website. Watch all of those.
You can post it in the forums on my website if you want more help. And if you want one-on-one assistance, I do have a list of developers available and tutors on my Access Developer Network page. You can check that out too if you want someone to actually help you with your problem.
I just make other videos.
Next up, rabbit follower is back. Rabbit follower used to post tons of comments that I missed you, rabbit follower, and it's good to see you back.
He says your poll - I just did a poll of people to see what everybody wants to have me make videos on. He says many users want to see SQL Server, but then so did my last poll. That is true.
It would take years to create a full course on SQL Server, just like it took you to make them for Access. That's true. So I'm not sure if it's feasible for you to do them at this point unless your students are patient.
Well, a lot of them are very patient.
As I said in your forum a while ago, you should have gone all in with SQL Server a long time ago, since SQL Server is the natural progression for any Access users. I switched to SQL Server in the early 2000s and switched to a web interface as the front end, with only select users using Access as the front end.
And then he talks about some of the guys commenting on Visual Studio.
But talking about the SQL Server thing, you are right that it took me decades to build out the full Access curriculum that I have now. An SQL Server series would be a big project too. But just because it takes a lot of time doesn't mean that I shouldn't do it. If I had thought that way when I started Access, I would have never made those lessons either. Back then, there were already plenty of other Access tutorials available online. Same thing today with Word, Excel, and everything else.
But I hope that people watch my videos because they like my style of teaching. So, I am planning on still doing SQL Server, but I plan on doing it my way. And it's probably not going to just be an SQL Server from scratch for people who just want to learn SQL Server. My focus is going to be on helping Access users who are outgrowing Access to migrate to SQL Server and use it effectively as a backend. That's a niche I think really needs filling.
And then maybe from there, moving into some web-based tools. I personally program everything in ASP, but I know a lot of people want something a little more graphical.
Now, I know there are only so many hours in the day, but that's never stopped me from pushing forward before. I try to stay positive. I keep building one video at a time. I wouldn't have thought five years ago that I'd have 1100 TechHelp videos online by now. But persistence is key, so you just do the best you can every day.
Does that mean I'm going to give up and not do SQL Server? Nope. I'm still going to do it when I get some time.
Access is still my forte. I'm always going to keep making Access videos. There's still so much that I haven't covered yet even with Access by itself. But I do plan on adding some SQL Server lessons in there as well. It's coming. They just need to add that 25th hour to the day.
And you mentioned Visual Studio. My good friend and colleague Alex Hedley did make a couple of VB.NET tutorials. They're on my website - you can find them right here. I'll put a link down below, and he covers all the basics for those of you that want to get started.
I'm probably going to eventually do some Visual Studio training of my own just to put my own stamp on it, but these are really good tutorials so check them out.
Another rabbit follower comment, we got two in one day. This is amazing. He says if you want people to know your videos aren't just for Fitness, but also topics that any user might find useful, then you may want to make your thumbnails show those topics. For instance, under Fitness Database 1 you might write table design, fields, data types, etc. Thumbnails could make or break a video, similar to movie posters in the pre-internet, pre-VHS era. You're that old, man. I'm that old too.
That could affect the movie's success. If you were the only "Fitness" in the thumbnail, they may not think it's for them.
You are absolutely 100 percent correct. I've been thinking about doing that for a while with the Quick Queries. As you can see with today's episode, I made a slight change to the header. I started putting some of the top questions here.
I've been thinking about that too ever since around Fitness Level 10. I was like, wait a minute, people are gonna keep seeing "Fitness," so I'm probably going to make that change. Your comment just spurred me on, so yeah, probably the next level I'm going to make a few adjustments to the thumbnail.
So, I absolutely do appreciate your comments, and please keep them coming. Again, it's good to see you.
Next up, dingus of baddest in Fitness 33. I mentioned about brackets and parentheses and stuff.
Essentially, you see a lot of these things online - these memes people post with these crazy, can you figure out this math equation? I posted about it in my Captain's Log a few weeks ago and wrote a little article about it. Basically, you see stuff like this right here.
The rules of operation say that multiplication and division go first together left to right. So, you do the three divided by one first, and then the two times two, and then you do the addition last.
But the problem is, there are a bunch of weird ones on here that I found online - that's factorial, right? But the problem is, and then there are some where there isn't even a right answer.
Sometimes you get stuff that's written like this thing here. That's an equation that's designed to trick the child to teach them order of operations. But in the video, I emphasize that I like to write my code with extra parentheses, even if they don't apply there, even if you don't need them.
Like if you have two plus five times six, the five times six goes first because of the order of operations, but I put parentheses around them in my code just to make it clearer to whoever might be reading my code in the future, including myself.
So, don't exclude the parentheses just because you don't need them. I put them there too.
That's what dingus of baddest was saying, it makes it more readable. People are all consumed with making their code tiny and compact and pretty, but it's not really adding much efficiency to the way your code executes. I like writing out long If Then blocks. If this, then do this, else do that.
Could that all be condensed into an IIf function? Yeah, maybe. But that's nowhere near as easy to read. I prefer my code to be easy to read. The compiler is going to do the same thing with it. It's going to run just the same speed.
Make your code readable. You're not going to win an academy award for most pristine code. Whatever award that applies to that - I don't know, are there programming awards now? I got to Google it, and I hate not knowing something. I had to ask ChatGBT. I forgot, there is the Turing Award. I knew about that one, but that's more about general computing than just programming.
But, yeah, okay. All right. I guess, oh, my best, most available professional. I'm in that. Yes, awesome.
Next up, once in a while I get a comment like this guy, Algo or whatever - I don't know how you pronounce it. He says, look, I would really appreciate if you would not show us a pink page. Much rather you show us normal Access.
Is this what you want? You want normal Access? Or is that page still too pink for you? What do you think?
Next up, Renee says he concurs at moving data from Excel to Access when it gets too big. He's got two thousand rows of data, 500 columns. Yeah, that's a good sign. That's about when it's ready to move over to Access. Me personally, I've got some pretty big spreadsheets, but I think if it's got more than a couple hundred rows, if it usually for me it's more than fits on one screen, maybe a little bit of scrolling either left and right or up and down, that's about when it's time for me to say, all right, time to put your big boy pants on and move over to Access.
See, 400,000 records zips along. That's no problem. Easily can handle a million, easily.
Oops, wrong button. Ha.
Next up, Sanny Muhammad says he's got 11 different databases that all share a common backend. Databases from your videos, splitting the databases is no longer a problem. The challenge is how to share the front end on the local network and online. Well, the local network you said you already got working by splitting it on your local network, your wired network. Online can be a challenge.
It's because most of the databases are meant for use by different offices. Sometimes staff prefer to go home and complete the work from home. I totally get it.
That raises the need for using SQL Server. Yes, definitely. You could definitely use SQL Server for that. You get yourself some online SQL Server hosting, whether it's through Azure or through a company like Winhost that I recommend. I've got a whole video and a page on my website that explains all the different options. There's SharePoint, there's Access Database Cloud, there's Chromamode Desktop, there's SQL Server.
This page will take you through all the different options, the easiest ways to get your Access database online so other people can use it.
As far as your second question goes, the CBT exam database, that's definitely something you could set up in Access. I'll put it on my list and possibly put something together in the future.
Lots of ideas that people give me for different databases. Obviously, building something like this local in just Access would be relatively easy to do, if this is something where you want them to log in like in a classroom so that they can't cheat. But doing it online, again, you'd need SQL Server and some kind of web-based solution, but it's definitely possible, sure. Pretty much anything is possible. It's just a question of how much time and work do you want to put into it.
And live long and prosper to you too, sir.
Oh, and I should also mention if anyone else watching is also interested in seeing some kind of a CBT exam database for doing stuff like that, post a comment down below. The more people that say they're interested in something, the higher up I bump it on the list. Just wanted you people to know. All right.Yeah, it's on the list. But if I get, like, 30 or 40 people saying "yeah, good idea," then that gets pushed up the list.
Next up, d'Ardiv says, "Could you make a tutorial? How to display not only scheduled events and appointments in a form or report, but also the free time slots in between, based on an employee's working hours? It could be very useful to see both the busy times and the available times for scheduling."
Yeah, that's definitely possible. I don't have a TechHelp video, I don't think, that does that. Let me see. Well, now see, I've got so many videos, even I forget. I have to Google search my own site sometimes to see if I've covered something.
But it looks like I did cover this in an extended cut. So, I've got an appointment database TechHelp video—this is the free part, and it's four years old. That's why I didn't remember it.
But in the Gold Members extended cut, we do have appointment conflict resolution. It will tell you when the next available open time slot is. And if you try to put in an appointment in a time slot where there's already an appointment, it'll alert you.
So yes, I've already covered this. It's right here. You can sign up to be a Gold Member using that blue Join button down below. I also did something similar in my Access Developer 24 class. We built a reservation system. This one's based on reserving—I think it's reserving a hotel room or something like that—similar to what you're talking about.
But you can check for conflicts. Someone wants to reserve a room on this date—oh, someone's already got the room, that kind of thing. So, this course will check for that as well. I'll put links to all this stuff down below.
And finally tonight, we got Shadow Dragon, one of my favorite commenters. Thanks for another great Quick Queries. Cat videos—I mentioned cat videos. I don't make millions on YouTube because I'm not making stupid cat videos, is what I said in the last one. Shadow Dragon says maybe you should add pictures or short vids of your puppies.
That's a great idea. Here they are going for a car ride. They love car rides. That's Carter and Cooper, and that's me—and yes, we're stopped at a stop sign or a light. I don't take pictures while I'm driving.
Here they are both in the crate now. They were both crate trained as baby puppies, but we haven't needed the crate for them because they're both four and five years old now. But my wife likes to foster, so every now and then she'll bring home a foster puppy for a week or so. And these guys just still love getting in the crate.
It used to be a threat, like if they were misbehaving: "You want to get in your crate?" And they just go and lay in it now, like, "Oh, I'm just gonna go chill in the crate." So they love their crates.
Here they are taking up the whole bed. And if you need to go to the bathroom, better close the door because they're going to come in there and lay there and watch you.
There's never a need to knock or ring the doorbell—they know you're here. I just love this picture. It's just one of my favorites.
"Excuse me sir, it's 5:01 p.m. and we have not had dinner yet." And this is my favorite picture of Carter. He's my older dog, he's my first dog. When I met my wife, she had a dog already—a black lab—and I've always loved black labs, even since I was a kid. My uncle had one when I was growing up. I loved that dog.
So Carter is my first dog, and he's just a beautiful boy. I'm very proud of him. And of course, I love Cooper too. He's our younger dog. Carter was bored by himself, so Carter needed a puppy. Cooper is technically Carter's puppy.
We call him Tutoy Coop because he always figures out a way to get two toys in his mouth. He's got a tennis ball and his ring toy.
They do have superpowers—they're both laser-eyed, so we've got to watch out for that. They do love to swim. They swim with penguins. No—this is actually, I gave the AI—I gave ChatGPT a picture of my dogs in the pool swimming and it came up with this. It's a beautiful picture. I have it as the backdrop of one of my laptops.
My wife and I have a thing for penguins, so I said put penguins in the pool and put penguins in the background. It was really cool. AI is getting scary good at making pictures like this.
But yeah, that's my puppies. I love my puppies, very proud of them. Occasionally, if you listen closely, you'll hear them bark in the back of a video once in a while—especially if someone comes to the door when I'm not expecting it.
I'll be honest, I record my videos in short takes. I punch in and punch out instead of going back and trying to edit and stuff. If it's a really good take, especially if it's one of my TechHelp videos or if it's one of my advanced developer videos, I don't really care if there's a bark in the background.
It's like when someone beams in. You hear me say, "Oh, someone's beaming in," and that's my half-hourly chime on my server computer. I think it's cool. I like background noises. I'm not trying to record in a sterile environment like I did when I first started doing this.
Anyways, that's going to do it for Quick Queries 57. Let me know what you think of the new thumbnail here. Like I said, Rabbit Follower's comment kind of drove me to be like, yeah, you're right. I should probably put a little more on the thumbnail. I've been using the same Quick Query thumbnail, I think, since episode one.
So, there's episode one, and actually, yeah, back in episode one, I actually did put some of the topics on here. Then it evolved to this, and then I started getting colorful just to differentiate them a little bit more.
This is last week's. And now we're up to this one. Who knows what we'll be on by Quick Queries 100? I don't know.
The reason why is because sometimes it's easier—I just do that. I start with just the title slide, then I just go into the questions without even looking at them. But what I did today was I actually figured out what the first two questions were going to be, and then I slapped those on there. I figured I'd get the headline question or two—the big ones—and then I'll make a title slide, then I'll record the title slide, and then re-number it.
Anyways, that's going to do it for Quick Queries number 57. I hope you learned something.
Live long and prosper, my friends. I will see you next time.
TOPICS:
Access handling large data volumes over a million records
Splitting Access databases into multiple backend files
Managing file size limits in Access databases
Storing large files such as images and attachments outside Access
Transferring images and files via links instead of attachments
Impact of database design on Access performance
Using indexes to improve queries on large tables
Compact and repair operations for Access maintenance
Backing up Access databases regularly
Differences between Access and SQL Server for concurrent users
Access as a frontend to SQL Server in enterprise environments
Proof that real companies use Microsoft Access
Role of Access in small to midsize businesses
How to requery a subform to display updated crosstab query data
Adding a Requery button to refresh subform data
Cascading combo boxes using Access and VBA
Migrating Access databases to SQL Server for online access
Options for hosting Access databases online
Writing readable and maintainable VBA code using parentheses
When to migrate from Excel to Access due to data size
Sharing Access frontends over a local network
Checking for appointment conflicts and finding available time slots
COMMERCIAL:
In today's video, we're discussing some of the most common questions people have about Microsoft Access, like whether Access can really handle over a million records without crashing and if it's actually used by real businesses. We'll talk about best practices for handling large databases, tips for working with images and attachments, and the differences between Access and SQL Server. You'll also hear answers to questions about restoring deleted AutoNumbers, updating subforms based on crosstab queries, cascading combo boxes, and sharing your Access front end over a network or online. Plus, I'll share advice on moving from Excel to Access, scheduling appointments, and even a few funny moments with my dogs. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. Which of the following statements about Microsoft Access and large datasets is correct?
A. Access can handle over a million records if properly designed and maintained.
B. Access cannot handle more than 100,000 records under any circumstances.
C. Access is only suitable for databases with less than 10,000 records.
D. Access can only handle large datasets if you use embedded images and attachments.
Q2. What is a common mistake that causes Access databases to reach their size limits quickly?
A. Storing large files such as images and attachments inside the database.
B. Adding too many indexed fields in tables.
C. Keeping multiple front-end files for different users.
D. Splitting the database into back-end files.
Q3. What strategy helps Access efficiently handle tables with a very large number of rows?
A. Splitting large tables into their own back-end ACCDB files.
B. Storing all related data in one unsplit database file.
C. Disabling all indexes on large tables.
D. Compacting the database without separating data.
Q4. When dealing with lots of users needing to edit and add records simultaneously, what should you consider?
A. Upgrade to a system like SQL Server for better concurrency.
B. Store more data in attachments to speed up Access.
C. Only use one shared Access front-end on a remote server.
D. Increase the file size limit using third-party plugins.
Q5. Why is it not recommended to store images and large files directly inside an Access database?
A. It rapidly uses up the database's size limit and can slow performance.
B. It increases security and makes backups easier.
C. Storing images in Access is more efficient than using file folders.
D. Access is optimized for handling multimedia files in tables.
Q6. What is the purpose of splitting an Access database?
A. To separate data storage (back-end) from the user interface and logic (front-end).
B. To convert all tables to forms.
C. To combine multiple databases into a single file.
D. To remove all indexes from tables.
Q7. What makes Microsoft Access a "real" database, according to the instructor?
A. It has tables, relationships, queries, SQL, forms, reports, and security features.
B. It is less expensive than Oracle and SQL Server.
C. It is only used by small businesses and hobbyists.
D. It works offline with no internet connectivity.
Q8. What is a common use case for Access in large organizations?
A. Serving as a front end to SQL Server databases.
B. Replacing all enterprise databases with Access alone.
C. Running the entire company's global operations.
D. Managing only static, unchanging data.
Q9. If you need your Access database to be accessible by users from home and multiple offices, what is the best approach?
A. Migrate to SQL Server with an online hosting solution.
B. Store Access files on each user's local computer.
C. Use Access web apps without any external database.
D. Share one unsplit Access file using email attachments.
Q10. What should you do if you need to update a subform based on a crosstab query in Access after entering new data?
A. Requery the subform to refresh its contents.
B. Delete and recreate the subform each time.
C. Switch the query type to a parameter query for updates.
D. Add a new table to the database.
Q11. Why does the instructor recommend writing code with extra parentheses, even if not technically required?
A. To improve code readability for yourself and others.
B. To make the code execute faster.
C. Because Access does not follow standard order of operations.
D. To reduce the number of lines in your code.
Q12. If you need help with complex features such as cascading combo boxes in Access, what options are suggested?
A. Watch existing video tutorials or post in the website forum.
B. Only rely on the Access help file.
C. Call Microsoft support for free detailed consulting.
D. Hire a non-Access expert.
Q13. What was cited as an advantage of building Access front ends even in enterprise environments?
A. They are faster and cheaper to build compared to custom web apps.
B. They require advanced SQL Server knowledge.
C. They cannot connect to corporate systems.
D. Microsoft Access does not support any form of automation.
Q14. How can you easily let Access users refresh data displayed on a subform without programming?
A. Add a button that performs a requery of the subform.
B. Automatically restart Access application.
C. Remove the subform entirely from the main form.
D. Create a new front-end ACCDB file for each refresh.
Q15. According to the instructor, when is it time to consider moving Excel data to Access?
A. When your spreadsheet is too large to view easily on one screen.
B. When there are fewer than 10 rows of data.
C. As soon as you have any formulas in your spreadsheet.
D. Only if using Access 2003 or earlier.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
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 answering some great questions that many Access users run into. My name is Richard Rost, and I appreciate you sending in questions and comments from YouTube and email. In these Quick Queries episodes, I gather the questions I receive and share my answers and insights to help everyone learn.
One of the top questions today is whether Microsoft Access can truly handle a million records without issues. I hear this concern a lot. People often repeat a myth that Access will fail or "choke" once you hit a million records, but that is simply not true. Before I migrated my own business database to SQL Server, I had a single order detail table with over 900,000 records, and a parent order table with hundreds of thousands more. Add in tens of thousands of customers, and I was well past the million-record mark. Access handled it without a problem. The key is designing your database efficiently: keep your records lean, store only essential data in your tables, and split your large tables into separate back-end files. Remember, Access has a two-gigabyte file size limit for each ACCDB file, but you can store millions of rows as long as you are not bloating the database with things like images or attachments. Files like PDFs and photos should always be kept outside the database, with just a file path stored in the table.
Where Access does have limitations is when it comes to heavy multi-user activity. If you have dozens of users all working concurrently and editing records, that is where SQL Server becomes a better fit. Performance can also suffer from poorly indexed tables or overly complex joins, but the sheer number of records is rarely the issue.
If you want a straightforward answer for your skeptical co-worker, tell them that yes, Access can definitely handle a million records if you follow good database design practices. Use indexes, split your database, maintain backups, and compact and repair regularly. Whether you are running a small office or a growing business, Access is much more powerful than many give it credit for.
Another common topic is whether real businesses actually use Microsoft Access. The misconception is that Access is not a "real" database and is more of a toy for hobbyists or tiny shops. This is not the case. Microsoft Access is a bona fide relational database management system, complete with tables, relational links, queries, forms, and security. It is optimized for quick, flexible business applications for small to midsize groups. While companies running massive operations like Amazon will rely on enterprise databases like SQL Server or Oracle, many Fortune 500 companies use Access in their departments – often as a convenient and fast front end for corporate databases. Access shines when teams need agility and control. I have personally built systems for large organizations with thousands of employees using Access. Microsoft itself, as well as many case studies online, confirm that Access is used in real-world business applications every day.
A common workflow is to use Access as a user-friendly interface while the data is stored centrally in SQL Server. This hybrid approach lets businesses get the best of both worlds: the development speed and flexibility of Access, and the performance and scalability of SQL Server. So if your boss claims that no real business uses Access, you can confidently respond with real examples and point out that the tool is still widely used and perfectly suited for many business scenarios.
I also get plenty of positive feedback from experienced users, like James, who dropped by to say they found my tutorials helpful even after years as a SQL developer. I always appreciate these kind words and enjoy knowing that these lessons help users of all experience levels.
Sometimes viewers ask about more specialized topics. For instance, someone recently needed to restore a deleted AutoNumber field and found my video on how to do this helpful. While this is a rare emergency fix, it is sometimes necessary after accidental data loss or corruption. This technique should be reserved for those rare "break glass in case of emergency" moments, not as a common practice.
I received a question about having a subform (especially one based on a crosstab query) update automatically when new data is entered. The best solution here is to requery the subform after relevant data changes. You can trigger a requery after various form events, or simply offer a Requery button to the user. While I always look for solutions that do not require code for beginners, sometimes a single line of VBA is the most practical answer.
Cascading combo boxes are also a frequent area of interest. Although I do not offer consulting services, I have a lot of videos covering how to build cascading combos – combinations where the selection in one box affects the choices available in another. If you need more interactive help, you can post in the forums on my website or reach out to tutors in my Access Developer Network.
Questions about future courses also pop up. For those interested in SQL Server, I am planning lessons on how to transition from Access to SQL Server back ends, with a focus on helping Access power users take the next step. Building out a full SQL Server curriculum will take time, but it is an essential path for many Access users who start to outgrow Access' limitations, especially regarding online and multi-user scenarios. Visual Studio training has also been launched on my website, courtesy of Alex Hedley.
Feedback is essential for improvement, and I appreciate observations about making video thumbnails more descriptive to reach a wider audience. Changing the way thumbnails highlight the main topics will help viewers more quickly spot videos that might be relevant beyond just the example subject matter.
I also address the subject of code readability. Using extra parentheses, even when not strictly necessary, can make code much clearer for others who might read it later. It is better to produce clear, well-structured code than try to condense everything into a single line at the expense of readability. Most of the time, small efficiencies in code length and compactness do not matter nearly as much as being understandable to future readers, including yourself.
Other technical questions come up often, like the best time to move from Excel to Access. If you are working with thousands of rows and hundreds of columns, that's an indicator it is time to switch to a proper database system. Access can handle large volumes – I have worked with tables containing 400,000 records or more without trouble.
Another viewer asked about making an Access front end available not just on a local network but also online. While sharing via a company network is straightforward with a split front end and back end, getting Access working in multiple offices or remotely does pose more challenges. Using a cloud-hosted SQL Server, which can be accessed from virtually anywhere, is an excellent solution. I have resources on my website explaining options like Azure SQL Server hosting, SharePoint, and more for making Access available outside the office.
From time to time, viewers propose ideas for future tutorials, such as a CBT exam database or tools for scheduling that display available and busy slots. When enough interest builds for a particular request, I give it higher priority on my list. For the scheduling and free time slots example, I have actually covered conflict resolution and finding open time slots in the Extended Cut of my appointments video series, as well as in my Access Developer class. If you are interested, check out those resources for step-by-step solutions.
And just to end on a lighter note, viewers sometimes comment on the dogs or other personal touches that appear in my videos. My pups Carter and Cooper enjoy making cameo appearances. I record in short takes, and if a dog barks or there's a little background noise, I usually leave it in. After all, I want my content to feel authentic rather than overly polished.
That wraps up today's Quick Queries. I hope these answers help clarify some common questions about Microsoft Access and building real-world database solutions. For a complete step-by-step walkthrough of all these topics, check out the full video on my website at the link below.
Live long and prosper, my friends.Topic List Access handling large data volumes over a million records
Splitting Access databases into multiple backend files
Managing file size limits in Access databases
Storing large files such as images and attachments outside Access
Transferring images and files via links instead of attachments
Impact of database design on Access performance
Using indexes to improve queries on large tables
Compact and repair operations for Access maintenance
Backing up Access databases regularly
Differences between Access and SQL Server for concurrent users
Access as a frontend to SQL Server in enterprise environments
Proof that real companies use Microsoft Access
Role of Access in small to midsize businesses
How to requery a subform to display updated crosstab query data
Adding a Requery button to refresh subform data
Cascading combo boxes using Access and VBA
Migrating Access databases to SQL Server for online access
Options for hosting Access databases online
Writing readable and maintainable VBA code using parentheses
When to migrate from Excel to Access due to data size
Sharing Access frontends over a local network
Checking for appointment conflicts and finding available time slots |
