Quick Queries #44
By Richard Rost
10 months ago
Database Size, Attachments, SQL Server, Random Quiz
In this Microsoft Access tutorial I will show you how to address common questions including fixing the database has reached maximum size error, tips for managing large databases, best practices for storing images and attachments, options for randomizing quiz questions, working with very large recordsets in SQL Server, using OCR services for bills, troubleshooting copy and paste issues, managing table relationships, and tips for Mac users who want to use Access. This is part 44 of the Quick Queries series.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Error 3036, database maximum size, compact and repair database, attachments storage best practices, storing images in database, external file storage, split database, archive old records, union query, extended info tables, SQL Server backend, random quiz generation, random number generator, DSum DCount usage, pass-through query tips, OCR with Google API, OCR with OpenAI API, account balances template, relationship window tips, Access on Mac, virtualization for Access, playlist management YouTube, continuous forms optimization, datasheet view with conditional formatting, copying and pasting issues Windows 11
Intro In this video, we'll cover a range of Microsoft Access questions for Quick Queries Friday 44, including how to fix the "database has reached maximum size" error, ways to efficiently store files and archive data, tips for working with large tables and using pass-through queries, and advice on splitting and optimizing your Access databases. We'll also talk about generating random questions for quizzes, automating account balance updates, using the Relationships window, and running Access on a Mac. You'll find viewer Q&A and practical tips for handling common Access issues throughout.Transcript Welcome to another TechHelp Quick Queries video. This is number 44 for Quick Queries Friday. Brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
For those of you who don't know, Quick Queries is where I take all the little questions throughout the week that people send me, which don't need a whole video in and of themselves, and I put them together here.
In fact, the first one today is actually a topic I have a video coming out on, but probably not for another week or two. I figured I'd address it quickly today for those who want to know. Let's get to it.
I get this question every now and then, which again is why I've got it on my slate to make a full video on it, but I'll just go over the basics today.
Anthony says, "I'm trying to add records, but Access gives me 'database has reached maximum size.' Error 3036. What does that mean, and how do I fix it?"
Well, basically Anthony, the maximum size of a Microsoft Access database file, whether it's an ACCDB or an ADE or whatever, can only be two gigabytes. That's not really a lot of space, but there are a bunch of things you can do and check to make sure that you fit within that two-gigabyte file limit. Here's my list of stuff to check. I've got videos that talk about all of these things in more detail.
The first thing is to obviously run a compact and repair. That will get rid of any empty space in your database, because when you delete records, that space is still there until you compact it. Then it gets rid of all of that extra space. It's kind of like pushing your foot down on a garbage can to compress all the air out of it. I have an event that runs weekly that compacts and repairs my database, and it usually saves about five to ten percent. That will add up over time. If you've never compacted before, do it. You'll be surprised.
The next thing, when people tell me that they're running out of space in their database, they're doing one of the two following things: they're either using attachments, or they're storing images in their database file, and both of those are no-nos. I do not like attachments--storing Word documents or PDFs or whatever in your database--same thing with images. Don't be storing big images in your database. It's not optimized for that. You're going to store them externally, in a folder on your file server, or even on your website, and then just store a link to those in your database so it's only text. Both of those videos will explain what to do in that case in more detail.
Next, obviously, is split your database.
Next step is you can archive old records. I have a video that covers that in detail. For example, old customers--someone who hasn't placed an order in ten years--you don't want to lose them completely, but you don't need them in your active customer file. So you just archive them to another backup table that's stored in a different database file. If you want to see them all together or search in that, you just union it in with your main table. That's what the union query thing is for.
Then I have another trick that I do called extended info tables. I don't have a TechHelp video that shows this, but I do cover it in Access Expert Level 4. That's basically saying that you might have some information for all of your customers--first name, last name, address, phone, that kind of stuff--but you might have a bunch of additional information that you don't have for everybody, like demographic information, preferences, that kind of stuff. You could take all those fields that you don't use for everybody, put those in a different table, and just make a one-to-one relationship between your customer and your extended customer file. All of those empty fields, even though they're empty, do take up a little bit of extra space. That's one more trick you can use to split things up.
I'm going to cover all of this in a more detailed video coming up in the future, but in the meantime, watch all of these and it will give you all the information you need to overcome that pesky two gigabyte limit.
If all of that fails and you're still running into problems, the ultimate solution is just to upgrade your backend to SQL Server. If you've got that much data, it might be time. There's a free version available. I believe that the default is five gigabytes for a database file for the free version.
Nope, I was wrong. I just looked it up. It's 10 gigabytes. They increased it from four to 10 gigabytes with SQL Server 2008. If you outgrow 10 gigabytes, then you can upgrade to the paid versions, or you can split across multiple Express databases, although that can be messy. If you've got that much data, then it's worth paying for SQL Server at that point.
I've been running my business for 20 years with the same database file. Yes, I've got mine split up into multiple backends. My customer table alone is probably about a gig and a half, so I'm going to have to start doing some optimization tricks on that one soon. I could probably archive a lot of old customers--if you don't log in on my website in the last five years, I move you off to a different table.
Anyway, I hope that answers your question Anthony. Again, I've got another video on this coming out in the future, pretty soon.
All right, let's head over to YouTube then. Cat G says, "How can we create multiple choice random quiz? Could you please up on this?" This is based on my flashcards video.
You have to learn how to do random numbers first, and of course I've got a video for that. This one will teach you how to generate random numbers and dice rolls. If you have a certain number of questions in your question database, all you have to do is use DSum or DCount to count up how many you have, and then generate a random number from one to that; that's the question you ask, if that's what you're asking me. I'm not exactly sure from your question. As always, if you want to see me put together a database on doing quizzes with random questions and multiple choice and all that stuff, post a comment down below. If enough people are interested, maybe I'll do a video on it.
I do make a test taker database in my Access Developer Level 9 class, and this is basically kind of what you're looking for. It lets you put in the student, department, class, tests--you can set up all the quizzes and then begin the test and it runs you through taking the test. It's not randomized, but it will take you through answering the questions one at a time and keep track of your score, so it's definitely possible. My Access Developer Level 9--I'll put a link to this down below too.
Fuego Esmorelda says, "I always get confused with ranges defined by inequalities. I have to get all the symbols pointing in the same direction just to make sure I know what I'm doing." I'm kind of the same way too. I like to write stuff like this to keep them all the same way, but it depends on what you're doing. If you're trying to do something between, then you do it this way. If it's outside, you've got to flip it. You want my date to fall outside of the date range between x and 1, so whatever works for you.
Angela Marie says a bunch of stuff, but the important thing in here is that she's working with SQL Server, and some tables have 500,000 plus records. She did try continuous forms at first but found it could work better with datasheets and lots of conditional formatting. That's one way to work with it.
What I like to do is specify my criteria first if I can, like on an unbound form, like your main menu, for example. If you're working with dates, specify a date range. Try to get that 500,000 down to something reasonable, and then you can use a pass-through query or whatever you have to do on the server to limit the set of records. Access will bog down if you try pulling a continuous form with 500,000 records. SQL Server doesn't send them all, but it sends a good number--more than you need. So I do whatever tricks I can with my queries, with my pass-through queries, to try to reduce that dataset ahead of time.
Mitchell Fulby says from my accounts payable part two, "For the bill table, it would be nice to scan a copy or electronic copy of the bill as well to get your billing data into your table." That would be great. It'd be nice if Access had some way to automatically do some OCR to pull data. You can do it with web APIs, like the Google web API. You can take an image, use your database, send it to Google, have it do the OCR, and get it back. There are ways to do it. You could do it with ChatGPT too--the OpenAI API can do OCR. There are ways to do it.
Personally, I just go to whatever vendor for receivables, payables, credit card stuff, checking accounts. I just open up their website to see what I owe them and then just copy the data that way. You have to log on at least once a month or so and check your statement, check your balances, all that stuff. While you're there, you might as well just grab the data that you need right off their page.
In addition to my payables seminar, I have this thing that I use called the account balances template that I use for recurring expenses, keeping track of your balances for your checking accounts, credit cards, all that stuff. There is a script editor in here, and what you can do is program in what your banks or your credit cards or your vendors or whoever's website data looks like, and say, "OK, go to that web page window, your browser window, copy all the text, and find the data that's between 'balance' and whatever's after 'balance' and then just grab that number," and it puts it right into your table for you. I have that available in my account balances template. You have to write the script for it, but once you have it, it's really cool. You literally just load up your bank website, click one button, and it will pull in your balance, pull in your amount through, and any recurring transactions or pending transactions will be marked as paid. It's pretty neat. Check it out. There's the link.
All right, next up we've got this, which I've got to go load Google Translate now.
"Please add Arabic audio translation to your wonderful videos." Well, if it's available in YouTube, then it'll be activated. I activated all the languages that I could, so if it's not showing up, maybe it just hasn't done it yet. I know sometimes it'll do Spanish and French and stuff first, and then over time it does the other languages. I have zero control over this and I don't speak Arabic. I don't speak any other languages except for English--and even that's not half the time. That's all up to YouTube. I have zero control over it.
As you can see here in my advanced settings, I've got "allow automatic dubbing" on and I don't have to review them. As soon as they're ready, they should get published.
Digging a little deeper, it looks like only these languages are the actual ones that are supported: French, German, Hindi, Indonesian, Italian, Japanese, Portuguese, and Spanish. I guess Arabic is still considered experimental so some videos might have it and others don't. Until they completely roll it out, I'm not sure.It's completely up to YouTube, not me. So trust me, if it's available, I want to get my video watched by as many people as possible. So if there's a new language out, of course, I'm going to say do it right.
Next up, Nick Brokisen, if I'm pronouncing that correctly, nice funny coincidence. I'm busy rewriting one of my old, very old databases where I use invoices and payments. Don't know if you're gonna do statements too. Well, it just so happens, I got all kinds of statements videos. I got account statements. I got billing statements. I got accounts receivable. That all starts here with my invoicing video. So whatever kind of statements you're looking for, I got you. I'm like Leo Getz, right? Whatever you need, Leo gets.
And I spend the majority of my full course, my Access course on my website, covering customer invoice sales, inventory, all that kind of stuff. So as much as you're looking to learn about this kind of stuff, I got videos for it.
Our tat one says regarding your frustration with copy and paste not working consistently: Are you sure it's an MS Access problem? No, no, I'm not. It just started happening randomly. I'm gonna say less than a year ago. I've been running Windows 11 since Windows 11 came out on my training machine. I have not had any problems copying and pasting files in Explorer.
I've checked online. I even asked Chat GPT. It didn't really give me any help. It says run Access as administrator, try turning off clipboard suggested actions. I've got the clipboard history on, I tried turning that off, I've tried a bunch of stuff, uninstalling updates, rolling back, none of it's really worked. So I don't know. I really don't know.
It's seemingly random too. Sometimes it'll work fine, sometimes it doesn't. Like I should be able to select all of that, go copy, paste, and it should paste it, and it didn't. But then again, if I try it a second time, copy, paste, it works. See, so I don't know, right? Copy, paste. Okay, do it again. Copy, paste. Second time is always a charm. No, not always. Sometimes it doesn't work the second time. Copy, paste. Copy, paste. Click, do it again, copy, paste, and then it works. It's like every other time it works. I don't know why, it's annoying. But I've learned to just live with it and just deal with it.
I've tried turning off a bunch of stuff. I've done everything except roll back to Windows 10. In fact, I think my server still runs Windows 10 - the big old I'll go experiment with it later. But if any of you come up with a definite fix or a reason why it's still happening, let me know. I would love to share it.
Now this is the kind of comment I like to see: Thank you for your tutorials, extremely helpful and you're a huge contributing factor to my promotions at my job. I am honored and privileged to have helped you. Just don't forget about me when you're in the big leagues and you're on top and you're CEO. All right, remember, remember, remember old Ricky here.
Next up, Hallcott, Allcott, Mohammed: Regarding the tables relationship design show up, there is an easier step than adding the tables again and the relationship just designed. Just click on all relationships and it shows up again.
Yes, I think I know what you're talking about. Let me see if I got you right here. So if you go to your database tools, your relationships. Now see, I don't use these very often. It's very rare that I use these, because they don't work across database files, and a lot of my databases that I've built in a production environment - well, I either am using SQL Server, or the back end files could be in two different files. The back end tables could be in two different files. So you can't use relationships across those.
But let's say you're working with one database, and you've got some relationships set up. Let's say you've got the customers and you got orders, right, and you've got them joined together by customer ID. So customer ID over here, and let's say you've got some referential integrity in case enforced.
Now, if I save this, the relationship is made. If I close this and come back into it, you'll see it there. Now even if you delete tables out of here, that doesn't delete the relationship. I think that's what you're trying to say.
So even if I delete both of these guys, that relationship still exists, you're just removing them from the layout. So if you come back in here and go right click, show all relationships, it'll show you all the tables that you get relationships with. This is something that you can do because you might have 20 different tables in here that all have different relationships. But if you only want to work with three of them, you can just take them out of here by just deleting them, and then go and do what you want to do, work with the other tables.
Close this, save the relate - it says save the changes to the layout of the relationships. This is nothing more than just a big map. Later on, if you come in here and you're like, oh, where was that one table that I needed? Oh, okay, let me see. I can just show all relationships. Oh, there it is.
There's also a link up here, and if you get rid of this or hide the table, basically, and you don't want to bring all 15-20 tables back, you can just drag it back in here and it'll pop back in with that relationship intact. There's a million ways you can do it, but again, I don't spend a lot of time in the relationships window. I don't use it myself that often. I just don't. I prefer to handle things like referential integrity myself in code, but that's just my preference. But thanks for the tip, great tip.
Camera Week TV: It's a shame Access isn't available for Mac users. Yeah, it is. I mean, the more Access users the better, even though I'm not a Mac user myself. Talk to Microsoft. Yeah, they got Word, Excel, yeah, I think PowerPoint even, all those other apps are available. It's probably something to do with the file system. I don't know, but that's definitely a Microsoft question.
There are ways to get Access to work on a Mac, but it usually involves virtualization, basically running Windows inside your Mac. So you can get it to work, but it's not native. In fact, I do have a video on this topic coming out soon about how you can run Access on a Mac and some different techniques you can use. So stay tuned for that.
Unreal Ramirez says can you list in your channel so small series like this are easier to watch. I think you're talking about setting up playlists. Yeah, I could set up playlists on YouTube. It'd be a little extra work though to go back after the series is done and set up the playlist. But if you go to my website, if you want to watch the videos on my website, there's little links here that'll take you right to the next one. Right here's accounts payable two, click. All right, take you to next one, accounts payable three, click, and so on.
Down here, it's got the prerequisite where you can find it. Up here is the previous one. I know Alex sometimes sets up actual pages that have all of them on the same page. I like to have them all on separate pages though, because then down here in the bottom you get the questions that are just for that particular video, instead of having them all together.
But yeah, I design for my website. YouTube is just an extra place to put them. But I won't lie though, my traffic comes from YouTube, so yeah, I like to keep YouTube people happy too.
All right folks, that's gonna do it for another Quick Queries Friday. Hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Microsoft Access database maximum file size
Running Compact and Repair to recover space
Avoiding attachments and images in database files
Storing files externally and linking in Access
Splitting an Access database
Archiving old records to separate backend files
Using union queries to combine archived and active data
Extended info tables with one-to-one relationships
Upgrading Access backends to SQL Server
SQL Server Express database size limits
Random number generation in Access
Counting records with DSum and DCount
Building random question selection for quizzes
Reducing records loaded from SQL Server with criteria
Using pass-through queries to limit SQL Server records
Automating bank statement data extraction with scripts
Using Account Balances Template for recurring expenses
Working with Access Relationships Window layout
Showing all table relationships in Access
Deleting tables from the Relationships layout without breaking links
Access availability for Mac and running via virtualization
Setting up playlists and navigation on a tutorial website
COMMERCIAL:
In today's video, we're discussing a variety of viewer questions for Quick Queries Friday number 44. We will talk about how to handle the 'database has reached maximum size' error in Access, suggestions for storing large files, archiving records, and ideas for optimizing big databases. We'll also discuss creating random multiple choice quizzes, working with conditional formatting and huge tables, pulling data from web pages, and making the most of Access features like the relationships window. Plus, I'll answer questions about running Access on a Mac, setting up video playlists, and more. 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. What is the maximum file size limit for a Microsoft Access database (ACCDB or ADE)?
A. 500 megabytes
B. 2 gigabytes
C. 5 gigabytes
D. 10 gigabytes
Q2. What is one of the first actions you should take if you get an error saying your Access database is out of space?
A. Delete all your tables
B. Compact and repair your database
C. Upgrade to a newer version of Access
D. Install more memory on your computer
Q3. Why is it not recommended to store large images or attachments directly in an Access database?
A. Because Access does not support images or attachments
B. It slows down computer startup time
C. It quickly fills up the database file size and impacts performance
D. It causes security vulnerabilities
Q4. What is the preferred way to handle images or files related to records in MS Access?
A. Store the files directly inside the database
B. Store the files on disk and only keep the file path (as text) in the database
C. Convert the files to Excel format
D. Use Access macros to embed the files
Q5. What is an advantage of splitting your Access database?
A. It reduces the number of forms needed
B. It helps manage data when your database is reaching the size limit
C. It prevents multiple users from accessing the file
D. It improves email integration
Q6. What is archiving old records?
A. Backing up your entire database
B. Moving seldom-used records to another table or file to reduce database size
C. Printing old records for offline storage
D. Compressing tables within the database
Q7. What is an 'extended info table' used for in Access database design?
A. Storing backup copies of tables
B. Storing infrequently used fields in a separate table to save space
C. Keeping customer passwords secure
D. Calculating totals for reports
Q8. If you still run into storage issues after optimizing your Access database, what is the next suggested solution?
A. Reinstall Access
B. Upgrade your backend to SQL Server
C. Use more macros
D. Export your data to Excel
Q9. When designing a random quiz in Access, what should you use to select questions?
A. AutoNumber fields only
B. DSum or DCount to determine the number of possible questions, then generate random numbers within that range
C. A fixed list of question IDs
D. A separate quiz design software
Q10. When working with very large tables in SQL Server through Access, what is recommended for performance?
A. Always use continuous forms for display
B. Specify criteria first to limit records before displaying data
C. Download all records for local use
D. Use only default queries without any filtering
Q11. What is a use for OCR (optical character recognition) in connection with Access databases?
A. Designing table relationships
B. Extracting data from scanned images to import into the database
C. Encrypting confidential fields
D. Backing up your database files
Q12. What feature does Access relationships window NOT support across different files?
A. Linking tables visually
B. Referential integrity between different backend files
C. Creating new tables
D. Viewing single table properties
Q13. If you delete tables from the layout in the relationships window, what happens to the actual relationships between tables?
A. The relationships are permanently deleted
B. Relationships between tables remain intact; only the layout is changed
C. The tables are deleted from the database
D. The fields are reindexed automatically
Q14. What is required to run Microsoft Access natively on a Mac?
A. Nothing, it runs natively on Mac
B. Use virtualization or Windows on Mac
C. Install a special Mac version of Access from Microsoft
D. Use Safari browser
Q15. What is a playlist on YouTube useful for regarding a series of instructional videos?
A. It improves video resolution
B. It groups related videos together for easy sequential viewing
C. It adds captions automatically
D. It increases the storage space
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 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'll be answering a collection of questions that have come in throughout the week. Quick Queries is my way of providing quick, concise answers to topics that do not need a full video, so if you have any burning Access questions, this is a great way to get them addressed.
Our first question deals with a common issue: what does it mean when Access tells you that your database has reached the maximum size, specifically Error 3036, and what can you do about it? Microsoft Access has a two gigabyte maximum file size for database files, regardless of whether you're using ACCDB or ADE formats. This can actually be filled up fairly quickly, depending on how you use your database, but there are effective ways to work within this limit.
First, you should always start with a compact and repair operation. When you delete records in Access, the space they occupied isn't immediately reallocated, so running compact and repair cleans this up and often recovers a significant portion of your file size. In my own routine maintenance, I've set up an event to compact and repair weekly, and it typically saves about five to ten percent of space every time.
You also want to avoid storing large objects in your database, such as attachments, images, Word documents, or PDFs. Access is not designed to handle large files stored internally. Instead, keep these files outside in folders on your server or website and only store the path to those files in your Access database. This keeps your database lightweight and performs much better.
Another proven method is to split your database, separating the data (backend) from the application/frontend. Archiving older data that you don't need on a day-to-day basis is also a good way to manage your file size. For instance, you can move customers who haven't placed orders in a decade to a separate archive table in another database, and if you ever need to view everything together, you can use a union query to display both tables seamlessly.
If your application includes many fields that are not used by every record, consider using extended info tables. For example, you might have a basic table for all your customers with contact information, but only a subset needs extra details such as preferences or demographic fields. Move those rarely used fields to a separate table that's joined in a one-to-one relationship, which helps keep your main table smaller and more efficient.
If you eventually reach a point where Access cannot handle your data size even after these optimizations, migrating your backend to SQL Server might be the answer. SQL Server Express is free to use, and the limit on each database is now ten gigabytes. If you need more space, you can upgrade to a paid version or split across multiple databases, though this can add complexity.
I've used a similar setup in my business for over twenty years. My own customer table is now approaching one and a half gigabytes, so I'm starting to apply some of these solutions myself, like archiving inactive customers.
Moving on to our next question, Cat G asked about creating a quiz with random multiple choice questions, similar to my flashcards setup. If you want to create randomized quizzes, you'll first need to know how to generate random numbers in Access. I have a video that covers this, explaining how to create random numbers and use them to select random records. Essentially, you would count the total number of questions using DSum or DCount, then generate a random number within that range to select your question. If there's enough interest, I might put together a full video on making a complete randomized quiz system.
For those seeking something more complete, my Access Developer Level 9 class covers building a test-taking database. It allows you to input students, classes, and tests, and manages the process of taking and scoring tests. Although it doesn't pick questions at random, it gives you a good structure for building your own quiz system.
The next question was about working with ranges and inequalities, particularly with symbols pointing in the right direction. I sympathize with this—it can be confusing! My advice is to always write inequalities in the form that makes them easiest for you to understand, especially when you're building criteria or between statements.
Angela Marie mentioned working with large SQL Server tables containing over 500,000 records and wanting to use datasheets and conditional formatting. My approach for handling large sets like this is to always start with a criteria form—let the user specify a range or filter, such as a date range, before running any queries. This reduces the volume of records being pulled and reviewed at one time, which speeds up Access responses. Pass-through queries can help limit datasets on the server side, which is essential for performance.
Mitchell Fulby asked about scanning and electronically capturing billing data from invoices. While Access doesn't have built-in OCR, you can use web APIs like Google's or even the OpenAI API to process the images and return the readable text. In my workflow, I often just log into my vendor's or bank's website and copy the data manually, which works efficiently if you only need to do it periodically. Additionally, my account balances template includes a script editor so you can write routines to fetch and parse financial data directly from web pages—after some initial setup, it can automate balance retrieval from your online accounts.
Another viewer requested Arabic audio translation for my videos. I have enabled all available language options on YouTube, but at this time, only a limited number of languages are fully supported, with Arabic still listed as experimental. The availability of translation and dubbing is entirely controlled by YouTube, so if a language isn't available, it's not something I am able to affect directly.
Nick wrote in about revisiting an old invoices and payments database and asked about statements. I have several videos covering billing statements, account statements, accounts receivable, and more, beginning with my invoicing video. My courses cover customer invoicing, sales, inventory, and related topics in considerable detail, with plenty of resources for anyone looking to improve their billing and statement workflows.
Another viewer asked if inconsistent copy and paste behavior in Access is really a Microsoft Access problem. The glitch appears to be relatively new and sporadic, at least in my experience under Windows 11. I've tried several fixes, including changing clipboard settings and running Access as an administrator, but nothing has fully resolved the issue. Sometimes copy and paste works on the second try or takes multiple retries. If anyone in the audience has found a sure solution, I'd love to hear about it.
Hallcott wrote in suggesting a quick way to show all the table relationships in your database. In the relationships window, you can right-click and select "show all relationships" to display every table and their connections. Keep in mind, deleting tables from the relationship layout doesn't remove their links—it just cleans up the diagram. I don't often use Access's relationship tools in production because many of my databases are split or use SQL Server backends, but for those working in a single-file setup, this is a handy way to manage and visualize relationships.
There's also some discussion about Access not being available on Mac. Unfortunately, this is true—it is a Windows-only product. Running Access on Mac usually requires some form of virtualization, like running Windows inside your Mac. I have a video coming soon that explains how to use Access on a Mac with these types of tools.
Another suggestion was to make it easier to watch related videos in a series using playlists on YouTube. While setting up playlists takes extra work and is easier to do once a series is finished, my website is designed to guide you through related videos with next and previous links, and also organizes related questions and discussions specifically for each topic.
That wraps up this week's Quick Queries. As always, 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 Microsoft Access database maximum file size
Running Compact and Repair to recover space
Avoiding attachments and images in database files
Storing files externally and linking in Access
Splitting an Access database
Archiving old records to separate backend files
Using union queries to combine archived and active data
Extended info tables with one-to-one relationships
Upgrading Access backends to SQL Server
SQL Server Express database size limits
Random number generation in Access
Counting records with DSum and DCount
Building random question selection for quizzes
Reducing records loaded from SQL Server with criteria
Using pass-through queries to limit SQL Server records
Automating bank statement data extraction with scripts
Using Account Balances Template for recurring expenses
Working with Access Relationships Window layout
Showing all table relationships in Access
Deleting tables from the Relationships layout without breaking links
Access availability for Mac and running via virtualization
Setting up playlists and navigation on a tutorial website |
