N
nibroc
I can find numerous methods to limit records in a query, but how can I limit
the number of records in a table.
the number of records in a table.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
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.
T
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
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
D
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!)
key"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
TedMi said: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
M
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