Force a limit to number of rows

  • Thread starter Thread starter Rich K
  • Start date Start date
R

Rich K

I want to limit the number of rows in a table for demonstration purposes. I
seem to recall that this can be done in the advanced properties of the table
in the validation Rule but I can't recall the syntax. Can anybody share a
hint on how this is done?
 
I want to limit the number of rows in a table for demonstration purposes. I
seem to recall that this can be done in the advanced properties of the table
in the validation Rule but I can't recall the syntax.
Click to expand...

I'd suggest you define a Jet CHECK constraint on the table e.g.
something like:

ALTER TABLE MyTable ADD
CONSTRAINT we_know_where_you_live
CHECK (3 >= (SELECT COUNT(*) FROM MyTable AS T2));

The Access user interface does a stupendous job of hiding CHECK
constraints from users, to the point where Microsoft haven't even
documented them in the Help (or anywhere else, in fact), and should
baffle most casual users ;-)

Jamie.

--
 
Rich

If you are working directly in the table (not a good idea in Access),
Jamie's approach will serve (and in fact, would server no matter how your
users were entering data). It requires a bit more technical approach and,
as Jamie mentions, is not supported or documented, so you (or your
successor) may have trouble figuring out what was done.

Another approach, perhaps less technical, but perhaps more "discoverable",
would be to use a form to mediate data entry (this is the preferred method).
Then, in the form's BeforeInsert event, you could create a procedure that
does what Jamie described, count the number of existing records.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jamie's approach ...
as Jamie mentions, is not supported or documented
Click to expand...

Not quite :)

CHECK constraints were promoted as a new feature of Jet 4.0 and there
are a few KB articles that *mention* them e.g.

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/kb/201888/EN-US/

ACC2000: How to Create a Jet CHECK Constraint
http://support.microsoft.com/kb/201888/EN-US/

So CHECK constraints are definitely *supported*.

When I say they are not documented I mean that Microsoft haven't
provided any *details* of interest to a developer (a spec, for
example).

Jamie.

--
 
Jamie,
Thanks for the help, this is exactly what I needed.

As a matter of fact, I decided to go ahead and create a little tool so I can
enable and disable the constraint, which is better than what I expected to be
able to do.

Rich
 
Jamie's approach...
requires a bit more technical approach...
so you (or your
successor) may have trouble figuring out what was done.

Another approach, perhaps less technical, but perhaps more "discoverable"...
Click to expand...

Interesting point. I'm no Access interface expert, so I ask myself,
"What would Allen Browne do?" See his article about SET NULL
referential actions:

http://allenbrowne.com/ser-64.html

But what if someone else needs to rebuild the database at some stage?
Since the interface cannot show them that cascade-to-null relations
are in force, they may recreate the tables and have no idea that your
application relies on this type of cascade. You need a way to document
this, and ideally it should be visible in the Relationships window.

Create a table purely for documentation. The table will never hold
records. To ensure it shows in the Relationships window, create a
relation to other tables, so it is not only saved in the Relationships
view now, but shows up when the Show All Relationships button is
clicked.

The field names can be anything, but since the goal is to catch
attention, you might create a sentence using odd names reserved words:

Field Name Data Type Description
* * * WARNING * * * Text Informational only: no data.
Cascade Text
to Text
Null Text
Relations Text
Exist Text
On Text
Products Text
And Text
Categories Text
Id Number Primary key

[Unquote]

I really can't say whether that's a good idea or not (personally, I
advocate good documentation *external* to the database itself e.g. the
data dictionary) but my point is that inherent lack of visibility (or
"discoverability" if you will) in the Access interface doesn't faze
Allen Browne a.k.a. "The Access MPV's Access MPV" :)
Another approach, perhaps less technical, but perhaps more "discoverable"
would be to use a form to mediate data entry (this is the preferred method).
Click to expand...

*Your* preference, maybe <g>. I prefer (and many other professionals
do) to enforce simple data rules in the database itself using table
constraints, rather than maintaining the same logic in every element
of every application that will ever access the data. Just as you think
in terms of a successor developer inheriting the application, I think
in terms of a successor application inheriting the database.

Jamie.

--
Click to expand...
 
Jamie

Perhaps you misinterpreted my statement.

I was pointing out that Access forms are for displaying (add/edit/...) data,
while Access tables are for storing data.

Working directly in the tables (i.e., data entry) is not the preferred
approach if you want to offer an application that folks can use without
undergoing serious Access training.

JOPO (just one person's opinion), like yours

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jamie Collins said:
Jamie's approach...
requires a bit more technical approach...
so you (or your
successor) may have trouble figuring out what was done.

Another approach, perhaps less technical, but perhaps more
"discoverable"...
Click to expand...

Interesting point. I'm no Access interface expert, so I ask myself,
"What would Allen Browne do?" See his article about SET NULL
referential actions:

http://allenbrowne.com/ser-64.html

But what if someone else needs to rebuild the database at some stage?
Since the interface cannot show them that cascade-to-null relations
are in force, they may recreate the tables and have no idea that your
application relies on this type of cascade. You need a way to document
this, and ideally it should be visible in the Relationships window.

Create a table purely for documentation. The table will never hold
records. To ensure it shows in the Relationships window, create a
relation to other tables, so it is not only saved in the Relationships
view now, but shows up when the Show All Relationships button is
clicked.

The field names can be anything, but since the goal is to catch
attention, you might create a sentence using odd names reserved words:

Field Name Data Type Description
* * * WARNING * * * Text Informational only: no data.
Cascade Text
to Text
Null Text
Relations Text
Exist Text
On Text
Products Text
And Text
Categories Text
Id Number Primary key

[Unquote]

I really can't say whether that's a good idea or not (personally, I
advocate good documentation *external* to the database itself e.g. the
data dictionary) but my point is that inherent lack of visibility (or
"discoverability" if you will) in the Access interface doesn't faze
Allen Browne a.k.a. "The Access MPV's Access MPV" :)
Another approach, perhaps less technical, but perhaps more "discoverable"
would be to use a form to mediate data entry (this is the preferred
method).
Click to expand...

*Your* preference, maybe <g>. I prefer (and many other professionals
do) to enforce simple data rules in the database itself using table
constraints, rather than maintaining the same logic in every element
of every application that will ever access the data. Just as you think
in terms of a successor developer inheriting the application, I think
in terms of a successor application inheriting the database.

Jamie.
Click to expand...
Click to expand...
 
Perhaps you misinterpreted my statement.

I was pointing out that Access forms are for displaying (add/edit/...) data,
while Access tables are for storing data.

Working directly in the tables (i.e., data entry) is not the preferred
approach if you want to offer an application that folks can use without
undergoing serious Access training.
Click to expand...

Access means many things to many people e.g. is it a forms-based RAD
development platform for simple data-centric applications or is it a
management studio for Jet databases? This is the 'tables' group and
the OP didn't mention Forms, therefore I think a Jet solution is a
fair assumption. BTW I think 'Access tables' is stretching definitions
a little too far ;-)

When testing for uniqueness of a key's values, do you do this in a
Form or do you use a PRIMARY KEY or UNIQUE constraint on the column(s)
in the table or do you do it in both places? The implementation
required is so simple in the table that I can't really see a good
reason for doing it in the Form at all.

BTW back on the discoverability issue, if you prefer you could add a
required (NOT NULL) column (though personally I avoid adding columns
if no such attributes exist in the reality being modelled) of type
integer with a Validation Rule such as:

BETWEEN 1 AND 3

Jamie.

--
 
Back
Top