Quick Queries #10
By Richard Rost
3 years ago
DB in Use, Max Records, Criteria for Data Types
Welcome to another Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- Why I Repeat Stuff
- Database is in Use on Compact
- Tracking Salespeople for Outlets
- Max Number of Records in a Table
- Expand/Collapse SubDatasheets
- Query Builder in the Form
- Limit on Linked Backend Tables
- Lookup Previous Record's Value
- MySQL Data Limits
- Replicate Autonumbers
- DLookup Criteria for Different Data Types
- Split Database Share Names
- Show Field Value and Not ID
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq
Intro In this video, I answer a variety of Microsoft Access questions from viewers, including topics like why I use spaced repetition in my lessons, issues with compacting databases stored in synced folders like Google Drive, how to organize outlet and sales person tables for business databases, the maximum table size and record limits in Access, keyboard shortcuts for subdatasheets, managing multiple backend files, using DLookup and DMax for criteria-based lookups, split database connection issues, and displaying related table values in reports. This video is Quick Queries 10.Transcript Welcome to another TechHelp Quick Queries video. That's number 10. I can't believe I've done it. Ten episodes already. Wow!
This is where I answer questions emailed to me, or posted on my YouTube channel, or in the forums on my website. I try to answer your questions that may or may not need videos on their own. That's what I do here, so check it out. Here we go. Ready?
I posted a little article on my website on 4/20. 4/20. That's my dad's birthday, by the way. The article was about why I repeat stuff in my lessons. This was actually a posting of a video that I saw on TikTok. It is a video on spaced repetition, and it's basically about how the more that you are exposed to something, the better your brain is at retaining it. Right after you learn something, you start forgetting it, so you have to be exposed to it again. If you keep getting exposed to the same or similar material in a relatively short amount of time, your brain retains it better.
It's like pathways walking across the lawn. The more that you walk across a particular patch of lawn, the more entrenched that path becomes. There was a story somewhere I read about a college campus that had a big lawn and the students would cut across it. They wanted to know where to put the sidewalks. One guy said, let's wait until the end of the semester and see where the paths are. That's where we'll put sidewalks. That's a great story, but that's how your brain works, too. The more certain memories are entrenched in your neurons, and I'm not a neuroscientist, but that's from the reading that I've done. That's what I understand.
Anyway, I got a lot of comments on this email, post, whatever, and a lot of you were like, yeah, that's awesome, that's how I like to learn. A lot of you don't. I'm curious to hear from more of you in the comments. Tell me what you think. Do you like my method?
This is what I told Johnny here: I like to do it where I'll show you something and then I'll put a little spin on it. I'll put a little twist on it. Like lesson one might be DLookup with looking up an ID. Then I'll show you how to do the same thing, but you're looking up a date, or you're looking up a string value, because those have to be handled differently. Then I'll show you how to do it with two criteria, or an inequality. Then we'll add the NZ function. So it's building on the previous one, and I like to spin a little bit. But sometimes, yeah, sometimes just repetition.
For beginners, especially building a form, I know in my beginner series, I show you three or four different times how to do the same stuff with building forms. I try to add a couple little tricks here and there, but it's mostly the same stuff. And you get it because you do it multiple times.
I've been doing this stuff for almost 30 years, so I've done it a million times. I know that for me, a lot of the times, it didn't stick until I'd seen it two or three or four times. Made the same mistakes, in fact. Then I went back and I recall it. I'm like, oh yeah. I did figure this out two years ago and forgot it. So if you don't see it, you forget it. That's how I think, that's how I teach.
This is an email that I got from one of my long-time students, DanZ. He kept saying that he's getting "database in use" when trying to compact the database, but he's the only user. His computer isn't on a network, and he's the only one. He shut down and restarted, all that stuff. He would go into his database and then do a compact and repair, and it would always say "database is in use."
After several emails back and forth, we determined that the problem was that he's got that database being saved in Google Drive Sync, which is awesome. I use it myself, the Google backup, which comes with Google Drive on your PC. Basically, you could take your entire documents folder, everything, and it'll get saved up in the cloud in your Google Drive. So you can access from any machine, if you're on your laptop, if you're traveling, whatever.
But the problem is that Google is constantly trying to back up the files in that folder. That's one of the reasons why I say, don't run your Access database out of online storage, even something that's like a synced folder that's being backed up. Access needs exclusive rights to that file, and if you're using Google Drive and it's trying to back it up, Access is going to say, I can't do it, the file's already open.
You can pause Google's syncing down in your system tray. There's an icon, you can right-click on it, you can pause Google. But that's a pain every time you want to compact. What I recommend is keep your Access database in a folder that is not automatically synced to Google Drive, Dropbox, OneDrive, or whatever else you're using. Then have a backup routine inside of Access. You can do it manually, or you can do it whenever you close your database, or you can do it with a timed interval, like at 2am. Have your database backed up to a folder that is backed up to Google Drive. That's what I do. I think at 4am every morning it takes the database, copies it over to a Google Sync folder, and that gets copied up to the cloud. I do it several times throughout the day too, manually.
I'll put a link to my backup video down below. Of course, if you're having any kind of wacky problems whatsoever, always check my troubleshooter. I wouldn't have even thought of this, but he emailed me a couple times, we went back and forth, and in my mind, I just start to mentally go down this: backup your database, restart Access, and the first one of the questions I asked him, after I thought about it, was, are you using any online storage? Are you running this over Google Drive or Dropbox? At first he said no, but then we figured out that he is running it in a local folder on his C drive, but that folder is being synced up to Google Drive and that's causing the problem.
Here's a question from one of my students on my website, Ludwig. He says, we're a company that sells kitchens. We have 45 independent outlets that sell the kitchens for us, but those outlets have their own sales people and work planners. Now I've got a point of sale table, and an orders table. My question is, where do I put the sales people and the work planners as fields in a table? I would say the point of sale team, but each independent point of sale has its own sales people. I'm stuck here.
Basically, your tables should each contain the same kind of stuff. Sales people are sales people, regardless of who they work for. So you're going to have a sales person table that has all of the sales people in it. Now, if you want to track where those sales people work, then you would have a table to track the outlets. Maybe your outlet table, and that outlet would have its own ID. Then in your work planner, in your sales people table, you would then give them an outlet ID. If they work for multiple outlets, like if the same sales person works for three or four outlets, then that has to be a many-to-many relationship. But if they only work at one outlet, then that's a simple, regular, normal relationship.
I would have an outlet table, a sales person table, where you could track each one of those. The point of sale table, you can also track who the outlet is, or who the sales person is in the order. That's up to you. If the sales person only works for one particular outlet, then you'd only need to store the sales person. But if they can work for multiple outlets, it gets a little trickier. But it can't hurt you to save both. You could save the outlet and the sales person in the point of sale table.
Again, this is why it's beautiful to build your own database, because you're not stuck with someone else's software. You can change it and make it do exactly what you want it to do. That's how I would do it. I'd have an outlet table and a sales person table. The relationship there depends on the work relationship, and then your order table could then store all the related information.
I'm not exactly sure what your point of sale is. Is that a physical location? So you have outlets, workers, point of sale, and orders. What exactly is a point of sale? I would need more information to really help you with this one, but do what I do in my association database. Go watch that. I spend a lot of time laying all the different tables and the fields and stuff out. In fact, the whole part one is just me going over the different tables and fields and determining what I need, laying it out, and setting it up. Go watch this again if you haven't watched it already.
If you need more help, post a follow-up in the forums and show me some screenshots of what you've got so far. Sometimes it's easier to help someone if I can see what they've got going on. I don't take file attachments, so don't send me your database, but you can post screenshots of your forms and your tables and all your relationships and stuff right in the forums.
Here's another question from the forums. One of my students, Richard, said, is there a limit to how many records you can have in one table? Well, two gigabytes is the maximum size of a database file. So if you had just one table in the database, that would do it.
Then I went and decided, lately I've been playing a lot with the AI bots, so I asked ChatGPT, Bing, and Bard the same question. ChatGPT said in Access 2019, the maximum number of records you can have is two billion. I guess they have to be very tiny records. Bing says, according to Microsoft Support, there's no specific limit on the number of records, but the maximum file size is two gigabytes. That's a better answer. I like that. The maximum size for any individual table is one gigabyte. I wasn't aware of that limitation.
If we go to Microsoft's website, right on Microsoft.com, maximum size for an Access database is two gigabytes minus the space for system objects. If we scroll down to tables, it says table size, two gigabytes minus the space needed for the system objects. So Bing, you didn't read Microsoft's website correctly. I guess I haven't tested this myself, but I'm curious to see. Maybe I make a supplemental video and run through it and see what the max size I can make a table is before it crashes.
It says the maximum size of a single record within a table is two gigabytes, excluding memo fields, long text fields, and OLE objects, which we don't use. If all the records are maxed out, a table could hold one gigabyte divided by two kilobytes, 524,000 records. I really want to test this, but I don't have time for it today, maybe in a future video.
Bard then comes along and says maximum number of records is two gigabytes, not technically correct, but okay, minus the space and the system objects. You can work around this, and it mentions linking multiple database files, each of which can be two gigabytes. That's a pretty good answer. I don't mind that answer.
I then gave my thoughts on realistic values for your table. I have an order detail table that at the time had 800,000 records in it, and the file size is only 38 megs, because it's a matter of how big each record is, it's not just the maximum number of records.
Then Kevin Yep, contributor, says the maximum record count is about two billion because the record count property stores as a long integer, which makes perfect sense. It sounds reasonable. Again, I might run through and test some of this stuff a little bit later on, but in a realistic database, you're never going to hit these limits unless you've got tons and tons of data, because you can split between multiple tables and all that stuff. If you really have that much data, that's about the time you want to consider moving your data up to a database server like SQL Server. The average small business database, you're never going to run into these limits. I have built hundreds of databases for small companies, and not a single one of them has ever had a problem unless they were doing something silly.
Here's a question from Joan, one of my students. We're talking about the subdatasheets in Access. You get a little plus and minus and you can open it up to show the related tables. Joan wanted to know how to open and close those with the keyboard. I didn't even know how to do it myself. I had to Google it, but it's Control Shift Down and Control Shift Up.
Here I am in the order table, Control Shift Down and Control Shift Up. Look at that. Down. I'll never remember this. There are so many keyboard shortcuts, but I'm just not going to make a separate shortcut video out of this. Like I sometimes do, that's a pretty cool trick. If you use it, I never use subdatasheets. I think I mentioned this in my subdatasheets video. I don't use it. I really don't. It's an okay tool for you, the developer, but I never give access to my end users to this. No, but if you are the kind of person that uses them and you like that keyboard shortcut trick, there you go.
Gary asks, when I'm working on a form or report and I want to change the record source, why don't I click the dot dot dot to expand the query builder right in the form? You're working on a form and you want to make changes, you want to base it on a query, whatever, you come over here, you hit dot dot dot. Now right now, it says you invoke the query builder on a table. Do you want to create a query based on the table? I can say yes, it'll come in here, I can make whatever changes I want now, this and this and whatever. Now when you save this, it slaps the SQL right in here, instead of creating a whole new query over here for it. If I zoom in, Shift F2, there's the query that I just basically created in the query builder.
Generally, the reason why I don't use this is because if I'm going to make a form off a query, which is rare, I want to make a query for it. Nine times out of ten, I try to build my forms directly off of tables because I want the data in the form to be based on one table. If I want some lookup fields, I'll put a DLookup on the form, for example. I almost always build my forms off of tables. If I do build a query that I'm going to use for the form, chances are I'm going to use that query in multiple forms or in a form and a report. I want the query so that I can use that, and if I make a change to the query, it's also going to update the other places that it's used. I almost never want an SQL statement here, unless it's an SQL statement that I'm manipulating in my VB code. I do that a lot, like I'll have a lot of list forms like this, not this one, but like this guy, where I'll have filtering or sorting up here, and this form is controlling that record source. In that case, I still have this based on a table because it loads faster.
There are lots of reasons why, but that's why you almost never see me use it, because I just don't. Simple forms I have based on a straight table, and if I am going to make a query for it, I'm going to make a query for it, and then I'm going to use that query in multiple places. So that's why.
Icras says, reaching data limit, is making multiple backends a good idea or not? Sure, I got a lot of them. I have most of my secure data stored in SQL Server, obviously, but I still use Access tables for a lot of stuff like contacts, emails, and lots of things that are less secure. You can have 20 backend files if you want, and there's basically no limit. There's probably a limit. I'm not exactly sure what it is. I'd have to Google it, but I've never run into it, and I've built databases with 30, 40, 50 backend links. So yeah, knock yourself out.
Elad asks, how can we do this action without the shortcut? He's referring to my shortcut keys video, where you can press Control single quote to get the data from the previous record's field, same field. He's looking for a way to refer in code to the previous record. I'm assuming you mean you want a VB solution. That is going to basically involve knowing how to look up the previous record in code. If you want to be able to click a button, for example, I wouldn't necessarily rely on the autonumber, the ID. They're usually in sequence, but not always. The best way to do this would be to date timestamp all of your records, so that when a record is created, you store a date time of when that was saved. Then you could do a DMax and look up the largest timestamp that's less than the record you're on, and then you could DLookup the value that way. That's how I'd do it, but I need to know a lot more about how your tables are set up and so on. I'll put a link to the DMax function down below for you. If you need more help than that, post something in the forum on my website, along with maybe a screenshot of what your forms or your tables look like, so we can get more information.
Leo Mareld asks, if I use MySQL as my database, as the storage in the tables increases, is the query I run more than two gigabytes? Well, I honestly have never used MySQL. I don't know, I can't answer that. You could probably Google that and find the information out. SQL Server, which is what I recommend, definitely can store more than two gigabytes, so you shouldn't have any problems there. But I don't know, I've never used MySQL, so go talk to a MySQL guy. Sorry, I'm Access and SQL Server, that's it.
Diego wants to know if there's any way to replicate an autonumber in an insert query. If by replicate you mean having more than one record with the same autonumber, no, you can't do it. That's not what autonumbers are for. Autonumbers are so every record in that table has a unique value, and that's what they're there for. There are tricks to get an autonumber back, or you can use a foreign key or some other type of key to have a duplicated value like that, but not an autonumber. I have tons of different videos on autonumbers, go watch these. Autonumbers are one of the most misunderstood things in Access, and they're very powerful, but you've got to understand the limitations of them. Lots of different stuff on autonumbers.
Nicholas is talking about my double lookup, my DLookup on two criteria, and in my video, I do a state and a city, and both of those are text values, like text "New York" and "Buffalo," and those have to be inside of quotes inside the string with an "and" in there. Nicholas is saying he can't get it to work with the first criteria as a date and the second is an ID field. Well, an ID field is a number, so you don't need any fancy stuff around that, just "ID equals" and then the number. Dates have to be inside of pound signs.
Go to my DLookup page. On this page, if you scroll down a little bit, here are all the common formats. It's DLookup(field, where you're looking it up, and then the criteria). If you've just got a simple hard coded ID, that's it, customer ID equals five. If the number is outside the string, you're getting it off of a field or a variable, for example, it looks like that. Text strings, like I show in the video, look like that. Dates have to be inside of pound signs. If your date field is last order date, then it's got to look like this. If there are two of them, so if you've got an ID and a date, it's going to be "order date equals #date#" and then "and," and then "ID equals" blank like that. You put it all together.
I know, this stuff is tricky. The rule is: numbers, nothing special; dates, pound signs; text strings, quotes. Just simple rules, that's all.
Jessica is talking about my split database video. She says for some reason, when we do this, it's an error that's not a valid path. When I open it on my computer, there are no issues. The backend is exactly where the path says, everyone has access to that server, but it's unable to open the frontend. I followed your instructions exactly. First of all, it's got to be exactly the same name. If it's Z:\\database, or whatever your share name is on one computer, it's got to be that way on all of them, including the server. If it's \\\\server, if you're using UNC naming conventions, it's got to be the same everywhere. You can do, if it's server\\database on some machines and Z:\\database on others, as long as it's pointing at the same server, that's okay, but it's best if it's all the same.
Another thing: make sure that everyone has read/write access to that directory. Everybody's got to be able to read and write files there. If they can see it, but they can't write to it, they're going to get a problem. See if they can create a text file, or make a folder, or something in that database folder. They use an Access database, you have to have read and write access. I know a lot of people just want to give read-only access. It won't work that way.
So that's it. This is one of those problems where without being there, without seeing it, it could be 50 things. I can't really tell you. It's probably a networking issue and not an Access issue, though. That's my two cents. I know back, especially in the 90s and early 2000s, when I used to set up networks, I'd go in and set up 20 computers on a Windows network and get everybody working properly, and then the Access database wouldn't work on three of them for some reason, and everything's mapped the same. Pull my hair out. Turns out it's a bad network card. It could be anything. I had a woman once whose computer would just randomly reboot. We basically rebuilt the whole computer from scratch, and it turned out to be a short in her keyboard. Believe it or not, when she plugged that keyboard in, because I'd take the CPU box back to my office and it worked fine, we'd replaced the motherboard, we'd bring it to her office, plug it in, 20 minutes later, it locked up and restarted. It was a bad keyboard. So you never know. I love computers, I really do. There's always a gremlin somewhere.
Christopher says he saw a video in which I show how to fix reports when using a combo box, where it shows the field value and not the ID number. I think what you mean is a lot of people ask me this question, and I don't think I have a video on specifically this. A lot of people try to make a report that's based on a table, and the table might have something, let's say it's an invoice ID or a customer ID on an invoice, and you want to display the customer's name, not the customer ID. What you do in that case is make a query that brings together the customer table and the order table, and you can have the customer name in the report because it's based on the query. Make a query first, bring all the fields together that you want, then use that for the report.
If you watch my invoicing video, for example, here's the order table. The order detail information from the query and the customer table; you can bring in the first name, last name, whatever, off the customer's record, to be displayed on your invoice because you make a query out of it. Go watch the invoicing video for more information on that.
That's going to do it for today's quick queries video. I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the primary reason the instructor repeats content in lessons?
A. To fill time in videos
B. To increase video views
C. To help students retain information through repetition
D. To make the lessons more complex
Q2. What analogy does the instructor use to explain how repeated exposure aids memory retention?
A. Water carving a path in stone
B. Writing in a notebook
C. Walking paths on a college lawn
D. Painting layers on a wall
Q3. What storage recommendation does the instructor give for Access database files to avoid "database in use" errors?
A. Store the file on your desktop
B. Store the database in a folder not automatically synced to cloud services
C. Only use Dropbox for Access databases
D. Never backup the database
Q4. What causes the "database in use" error when someone is the only user and the database is in a Google Drive Sync folder?
A. Another user is secretly accessing the file
B. The antivirus software is scanning the file
C. Google Drive is trying to backup the file, preventing exclusive access
D. The Access software is out of date
Q5. What table structure does the instructor recommend for a business tracking sales people who may work at multiple outlets?
A. Store everything in one table
B. Use only an outlets table
C. Use separate tables for salespeople and outlets, with appropriate relationships
D. Use only a salespeople table
Q6. What should you do if the same salesperson works for multiple outlets?
A. Ignore it and use a single outlet per salesperson
B. Set up a many-to-many relationship between salespeople and outlets
C. Use only one table with extra columns for each outlet
D. Avoid tracking outlets altogether
Q7. According to Microsoft, what is the maximum size for an Access database file?
A. 1 gigabyte
B. 500 megabytes
C. 2 gigabytes minus space for system objects
D. 4 terabytes
Q8. What determines the practical number of records you can store in an Access table?
A. The number of tables in the database
B. The size of each record and the 2GB file size limit
C. The speed of your CPU
D. The Windows version
Q9. What is the keyboard shortcut to open a subdatasheet in Access?
A. Alt + Down
B. Ctrl + Shift + Down Arrow
C. Ctrl + Down Arrow
D. Shift + Enter
Q10. Why does the instructor prefer to base Access forms off tables rather than queries?
A. Forms based on tables load faster and are simpler
B. Queries are unsafe
C. Tables allow for more colors
D. Queries cannot be used in reports
Q11. What is the suggested solution for reaching the Access data limit?
A. Stop adding more data
B. Use fewer tables
C. Split data into multiple backend database files or move to SQL Server
D. Upgrade to Office 365 Premium
Q12. Why is it not possible to have duplicate values with autonumbers in Access?
A. Autonumbers are used for text values only
B. Autonumbers must be unique for every record
C. You can have duplicates if you use VBA code
D. It's allowed but not recommended
Q13. How should you format a DLookup criteria string for a date field in Access?
A. Put the date in single quotes
B. Use curly braces around the date
C. Place the date between pound signs (#)
D. No special formatting is needed
Q14. What is a key requirement when multiple users access an Access database frontend connected to a shared backend?
A. All users must have the same version of Windows
B. Each user should have their own version of the backend
C. All users must have read and write access to the backend folder
D. Users should only have read-only access to prevent data loss
Q15. What is the recommended method to display a related value (like customer name) instead of an ID in an Access report?
A. Directly reference the ID field
B. Only show the ID in the report
C. Create a query joining the necessary tables and base the report on that query
D. Use VBA code to change the displayed values
Q16. If you want to refer to the previous record's value in VBA code, what should you do?
A. Use the AutoNumber field to find it
B. Use a DMax on a datetime field to locate the previous record
C. Use a DSum function
D. Rely only on the current record context
Q17. What is advised if your Access database needs to store more data than the 2GB limit allows?
A. You cannot exceed this limit under any circumstances
B. Use multiple backend database files or switch to a server-based solution like SQL Server
C. Compress the data using Access options
D. Store extra data in Excel instead
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-B; 9-B; 10-A; 11-C; 12-B; 13-C; 14-C; 15-C; 16-B; 17-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 answer a series of questions that many of you have emailed or posted on my forums or YouTube channel. This series is for the smaller questions that may not need a full video of their own, but still deserve clarification and discussion.
First, I want to mention a recent article I posted on my website about the importance of repetition in my lessons. It was inspired by a TikTok video on spaced repetition. The main idea is that the more frequently you encounter information, especially in a short span of time, the better your brain retains it. Every time you learn something new, you begin to forget it almost immediately. So, reviewing or revisiting the same concept again and again is critical for reinforcing those neural pathways. It's similar to repeatedly walking the same path across a lawn and gradually wearing it down – your brain works in a similar way when forming strong memories. While some viewers appreciate this approach, others may not enjoy as much repetition. I would love to hear your thoughts about this learning method in the comments.
Often, I teach a method by first presenting a basic example, such as using DLookup with just an ID. Then, I expand on it by showing how to look up other data types like dates or strings, handling the necessary differences. From there, I introduce more complexity, such as adding a second criterion or using the NZ function for nulls. Especially for beginners, repeated practice like building forms several times pays off. After nearly three decades working with Access, I've found that most concepts only stuck after seeing them multiple times and, yes, making the same mistakes more than once.
Now, let me shift to a specific technical question that came up recently. One of my long-time students, DanZ, was getting a "database in use" error when trying to compact his database, even though he was the only user and working on a standalone PC. After investigating, we discovered the culprit was Google Drive Sync. If your Access database resides in a folder that syncs to a cloud service like Google Drive, the background syncing can interfere with Access's need for exclusive access to the database file. The best prevention is to keep your working Access database in a local folder that is not synced, and then have a regular backup routine to copy it into a synced folder as needed, usually at set intervals or during off hours. Manual or scheduled backups to the cloud are a far better method than working directly in a synced directory.
Another question came from Ludwig, whose company sells kitchens through 45 independent outlets, each with unique sales people and work planners. He wanted to know the best way to design tables for tracking these people in relation to outlets and orders. The key principle in good database design is to keep each table focused on one type of data. So, sales people belong in their own table, with a foreign key to link them to the outlet where they work. If a sales person can work for multiple outlets, a many-to-many relationship will be needed, often by introducing a junction table. Outlets also get their own table, and your orders table would then link to both the sales person and outlet as needed. For more ideas on planning this out, you might want to watch my association database video, where I take time to design tables and relationships carefully.
Richard asked about the limit on records in an Access table. The primary restriction is that an Access database file tops out at two gigabytes, minus system objects. There's no defined record count cap; it all comes down to how much disk space your data takes. If your records are small, you can fit far more of them; bigger records mean fewer fit. While tools like ChatGPT or Bard suggest high theoretical limits, realistically, you will rarely run into them with small to midsize applications. If you do, it might be time to move your backend to something more robust, like SQL Server, rather than relying on multiple Access files.
Joan was interested in keyboard shortcuts for toggling subdatasheets in Access. The answer is Control Shift Down to expand and Control Shift Up to collapse. I don't use subdatasheets very often myself, since I prefer more controlled forms and queries, but it can be handy for some developers.
Here's a question I get regularly: when building or updating a form or report, why don't I use the built-in query builder to edit the record source SQL directly within the object? My approach is almost always to base forms on tables, not queries, for performance and clarity. When I do use queries, I create them separately so I can reuse them in multiple places. This way, any changes I make to the query propagate everywhere it's used. The only exception is if I need to manipulate the SQL with code, such as in filterable list forms.
Regarding splitting your data into multiple backend files, as Icras asked, it can be an effective solution for very large or complex databases. I've used many backend files in my own projects and have yet to hit a practical limit, even with dozens linked. For truly large or sensitive datasets, I do turn to SQL Server for storage, but Access remains useful for many tasks.
Elad wanted a code-based way to reference the previous record's field value, rather than using the keyboard shortcut Control and single quote. The best way to do this programmatically is to add a date-time stamp to each record as it's created. This allows you to look up the newest record saved before the current one. With functions like DMax and DLookup, you can retrieve these values as needed.
Leo asked about using MySQL as a backend and whether queries could surpass two gigabytes. I personally don't use MySQL, so can't give a firsthand answer, but I can say SQL Server handles much larger databases without issue. For details about MySQL limits, it's best to consult MySQL documentation or experts.
Diego wanted to know if you can replicate an autonumber value in more than one record using an append query. Autonumbers in Access are unique by design and can't be duplicated within the same table. If you need duplicate values, use a different field as your foreign key and manage duplication as needed. I cover autonumbers in detail in several of my videos.
Nicholas raised a common question about DLookup across two criteria, especially when using different data types like a date and an ID. When constructing your criteria, remember: numbers go in as-is, dates need pound symbols, and strings require quotes. Mixing data types can be confusing at first, but I provide several handy examples on my DLookup reference page.
Jessica reported issues with split databases and path errors when opening the frontend on other machines. It's vital that the backend file path remains consistent across all computers, and everyone must have full read and write access to the shared folder. Differences in mapped drives or permissions are usually the cause.
Lastly, Christopher asked about displaying descriptive values from combo boxes (such as a customer name) instead of IDs in Access reports. The solution is to create a query that joins your transaction table with the related lookup table, bringing in the name fields you want to display in the report.
That wraps up this round of Quick Queries. I hope these answers help clarify some common Access obstacles and design questions. 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 Explanation of spaced repetition and memory retention
Problems compacting Access databases stored in cloud sync folders
Why Access databases should not be run from synced folders
Recommendations for backing up Access databases to cloud storage
How to structure tables for salespeople and outlets in a business
Handling many-to-many and one-to-many relationships for sales staff
Maximum record and file size limits in Microsoft Access
Difference between theoretical and practical Access table limits
Access table size versus record count considerations
Access database splitting and using multiple backend files
Keyboard shortcut to expand or collapse subdatasheets in Access
Why use queries versus SQL strings as form record sources
Access form record source and query builder considerations
Discussion of backend file limits for split databases
How to get field values from previous records in VBA code
Using DMax to find previous records by timestamp
DLookup function criteria formatting: numbers, text, and dates
Dealing with errors in split Access databases and network paths
Combining tables in queries to display names instead of IDs in reports
Using queries for reports to join related data for display purposes |
