Limit the rows in Access table

StewartBW 825 Reputation points


Is it possible to limit the number of rows in each Access mdb database table?

Will need to apply the limitation via VB.NET OLEDB and using Access database engine.


A family of Microsoft relational database management systems designed for ease of use.
336 questions
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,655 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
852 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiachen Li-MSFT 28,781 Reputation points Microsoft Vendor

    Hi @StewartBW ,

    Access itself doesn't provide a built-in feature to directly limit the number of rows in a table, you can implement this functionality through your application logic in VB.NET.

    You can check the current number of rows in the table before inserting new data.

    If the table has reached the specified limit, consider deleting the oldest rows to make space for new data or canceling the data insertion operation.

    Best Regards.

    Jiachen Li

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom van Stiphout 1,701 Reputation points

    MDB??? We last used that 21 years ago with Access 2003.
    Assuming you meant ACCDB or can upgrade to it, you can create a Data Macro to enforce a row limit. That DM would fire any time you insert a row, similar to a trigger in SQL Server.

  2. Ken Sheridan 2,756 Reputation points

    You can also do this by applying a CHECK CONSTRAINT to the table, e.g. to limit a table Contacts to 100 rows:

    CurrentProject.Connection.Execute "ALTER TABLE Contacts ADD CONSTRAINT MaxRows CHECK((SELECT COUNT(*) FROM Contacts)<=100)"

    Note that in Access this must be done in code. The DDL statement cannot be directly executed in the query designer.

    To remove the constraint the code would be:

    CurrentProject.Connection.Execute "ALTER TABLE Contacts DROP CONSTRAINT MaxRows"

    0 comments No comments