 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2008Force a limit to number of rows
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. 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. -- 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 > > 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 [quoted text clipped - 15 lines] > > -- 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 >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? > Jamie's approach ... > as Jamie mentions, is not supported or documented 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'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"... 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 [Quote] 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). *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. -- 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's approach... >> requires a bit more technical approach... [quoted text clipped - 62 lines] > > -- > Perhaps you misinterpreted my statement. > [quoted text clipped - 4 lines] > approach if you want to offer an application that folks can use without > undergoing serious Access training. 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. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.