How to Get a Warning When the Number of Records in a Table Reaches a Specific Limit

One of the main reasons people move from using a spread sheet to a database is because the volume of information they’re storing has outgrown what Excel is comfortably capable of handling. But moving to a database doesn’t get rid of that problem completely as database tables have limits for the number of records they can hold too. But how can you know when a table is nearing that limit? Or when it’s simply getting too big and some archiving of data is needed? This article will show you.

Size Limit of Access Tables

Whether you’re aware of it or not, Microsoft Access has a limit for how big any given table can get (you can find that limit by checking the official Microsoft site as it changes based on the version you’re using). For example, In Access 2000 a table can only hold up to 1 GB of info. While that might seem like a lot, it’s surprisingly easy to reach that level if you’re not careful (or if the field types in the table aren’t appropriately selected/sized).

So the question you should be asking yourself is not “will I ever reach that limit”, but instead you should be asking yourself “how should I make sure I know I’m approaching it well before it becomes a problem” – after all, it’s better to avoid a corrupted Access database rather than attempt to fix it.

VBA Code to Prevent Oversized Access Tables

One way to achieve this is to keep an eye on the number of records within the table, and that’s exactly what we’re going to do with our VBA code.

Firstly, let’s define what we want our code to do:

“Given a table name, display a message when the table reaches a given ‘warning limit'”

Using the code below, you will be able to display a warning message telling you when the table specified (using “TableName”) contains x number of rows or more (set with “WarningLevel”).

NB: I’ve included basic error handling to account for times when you may, for example, misspell the table name when calling the sub.

Public Function GetTableSize (TableName As String, WarningLevel As Long)
    Dim errCode As Long
    Dim dbs As Database
    Dim tdf As TableDef

    On Error Resume Next
    
    Set dbs = CurrentDb
    ' The following line selects the table definition using the name supplied - if there is an error (more likely due to misspelt table name, the code will jump to the error handler at the end of the sub)
    Set tdf = dbs.TableDefs(TableName)
    errCode = err.Number
    On Error GoTo BadTable
    
    If tdf.RecordCount >= WarningLevel Then
        MsgBox TableName & " has " & tdf.RecordCount & " rows, consider archiving some of this data", vbExclamation, "Table Size Warning"
    End If
    
BadTable:
    If errCode <> 0 Then MsgBox "An error occurred - did you use the right TableName?", vbCritical, "Unable to load table details"
    Set tdf = Nothing
    Set dbs = Nothing
End Sub

Code Explanation

Most of the code is self-explanatory. First we start by obtaining a reference to the current database (dbs), and then we attempt to open the definition for the table in question (tdf). If there’s an error selecting the table, it’s more than likely because the table doesn’t exist because the name supplied was mis-spelt. Now that we have a reference to the table definition, we can check how many rows are in the table using the “RecordCount” property which we compare against our “Warning Limit” and act accordingly.

Using the Code

You can naturally call this code from anywhere in your database – if you’re using Access 2000 or higher you can use the following workaround, otherwise you’ll need to create an AutoExec macro to run this code each time you open your database or add the event call to a form event.

Access 2000 and higher only

Microsoft introduced the ability to run specific actions against a table when, for example, records were inserted or deleted. Unfortunately running VBA code is not one of those actions, but you’ll be pleased to know there is a relatively easy work-around.

1. With the relevant table open, click on the “Table” menu bar.
2. As we’re monitoring tables for growth, it’s best to use the “After Insert” Event, so click on that
3. As we can’t use the usual “RunCode” action, we’ll use the “SetLocalVar” action instead so click on that option from the drop-down list of actions
4. The name you give this isn’t important, so call it anything you want – i.e. dummyVariable
5. In the Expression, you can enter the name of your Function above, along with the params – so, for example – if I were monitoring a “sales” table I would enter the following in the Expression field: GetTableSize(“sales”,100000)
6. Save the action and close it.

Now the code will run whenever you add new records to the table and you’ll be warned whenever the table reaches the set number of records – just what we want!

Author Introduction:

Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL Server error and excel recovery software products. For more information visit www.datanumen.com