How To Enforce Relationship Integrity Of SQL Tables From MS-Access?
Feb 12, 2005
Hello everyone. I'm not sure should I post this question here or at SQL Forum? However, my problem is this. I have 2 Tables, Table1 has AutoNumber as primary key, my second Table2 has index key field. I have one-to-many relationship on those two fields, with enforce referential integrity, cascade update and delete
The access program works fine until I upsized to SQL server. [u]Then I was unable to perform cascade update or delete. I have check SQL table (which I have little knowledge about) and seems to be okay, the relationship exists. But at ms-access I was unable to make the cascade update and delete.
tblDateReason DateReasonID(pk) EntryNumber(fk) Date Reason Entered
tblEmpMain EntryNumber(pk) EmployeeNumber Etc//
I have a 1 to many setup between tblEmpMain and tblDateReason.
When I try to setup the relationship between the "reason" field in tblDateReason and luTblReason, Referential Integrity along with the other two check boxes are greyed out.
It still shows one to many but it will not let me enforce referential intergrity.
Why is this? Did I setup the database wrong? Or am I trying to do something I shouldn't be doing??
I have attached the db.. It is split with both the backend and the front end.
Please see attached word document with a screenshot of my tables and their relationships. I'm trying to link from Table:Line,Field:Line to Table:Shift-Line and similarly with the shift table and access is not able to maintain referential integrity.
Can anyone explain why? and generally what I've done wrong with my relationships please?
I opened the relationship window to do the relationships among my tables; however, the check box 'Enforce Referential Integrity' is disabled. I don't understand why is that? And is there anyway to change it?
Also, where should relationships be implemented? is it the FE or BE? Any help will be very much appreciated. B
I am new to access and I am trying to set a relationship between two tables, with the data types the same but keeps coming up with violates referential integrity rules....and I simply do not understand what this means, and how to solve it...
Does anyone know if this is possible and is there an easy way to set it up?
My db has many relationships, hence if you attempt to delete a record from the table, it will not allow it if there are related records.
This is great, however.
My intention is to disable any record deletions. Instead, once a record is no longer live, the status of that record is set to disposed,exipired, etc.
I want to force the same rules as if attempting to delete this record but set to the status field.
Example message. ie, Warning! You cannot dispose of this PC. There a related records in the Software License and Contracts Tables. Please reassign them and try again!
Being as how I'm new in using Access I need some guidance. I have three tables I've set up my relationships as follows:
tblPatient tblContact tblPhone PatientID PK ContactID PK PhoneID PK PatientAccount FK PatientID FK PatientID FK PatientFirstName ConatctFirstName ContactID PatientLastName ContactLastName DOB Address City State Zip Intial Visit
I have set up a 1 to many between tblPatient PatientID and tblContact PatientID.
and a 1 to many between tblContact ContactID and tblPhone ConatctID
I have given each table referential integrity, to mine understanding in short it means change it in one table it changes in all tables. Please let me know if I'm headind in the right or wrong direction the way I have this set up.
I'm not sure if I am heading down the right path here. I have searched the forum but not come up with anything on the subject of deleting relationships and RI.
I have a project assignments junction table that links project and personnel tables. I want the employee details in tbl_prj_assignments to be fixed so that they are not updated by changes in tbl_Employee_details. This is so archived records will always reflect the personnel who actually worked on them rather than the people currently in that position or if they have left the company.
I had referential integrity from both parent tables to the junction table in the back end database. The project relationship remains unchanged so that any project changes or deletions removes all the related assignments.
I tried to remove the RI from the relationship between tbl_Emp_Details and tbl_Prj_Assignments by unchecking the 3 boxes for Enforce RI, Cascade update, Cascade delete. On testing I found that the junction table was still updating with changes to the employee table.
Next I tried deleting the relationship altogether with the same result. So now I am left with referential integrity being enforced where I cannot see any relationship. :confused: :confused:
The fields in the tables are all text boxes - no lookup. The junction table is populated by a subform based on the table and located on the project details form. The subform uses a combo box with an SQL statement to select the employee.
Questions:
1) Firstly, is this the best way to achieve the desired outcome?
2) Where is the referential integrity being enforced, and can it be removed?
Enforce referential Integrity and Cascade Update Related Fields
Thus, it is possible to change the name of the state or city, but can not be deleted until the state is associated with some of the city.
I also made a form for the "state" in which is the list that contains a list of all states. The name of the list is "lstState". So that I can delete the "State" I make a button in form and I use the following code:
Code: Private Sub DeleteState_Click () If IsNull (Me.lstState) Then MsgBox "Select the state you want to delete", vbCritical else DoCmd.SetWarnings False
[Code] ....
Everything works fine when the state is not assigned to any one city. But the problem arises when trying to delete a state which is assigned to the city, that is when I select this state and click on the delete button then the state is not deleted - this is ok, but without any message told why the state is not deleted and that's the problem.
My question is how to make the code that the user receives a message that such State can not be deleted because there are cities that are associated with it.
i have imported 5 excel files and linked to a query named stock.i have linked the code,description and quantity from each table (ms excel imported) to stock. the problem is not all the codes available in the tables r available in stock.there r more than 25000 codes but only 2614 r listed in stock.i dunno whether its the problem with the way i have linked.so if u can help me with the problem i will be really thankful. 5 excel sheets contains the code,description and qty of 5 shops.in the query stock contains 1st column-id,2nd column-code,3rd column-description and 4th,5th,6th,7th,8th column the qty from 5 excel sheets respectively.
Field 1: Group Text field ( Primary key) Field 2: Group Description Text field Field 3: Uidgroup( Autonumber)
Table 2: Subgroup Text field
Field 1: Group ( I want to bound this column to Table 1's Group field that is column 1) I have set bound column property to 1 and column count 1 and the Subgroup table is showing group fields as input perfectly no issues in that )
Field 2: Subgroup, Text field( Primary Key )
Field 3: uidsubg( Autonumber)
Table 3: Email
Field 1: Group ( Bound to Table1's Group ; showing values in combo box, setted bound column property to 1 and showing group field perfectly, no issue in that )
Field 2: Subgroup (I want to bound Table2's subgroup field, which is column number 2, so I wrote 2 in bound column property and row source is table subgroup ; Here is some error comes up, values from subgroup field of subgroup table not being shown up in Email Table's subgroup field as combo box. )
Field 3: Email Text field
Field 4: uideml (Autonumber ) primary key
I want to prepare a Data entry form should have all these fields from all the tables. That should work in following way, first user selects Group then User selects Sub Group and write Email and save the record.
What relationship should I set, or shall I change the table structure.
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
Just a quick question, it appears that I can't Enforce RI for relationships where one of the tables is linked from another *.mdb (the checkbox is greyed out)... is this a general thing with linked tables?
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
I have converted a database from 97 to 2002 and now i have getting two errors which are concerned with the relationship. In Access 97 application the relationships are working well but after the conversion some relationships between the tables are broken and now i cant establish the same relationships between the broken tables. I get the erros message
Microsoft cannot establish this relationship with referential integrity
In table X are some recvords which violate the rules of referential integrity.
I wonder the same relationship works in Access 97.
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
Table 1: contains staff names and contact numbers Table 2: contains training above staff have been on or need to go on Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
Does anyone have a good technique for enforcing a minimum number of records in a table?
I am attempting to ensure that in a table of Roles, there be a minimum of two Roles defined as Technicians. The field which identifies a Role as Technician is a Boolean.
I wrote a Function which does a domain lookup to count the number of Roles defined as Technician and am calling it from the BeforeUpdate event.
However, if there are 2 Technician Roles, and I mark a third Role as Technician, change my mind and try to unmark it...of course the Function can still see just the 2 existing.
I realize I can Undo the edit, but a user may not.
I have a form based on a query. In this query I sorted the columns year and month in a chronological order. However, on the form this does not appear that way on the form. How can I enforce this? Thank you Stacey
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
I am trying to define a relationship between tables. However, the unique nature of my data doesn't seem to be allowing typical relationships. I am not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. Do I need a Foreign Key? FYI, I am only querying the data - no updates.
I have two tables that I am able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables. For example:
Table1.ColA has a 5 character string. Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.
IMPORTANT FACTORS: - Table1 contains data at a SYSTEM level. - Table2 contains the parts that make up the "SYSTEM" in Table1 - The PARTS in Table2 can be in 1 to many SYSTEMS from Table1 - Table1.ColA is not unique by itself (it's part of a composite PK in Table1). - Table2.ColA is not unique by itself (it's part of a composite PK in Table2).
I have a db with two table. Table1 Trade has the following among other fields: RefNo (autoNo) PK, TradeDate, HostName, Methodology, etc.
Table2 Market has the following among other fields: MarketDate as Date ( ), USD/EUR_Rate, USD/CHF_Rate, USD/BRL_Rate, etc.
I need to link both tables so that the TradeDate correspondes to the MarketDate. what is that say on TradeDate if it is on 15/01/06 I can see what the Market values for the MarketDate corresponding to that TradeDate.
I tried to link MarketDate as PK on Market table2 to TradeDate on Trade Table1. It seems to work but it does not seem right as sometimes I get errors in entering data, etc on the Market table2. The MarketDate is unique meaning only one data of values for a given date, they can be modified but not duplicated.
Can anyone help, on the best solution for the links to work?
I feel confused about something and I have the need for assistance because solving this small thing would make my life easier. I very much appreciate you taking the time to read this.
I want to normalize my database as much as possible.
I will make a make a form for Breakpoint containing two comboboxes. The top combobox will contain all Modules and I want the second combobox to contain only the Functions that are related to the selected Module from the first combobox.
I know this can be programmed, that's how I do it now: in the onChange-event of the first combobox, I load the content of the second combobox.
However: what I would like to know specifically 3 things: --> what is a common way to solve this? --> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically) --> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?
