Size Problems
By Richard Rost
9 days ago
Detect & Prevent Database Size Issues Before Crash!
In this Microsoft Access tutorial, you'll learn how to detect and help prevent database size problems by understanding the 2GB file size limit and how Access handles out-of-space errors. I'll show you step by step how to create a VBA function to check the size of any file - including your backend database - and display a warning message if it's getting too large. We'll also discuss strategies for regular compacting and monitoring file size thresholds to keep your database running smoothly.
Russell from Dublin, Ireland (a Platinum Member) asks: Yesterday I came into the office and the whole database looked dead. Nobody could log in and Access kept giving me this vague message that said the database was corrupted or not recognized. I wasted almost an hour checking permissions, rebooting the server, and trying to figure out what broke. Eventually I looked at the back end file and realized it was almost 2 GB. I compacted and repaired it and got it back down to around 900 MB, but now I am nervous it will happen again. Is there a way to have Access check the file size every night and warn me before it gets too big?
Members
In the extended cut, we will write a routine that automatically checks all backend files for every linked table in your database. I will show you how to loop through your linked tables so you never forget to check the size of any new backends you add in the future.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, 2GB file size limit, detect database size, prevent database corruption, FileLen function, compact and repair, VBA file size check, backend file management, action queries temporary data, display file size in gigabytes, On Load event, form events, round to decimals, global module function, linked tables size check
Subscribe to Size Problems
Get notifications when this page is updated
Transcript
Today we're going to learn how to detect and prevent size problems in your Microsoft Access database before it crashes. As we all know, Access has a 2GB file size limit, but it doesn't always tell you that it's approaching it. So I guess we could say that Access has size issues too. Anyways, let's get to today's question.
Today's question comes from Russell in Dublin, Ireland, one of my Platinum members. Russell says, "Yesterday I came into the office and the whole database looked dead. Nobody could log in, and Access kept giving me this vague message that said the database was corrupted or not recognized. I wasted almost an hour checking permissions, rebooting the server, and trying to find out what broke. Eventually, I looked at the backend file and realized it was almost 2GB. I compacted and repaired it, and got it back down to around 900MB, but now I'm nervous it's going to happen again. Is there a way to have Access check the file size every night and warn me before it gets too big?"
Well yes, Russell, this is a common problem when you start pushing that 2GB limit. The worst part is Access doesn't give you one consistent error when the file starts getting that full. Maybe we should add that to our list for the Access developer team. Whatever you happen to be doing simply stops working, and the message you get might have nothing to do with file size.
When Access is filling up, it rarely tells you that the database has reached the maximum size or whatever. A lot of times you'll get a vague message like the database is corrupted or not recognized, could not open the file, or Access just refuses to save the data. I've seen forms that just look weird because they can't read or write to the tables. None of those messages scream file size, but the backend is like 1.9GB. And it can also fill up with temporary data too if you're doing any kind of action queries in the background, like make table or append queries. Those temporary tables can also fill up your file size, and until you compact you don't get that space back. So it's important to compact regularly.
I'm not a fan of compact on close. Depending on your database, once a week is usually good enough, but it's also handy to have something in place that can check the file size. Maybe when you do your compact routine, have it just look and say, "Hey, this file is up to 1.4GB, you might want to do something about it." We'll talk about what you can do about it later. First, let me show you a real easy way to just have an Access database check how big it is.
Before we get to that, this is going to be a developer level video. What does that mean? If you've never done any VBA programming before, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes. Also, go watch my status box video. This is what I use to put little messages on the form, and we're going to use that to display the message. I like this better than message box. You should know how to work with variables and how to create your own functions. Today we're going to create our own function to check the file size and return that value in gigabytes.
If you've never made your own function before, go watch this video. These are all free videos, they're on my website and on my YouTube channel. Go watch those and come on back.
Alright, so here I am in my TechHelp free template. This is a free database you can grab a copy of on my website. What we're going to do is make a function that's going to just check the size of any file. You can give it any file you want, just specify your backend file's file name, and then it will check it. Whenever this database loads, it will go check that file. If you have multiple backend files, you can check those too.
Let's write our helper function to go and figure out the file size of any file in gigabytes. Go down here to our global module. Let me resize this guy. There we go. Down here we're going to put our new functions. We'll make it a public function so that everybody in the database can use it. I'm going to call it GetFileGigabytes. It's going to return the file size in gigabytes because I don't really care to the byte or even to the megabyte; 1.5 is enough for me. If it's getting bigger than that, I want to know about it.
If you want it more granular, like megabytes or kilobytes, you can change it easily; you'll see in just a minute. We're going to send into it a file path as a string. So the full path and file name of your database file or whatever file you want, and we're going to return a double.
Remember I said there are really two types of numbers you should ever worry about when working with Microsoft Access: long integers and doubles. Ignore everything else. If it's a counting number, use long integer. If it's got a decimal, use double. Forget all the rest of them until much later on when you need the different types for a specific reason. I cover all those specific reasons in my developer course, and I think I have some other TechHelp videos too, but doubles and long integers are all you need to worry about for now.
First thing we're going to do is say I need the file length of whatever file path you send in. Now there's a function built into VBA called FileLen, so we're going to say GetFileGB equals FileLen of file path. Now FileLen returns the number of bytes in a file, so it's going to be a long number and a long integer, but that's okay. VBA is really good at typecasting, so even though this returns a long integer, this will convert it just fine to a double because we declared it as a double up here. If you want to specifically do that, you could put CDBL out here and put that in parentheses, but you don't really have to.
If it's a one megabyte file, you'll get a number in here like 1,204, etc. Now we're going to convert the number of bytes to gigabytes with just some easy division. It's going to be GetFileGB equals GetFileGB divided by 1024. Everybody thinks it's a thousand, because "kilo" is a thousand, then "mega" is a thousand kilo, and then "giga" is a thousand, but it's actually 1024 in computers. You're dividing and multiplying by 1024. Why? Because it's a perfect square. That's a long theory story, but don't worry about it.
So that'll make it kilobytes, then divided again to make it megabytes, and then divided again to be gigabytes. So we'll convert to gigabytes. Now we've got a number. If it's a 1.5 gigabyte file, you'll get 1.5 at this point. I don't want all those decimals, I just want like two decimal places. We're going to say GetFileGB equals Round(GetFileGB, 2). We're going to round to two decimal places, and that's the value that'll get returned. So we'll call this, give it our file path, and it'll send back a number like 1.6.
Now we just have to go wherever we want to put this. Let's just stick it in a button for now. Let's go back out here, into our main menu. We'll just hijack this Hello World button. Right click, Build Event, and we'll just put in here "status" and then "checking."
I want to get the file size of a particular file and display its size. But I'm going to make a variable to put it in first, so I'm going to Dim D as a double, and I'm going to say D = GetFileGB, and then you want the file path of a file -- your backend file or whatever file you want to check.
I happen to know on my network, I've got a Z drive, and one of my databases is on it. It's Z drive, and then it's MessageArchive.accdb. This is a pretty big file; I think it's about 600 megabytes, and it's literally every customer service email that I've received via my website for the past 20 years. When people tell me they're running out of space, you need to figure out why your tables are so big. I've got 20 years of emails in this one table that's the message archive. I have the main message table in my database, then after a year they get sent to the message archive. Anyways, that's just the biggest accdb file I have on my network.
Let's status it. "Status: The size is" and D, and then "gigabytes" in quotes, and maybe a Beep. See what we're doing? We're going to get the file size of that file, put it in D, and then we can message it. Do you need the D? No, you could throw this right inside of here, but this is just cleaner and easier to read. When we did this before, could we just divide it by that one big number? Yes, but this is easier to understand what's going on. This whole thing could be done in one line, but that's difficult to read. I'm all about making readable code. I'm not one of those guys who tries to compact my code into one line. You can leave that to C++ programmers.
Let's go back to this. Alright, this looks good. Let's Debug - Compile once in a while, and then we'll come back out and close it and save it and open it. It moves because I have code that positions it on the screen for recording videos. Hit the button, ready, go. Boom. There we go. "Size is 0.62 gigabytes," and that is correct. I double checked.
Basically, what you can do now is run it manually if you want every time you log in, or when you open the database. You could put this inside your On Open event or your On Load event for the form. Maybe if you want this to be automatic, come into your form's events. I have several different videos on form events. I already have an On Load event in here. This positions the window, like I just said a minute ago. But after that, we can say CheckDBFileSize, and then we'll make another subroutine: Private Sub CheckDBFileSize, and in here we'll do this stuff. We don't need the beep. We'll take this stuff, stick it up here, and then you could display that every time if you want, just so you know when the database is loading, it's doing its job. You could say something like, "If D is greater than 1.5 then" status "compact soon" or whatever. You could have it play bells and whistles or sing an opera. I've got my database so it plays lots of Klingon sounds and stuff when things happen. I have a whole separate video on playing sounds; search for it on my website or on my YouTube channel.
Now we'll save it. Actually, we're going to come down here and say CheckDBFileSize right there so the button does the same thing now too. The button will say "Checking," then it'll do this, then it'll come back here and beep.
Save it, Debug - Compile, close it, open it, and now notice when it ran it says "size 0.62 gigabytes." Everything's good. Let's change that, let's say it's got to be more than 0.5, right, 500 megs, and now when I run the database, "Burp, compact soon." You can have it throw up whatever flag you want, open a big warning message, play a sound, play a Rush tune, whatever you want. Now you'll be notified: hey, this file is getting pretty big, you might want to compact it.
You can call this for each backend file you want. If you have six backend files, just call it for each one of those. If you have a log file that's a text file, and you want to check the size of that, just put whatever size you want, and it'll check it for you each time you log in to the database.
It's not that hard. It's the FileLen function. There are several different ways to check the length or size of a file. That's the easiest one. I teach more about that in my Access Developer series. In lesson 31, we cover file handling. Let's see what we have in here. That's navigating files and folders. There's file copying, all kinds of stuff, compacting your backend files automatically. There's all kinds of cool stuff in this video; this is I think part two, we're doing classic VB file handling. What do we have in part 30? Oh, reading or writing text files. There's all kinds of stuff. You name it, I cover it.
Now, members, what we're going to do in today's extended cut is write a routine that will automatically check all of the backend files for every table in your database. Sometimes I'll create a new file, like put a table in it, and then attach it to the database, and forget to put it in this check routine. So this will just go through all of the linked tables in your database, and for each one of those, it'll check its backend file. If you add new ones in the future, you don't have to worry about adding them to your routine. I'm all about future-proofing. When you add something like this, you have to think to yourself: I might add new tables from different backend databases in the future, I might want to check those too. We'll do that in the extended cut.
Silver members get access to all of my extended cut videos, not just this one, all of them. Gold members get access to the code vault; you can download the databases from the videos, all kinds of extra perks. Everybody gets some free training and lots of cool stuff.
That's going to do it for your TechHelp video for today. Russell, I hope that answered your question, and I hope the rest of you learned something. Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. What is the maximum file size limit for a Microsoft Access database file?
A. 2GB
B. 1GB
C. 4GB
D. 10GB
Q2. What type of error messages does Access typically display when the file size limit is reached?
A. A clear message indicating the file size is too big
B. A message saying the file cannot be opened or is corrupted
C. A blue screen of death
D. A message prompting you to upgrade Access
Q3. Why is it important to compact your Access database regularly?
A. To improve graphics performance
B. To remove unused space and prevent reaching the file size limit
C. To increase the number of users
D. To enable new features
Q4. What built-in VBA function can be used to get the size of a file in bytes?
A. FileSize
B. FileLen
C. FileCheck
D. GetByte
Q5. Why should the function to get file size return a Double rather than a Long integer?
A. Double can store decimal values for more precise sizes
B. Double is faster than Long integers
C. Long integers are not supported in Access
D. Double uses less memory than Long integers
Q6. Why do we divide by 1024 when converting bytes to kilobytes, megabytes, and gigabytes?
A. Because 1024 is a perfect square
B. Because it is the standard in computer storage measurements
C. Because Access requires it
D. Because 1024 is the ASCII value for GB
Q7. What is the most readable way to present the file size to the user, according to the video?
A. In a Status Box message on the form
B. In a pop-up MessageBox
C. By sending an email
D. By printing the size to the immediate window
Q8. If you want to automatically check the file size whenever the database is opened, where should you place the check routine?
A. In the On Load or On Open event of the startup form
B. In the startup options dialog box
C. In the navigation pane
D. In the Windows Task Scheduler
Q9. When dealing with temporary tables and action queries in Access, what happens to database file size?
A. The file size increases until you compact the database
B. File size remains unchanged
C. File size is automatically reduced
D. File size is stored separately
Q10. What should you do if your Access database file regularly approaches the maximum file size limit?
A. Compact the database more frequently and investigate large tables
B. Upgrade to SQL Server Express
C. Buy a new computer
D. Ignore the warning
Q11. How can you future-proof your file size checking routine for multiple backend files?
A. Check the file size for every linked backend table automatically
B. Only check the main backend file
C. Exclude new tables from the routine
D. Convert all tables to local tables
Q12. What is the main advantage of writing your own function for checking file size in Access VBA?
A. It lets you easily check any file and customize warnings
B. It speeds up Access queries
C. It reduces the memory usage of Access
D. It allows more users to connect to the database
Answers: 1-A; 2-B; 3-B; 4-B; 5-A; 6-B; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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
Today's TechHelp tutorial from Access Learning Zone will show you how to monitor and prevent file size problems in your Microsoft Access database before they cause a crash. As many of you are aware, Access databases have a 2GB file size limit. However, Access does not provide clear warnings as you approach that threshold, which can lead to unexpected issues. This is a topic that comes up frequently for database administrators and developers.
Let me share a scenario. A Platinum member, Russell from Dublin, recently experienced a situation where no one in his office could log in to their Access database. The only message they received was that the database was either corrupted or not recognized. After spending almost an hour checking permissions and rebooting the server, Russell discovered that the backend file had grown to almost 2GB. Compacting and repairing the database reduced the size to 900MB, but now Russell is understandably worried about this happening again. He asked if there is a way to have Access check the file size automatically and notify him before it becomes a problem.
This is a good question, and the concern is valid. When an Access database approaches that 2GB limit, Access does not always provide an explicit or consistent error message. Sometimes it stops working altogether or displays vague messages about corruption. The actual cause - reaching the file size limit - is rarely made clear. Issues can show up in many forms: could not open the file, database not recognized, data not saving, or even odd behavior in forms due to unreadable tables. These types of messages do not immediately point you to file size as the culprit. Temporary data from processes like append or make table queries can also consume extra space, and unless you compact regularly, that space does not get reclaimed.
Because of these challenges, it is critical to compact your database on a routine schedule. I generally do not recommend using the "compact on close" feature, but running a weekly compact is a solid practice. Even so, it is wise to implement a way for your database to check its own size and warn you when it is getting too close to the limit. For example, after each compact operation, the database could check its file size and provide a notification if it passes a certain threshold.
To accomplish this, you can create a simple function in Access that checks any file's size. You just need to specify the path to your backend file or any other file you want to monitor. This approach will work whether you have one backend file or several.
Before we proceed, I should mention that this is a developer-level solution. If you are new to VBA programming, I recommend watching my introductory video on VBA for beginners. You should also look at my video on creating a status box for displaying small messages on your forms. Understanding how to work with variables and create your own functions in VBA will be necessary. If you have not made your own function before, I have a free tutorial on my website and my YouTube channel that covers this.
In the free TechHelp template database available on my website, I will guide you through creating a function that checks the size of any file and returns its size in gigabytes. The function will be located in a global module and can be called by any part of your database. The function receives the full file path as a parameter and returns the file size as a decimal value in gigabytes. For most purposes, you probably only need to know the file size to two decimal places; for example, 1.5 GB is sufficient information.
If you want a result in megabytes or kilobytes instead, the conversion is straightforward - you can simply change the division used in the calculation. In Access VBA, two main number types are typically used: long integers for whole numbers and doubles for numbers with decimal points. There are other types, but these two will cover almost all your needs for tasks like this.
The function uses VBA's built-in FileLen function to get the number of bytes in any file. After retrieving the byte count, you can convert it to gigabytes by dividing successively by 1024 three times. While it is common to think of kilobytes, megabytes, and gigabytes as multiples of 1000, in computing each step is actually 1024. This is based on the binary system rather than the decimal, so remember to use 1024 for conversions.
After converting the file size to gigabytes, the function rounds the result to two decimal places for readability. Now when you call this function with the file path to your backend database, you will get back a number like 1.6. You can then use this value wherever you like, such as displaying it in a status box or as part of an on-screen message.
For testing, you can place the call to the file size function inside an event handler for a button on your main menu. When you click the button, the database will display the current file size to you. As an example, my own message archive database file is around 600MB and contains twenty years of customer service emails.
You can choose to run this check manually, or better yet, have it run automatically every time the database loads. Simply call the file size check function in your form's OnLoad or OnOpen event. If the file size exceeds your desired safe threshold, you can trigger a notification, show a big warning, or even play a sound if that suits your style. The key is to automate the warning so that you have time to address the problem before the file reaches that 2GB ceiling.
If you have multiple backend files, you can call this function for each one. If you want to check the size of other files such as log files, it works the same way. It really comes down to passing the correct file path to the function.
The technique relies on the VBA FileLen function, which is the simplest way to check the size of any file from within Access. I cover additional file handling concepts in my Access Developer series. In lesson 31, for instance, I show how navigate folders, copy files, and even automate compacting backend files. Lesson 30 focuses on reading and writing text files, so there is a lot of depth available if you want to expand your knowledge.
In today's Extended Cut, I will show you how to create a method that automatically checks all the backend files for every linked table in your database. This is useful because you might add a new table in the future, link it to a new backend file, and forget to update your manual checking routine. With this method, the routine will scan all linked tables and verify the backend file sizes, making maintenance much easier and more reliable. If you add new tables or backend files, there is no need to remember to update your size-check code; it will catch everything automatically.
If you want access to the Extended Cut where I demonstrate these more advanced techniques and other extra benefits like the code vault, consider becoming a Silver or Gold member on my site. Gold members get access to downloadable databases and even more resources, while everyone can access plenty of free learning materials.
That wraps up today's TechHelp tutorial. Russell, I hope this addressed your concerns, and I hope this helps all of you avoid file size headaches in Access databases. You can find a complete video tutorial with step-by-step instructions on everything we discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Understanding Access 2GB file size limit
Recognizing vague error messages related to file size
Explaining how temporary tables can increase file size
Using compact and repair to reduce database size
Creating a custom VBA function to check file size
Using FileLen function to determine file size in VBA
Converting file size to gigabytes in VBA
Rounding file size output to two decimal places
Displaying file size on a form using a status box
Triggering file size check on form load or button click
Setting custom size warning thresholds in VBA
Checking multiple backend files for size
Integrating file size monitoring into Access workflow
Article
In this article, we are going to look at how you can detect and avoid database size problems in Microsoft Access before they crash your system. Access databases have a strict 2GB file size limit. Unfortunately, Access does not always warn you when you are getting close to that limit. If you have ever come in to find your Access app broken with vague messages about corruption or files not being recognized, there is a good chance the backend database has gotten too big. Usually, compacting and repairing the database will trim things back down, but this is not a problem you want to discover only after disaster strikes.
Access often throws generic errors as the file fills up, so it is not always obvious that the issue is file size. You might see messages about not being able to open files, tables or forms not working right, or it might just refuse to save data. It can also fill up with temporary data from action queries, such as when you create new tables or append lots of data. Unless you compact the file, that space is not reclaimed. Compaction should be a regular part of your maintenance routine—once a week is usually good enough for most databases, although you can adjust this to your workload.
It is very helpful to have a system that checks your file's size regularly and lets you know when it is getting close to the limit, so you can act before hitting disaster. Fortunately, it is quite easy to set this up using a little bit of VBA. You can even put this check into your nightly maintenance, or have it run automatically every time your database opens.
Let's walk through how to code a function that checks the size of any file you specify, such as the backend file for your Access database. The function will return the file size in gigabytes. You can then use this function to display a warning message in your app, letting you know when it is time to compact and repair.
First, create a new module, or open a global module in your Access database. Enter the following VBA code for the function:
Public Function GetFileGB(FilePath As String) As Double
GetFileGB = FileLen(FilePath)
GetFileGB = GetFileGB / 1024 ' convert to kilobytes
GetFileGB = GetFileGB / 1024 ' convert to megabytes
GetFileGB = GetFileGB / 1024 ' convert to gigabytes
GetFileGB = Round(GetFileGB, 2) ' round to 2 decimal places
End Function
This function uses VBA's built-in FileLen function, which returns the size of the file in bytes. By dividing by 1024 three times, you get the size in gigabytes. The function then rounds to two decimal places for a tidy display.
Now, let's look at how to call this function and display the result. Suppose you want to display the file size on your main menu form when the database loads, or show it when you hit a button. Here is a simple way to do so inside a button click event:
Private Sub YourButtonName_Click()
Dim D As Double
D = GetFileGB("Z:\\MessageArchive.accdb") ' replace with your file path
' Display the size in a status box or messagebox:
MsgBox "The backend database size is " & D & " gigabytes."
End Sub
If you have a custom status label or info box on your form, you can use that instead of a popup. A status box is less intrusive and can be updated automatically without stopping the user.
You can also wire this into your form's OnLoad event to run whenever the main screen loads. For example, you can create a procedure called CheckDBFileSize like this:
Private Sub CheckDBFileSize()
Dim D As Double
D = GetFileGB("Z:\\MessageArchive.accdb") ' replace with your backend path
If D > 1.5 Then ' Set your alert threshold here
' Show a warning wherever you want, for example:
MsgBox "Warning: The backend database is over 1.5GB. Compact soon!"
Else
' Optional: Show current status
' Me.StatusBox.Caption = "Database size: " & D & " GB"
End If
End Sub
Then, call CheckDBFileSize from your form's OnLoad event, or from anywhere else you want.
You can easily adapt this code to check multiple backend files if you have more than one. Simply pass each path to GetFileGB and handle as needed.
This approach is based on the FileLen function, which is the simplest method for checking file sizes without additional libraries or complex code. If you want results in megabytes or full bytes instead, just adjust how far you divide the file length.
To recap, regularly checking your backend database file size is a practical and proactive way to catch and prevent Access file size problems. Add this simple VBA function and alert into your regular workflow. With a little code, you'll know in advance when it's time to compact and repair, keeping your system running and your users happy.
|
