How can I limit the number of records in a table, not a query?

[nibroc]

I can find numerous methods to limit records in a query, but how can I limit
the number of records in a table.

 

[John W. Vinson]

I can find numerous methods to limit records in a query, but how can I limit
the number of records in a table.

One sneaky way is to use a Long Integer primary key with a validation rule,
e.g.

0 AND <= 100

will allow only 100 values for the field; since the primary key must be
unique, you can't have any duplicates.

 

[TedMi]

John's method will limit the VALUE of a PK, but not the number of records.
For example, if you have records with PK's of 1-100 and delete 50 of them,
you still won't be able to enter any new records because the next one will
get a PK > 100.
Another way: In the BeforeInsert event of the form that inputs new records,
check the DCount for the table and cancel the insert if DCount=100. However,
events only work in forms, not in tables. Thus if the user can open the
table directly, records can be inserted without firing the BeforeInsert
event.

-TedMi

 

[Douglas J. Steele]

John said "use a Long Integer primary key", not "use an AutoNumber primary
key"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John's method will limit the VALUE of a PK, but not the number of records.
For example, if you have records with PK's of 1-100 and delete 50 of them,
you still won't be able to enter any new records because the next one will
get a PK > 100.
Another way: In the BeforeInsert event of the form that inputs new
records, check the DCount for the table and cancel the insert if
DCount=100. However, events only work in forms, not in tables. Thus if the
user can open the table directly, records can be inserted without firing
the BeforeInsert event.

-TedMi

 

[Michael Gramelspacher]

I can find numerous methods to limit records in a query, but how can I limit
the number of records in a table.

I think this would work:

With CurrentProject.Connection
.Execute _
"ALTER TABLE Table1 ADD CONSTRAINT record_limit_is_100 " & _
"CHECK ((SELECT COUNT(*) FROM Table1)<= 100);"
End With