Solved

Access Table Constraints Built using ADO

Posted on 2008-10-31
6
442 Views
Last Modified: 2013-11-29
My Cableroutings table models a many-to-many relationship.  However, I am trying to constrain this relationship to limit the number of occurrences of any given Cable to a maximum of 2 occurrences.  (Basically a cable can only be routed to two enclosures).

There is a problem.  My 'CHECK_ONLY_2_ROUTES_FOR_CABLE' doesn't work as I've defined it.  It only allows me to create two records in the entire table.  This isn't what I want.  

Does anyone know how I am reformulate my check constraint to achieve the desired functionality?
--Table In question
CREATE TABLE Cableroutings (
    areacode TEXT( 4 ) NOT NULL,
   enc_class TEXT( 3 ) NOT NULL,
     enc_nbr INTEGER NOT NULL,
      prefix INTEGER NOT NULL,
   cabletype TEXT( 2 ) NOT NULL,
      suffix INTEGER NOT NULL,
 
  CONSTRAINT FKEY_Enclosures_Cablerouting
 FOREIGN KEY ( areacode, enc_class, enc_nbr )
  REFERENCES Enclosures( areacode, enc_class, enc_nbr )
   ON UPDATE CASCADE,
 
  CONSTRAINT FKEY_Cables_Cablerouting
 FOREIGN KEY ( prefix, cabletype, suffix )
  REFERENCES Cables( prefix, cabletype, suffix )
   ON UPDATE CASCADE );
 
--Access doesn't like CHECK constraints declared in Table Definition
--But declaring them with ALTER TABLE ADD ... works just fine.
     ALTER TABLE Cableroutings
  ADD CONSTRAINT CHECK_ONLY_2_ROUTES_FOR_CABLE
  CHECK(( SELECT COUNT( * )
            FROM Cableroutings AS CR
           WHERE CR.prefix = prefix
             AND CR.cabletype = cabletype
             AND CR.suffix = suffix ) BETWEEN 0 AND 2 );

Open in new window

0
Comment
Question by:rabroersma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:LennyGray
ID: 22851598
You may want to consider redesigning the table for the Cable and include the following fields:

CableID as Long Integer
CableName as String
CableEnclosureDestinationOne as Long Interger (holds the index of an enclosure)
CableEnclosureDestinationTwo as Long Interger (holds the index of an enclosure)
.......other data fileds
0
 
LVL 1

Author Comment

by:rabroersma
ID: 22851953
While this is true, I am a big fan of natural keys.  I don't want to have this question to get off topic.  But I can achieve anything with a natural key design that I can do with a surrogate key design.  However, the converse of this is not true.  Using a natural key design I can impose data validation constraints using ordiary DDL statements that are not possible using a surrogate key design.

My opinion is that the table constraints should be the final safety net that are in place to ensure that all applications based upon them _cannot_ violate business rules.  Using Natural Key designs allow me to do this and this is why I like to use them.

But in either case, using a surrogate key will not help with my problem.
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22852670
You could establish a special constraint table that contains only CableID and CountOfEnclosureID. This table would be incremented and decremented upon stored procedures for insert or delete events. In the afterinsert event, if the count is greater than 2, you would raise a dialog and rollback the event.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:rabroersma
ID: 22853272
That is an interesting idea, I hadn't ever considered doing this.

You mentioned that stored procedures could be used.   Do you have a documentation links that describe how to build these in Access?

Also, would these procedures need to be called from a form, or is there away in Access to implement as a trigger on affected tables? (Does Access support triggers when implemented from ADO?)
0
 
LVL 10

Accepted Solution

by:
LennyGray earned 500 total points
ID: 22853400
The stored procedure would reside in your sqlServer table on the field level.

If you do not want to do it in SqlServer (which is the best place), then you can place the logic on the forms that add or delete data.

Calling a store procedure from Access is managed here:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22741094.html?sfQueryTermInfo=1+access+call+procedur+store

or

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22674876.html?sfQueryTermInfo=1+access+call+procedur+store

or

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22636094.html?sfQueryTermInfo=1+access+call+procedur+store

You can also go to Experts Exchange .... Home, Find Your Solution, Search The Knowledge Base and type in "access sqlserver stored procedures" (without quotes) and you will find many solutions.

Access does not support triggers. Events are the customary way to emulate triggers. You could use the BeforeInsert event on the form to check if the Constraint Table count goes over 2 and raise the dialog there. You can use the OnInsert event to increment the table and OnDelete to decrement the table.

If you use these events, remember that if anyone circumvents the form and directly enters data into the tables, the constraints that you are designing are vulnerable. Putting the constraint in SQLServer on the record level stops even data entries that circumvent the form.


Good Luck!!!
Lenny Gray

0
 
LVL 1

Author Closing Comment

by:rabroersma
ID: 31512103
While the answer works. I would still like to know how to correctly implement table sub-select check constraints in MS Access tables.  The SQL Standard defines this functionality and MS-Access via ADO says that It can implement it.  However I have yet to see it successfully work.
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question