Limit the rows in Access table

Question

Hello

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.

Thanks.

Answer 1

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.

Answer 2

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.

Answer 3

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"