Link to home
Start Free TrialLog in
Avatar of BobRosas
BobRosas

asked on

Referential Integrity

I have an access database that's connected to tables in SQL Server 2005.  I've been getting Error 3101 (Access database engine cannot find a record in the table tblServices with key matching field(s) tblClientServices.ServiceID)
I couldn't figure out why so I recreated just the portion causing the error with Access tables and it works fine.  The only thing I can see that's different is the table linking.  
I've attached a jpg with what works (1st one) which one doesn't (2nd one) and settings I have in SQL.  Can you tell me what I need to change to make SQL work like Access?
Thanks

Referential-Integrity.JPG
Avatar of dqmq
dqmq
Flag of United States of America image

First, the tables in the diagrams don't match the tables in your error message, so that's very confusing.

Second, it's undoubtedly an RI error on the SQL Server side.

Third, the RI is a design element most likely added by the database designer to avoid data corruption (and save your butt!).  Said another way, I really doubt you want SQL to behave like Access and permit you to screw up the data.

What you should do, is go to the Access relationship dialog and check the "Enforce Referential Integrity" box so that Access behaves like SQL.

The goal is to prevent adding a client service for a non-existent service.  Can you imagine the problems that might cause?  The solution is to add the service before associating it with a client.
Avatar of BobRosas
BobRosas

ASKER

I think from your response that the labeling on my pictures was not right because the pictures where "Enforce Referential Integrity" is checked is the working Access side.  The picture where it is greyed out and I can't change it is the SQL side.  I'm not surprised that I can't change it in Acess but I don't know how to change it in SQL so that it shows there is Referential Integrity.  I'd really appreciate it.  And also thanks for your quick response.
The main confusion is that the error message references tblServices and tblServices.ServicesID , but neither of those table names nor the column name shows up in the diagrams.
You are right again...I aplogize.  I have two subforms, 1 = fsubServices and 1 = fsubFunds.  I get a similar message for both.  Ex: Funds gives me the message...
(Access database engine cannot find a record in the table tblFunds with key matching field(s) tblClientFundSource.FundsID)
I hope this clears up the confusion.

 
Another point of confusion:

The relationship shown in the second diagram (and the SQL Server setting) involves the client table, not the funds table.  It is not the relationship that is producing the error.

Basically, I take the error message at face value:  that whatever fundID you are trying to add to tblClientFundSource, does not exist in tblFund.  Are you certain tblFund is linked to the right table?
I've attached more print screens in hopes it will help. tblClientFundSource and tblClientServices are both Many to Many tables...and the only place I'm having a problem.    I totally agree with you about taking the mesage at face value.  Since I recreated the bare basics in Access and it's fine until I link to SQL I don't know what else to think.  I'm very new to SQL so there may be basic code I need that access doesn't to make it work.  I just don't know what that is.
I'd be glad to send you the working stripped down access version if that would help.  But there is no error unless it's linked to a SQL database.
I really appreciate any help.
Thanks!
Referential-Integrity2.JPG
Now we're getting somewhere.  The FK shown is cross-wired (my term, actually it looks like a copy/paste error from another FK constraint).    As shown, the FK constraint tells SQL server to make sure tblClientFundSource.ClientFileNo is found in tblFunds.FundID

In the above properties table, I would expect to see this:

 Foreign Key Columns     FundID

Not this:

 Foreign Key Columns    ClientFileNo



I'm so glad you found something!  I wasn't sure how to change it so I deleted the link and re-created it and it fixed itself.  Unfortunately even tho I relinked I'm still getting the same error.  Since I'm new to SQL could you tell me what my Update Rule should be?  Right now it's set to "No Action".  I thought maybe that was related.  I get the error on add....but it's only if I change an existing record.  I can just start trying all the options but there are several options and I have 2 sets of 4 tables so the combination of choices could take a while.
Thanks for sticking with me!

The update and delete rules are fine.  The error occurs on Insert, right?

You need to fix the problem on the SQL Server side.

Run this script (after replacing [YourDatabase] and [dbo] with the correct values:

USE [YourDatabase]
GO

ALTER TABLE [dbo].[tblClientFundSource] DROP CONSTRAINT [fk_tblClientFundSource_tblFunds1]
GO

ALTER TABLE [dbo].[tblClientFundSource]  WITH CHECK ADD CONSTRAINT [fk_tblClientFundSource_tblFunds1] FOREIGN KEY([FundID])
REFERENCES [dbo].[tblFunds] ([FundID])
GO
FYI - The error is consistent but 2 things have to take place.
If I just add/insert a record it works just fine.  That's why it's taken me so long to figure out the pattern.
For the error to happen I have to...
1) Make a change to an existing record
2) Try to add/insert.
Thank you so much for the code.  I will try that next.
>If I just add/insert a record it works just fine.

If so, it's just a coincidence (because some FundID's happen to match the ClientFileNo you are trying to add). Your foreign key constraint says that ClientFileNo, must be a FundID. I believe that is wrong.  I'm guessing you have probably have another FK constraint that says ClientFileNo must be a ClientFileNo.   That is correct.  The  one in question should say FundID must be a FundID.

Make sense?
Please don't say that....This database has been in implementation since Jan 1 and the users have not complained about bad data, only this error.  As long as they exit and re-enter the program they can continue on.  I will max the points and answer any questions that I can if you can help me with this.  Now I'm very concerned.

Referential-Integrity3.JPG
Now, those RI constraints look fine!  

If feels like I'm standing on quicksand; what happened to FK_tblClientFundSource_tblFunds1 (note the trailing '1' in the FK name) that I debugged a couple of frames back?  

Thank goodness!  Of course it sounds like I'm back to not having a solution to my problem.  But in this case the alternative would have been much worse.
As for the name change... I'm honestly not sure when/how I changed it to Fund1 but  when I noticed it I just renamed it thinking it was just a label name and not a table name.  I didn't relaizing how it might affect your troubleshooting.  I still have the problem after I changeed it back so I don't know where to go from here.  I do apologize for making this tough on you.  I really appreciate all your help.  Is there any other info I can provide?  
Changing the name of the FK constraint has no effect (except to confuse me).  However, the FK column also changed (for the better) and that is very important.  

I want you to put the constraints back the way they were in the referential-integrity_3.jpg. Those look good to me.  

Then reproduce the problem and show me the error message and the SQL that produces it.  
I checked all my settings and they currently match those from the referential-integrity_3.jpg.
I'm also sending a print screen of the error message.
My Steps were
I changed the date for the Fund Source CSI
Then I added 'DDS' and '1/1/2001' and as soon as I tried to leave that record I got the message.
Then I select  'OK'  on the Error msg, hit Esc (which clears the DDS 1/1/2001 rcd) and I can close the program.
Would it help to tell you that I can also create the error if I....
1)Change the date for any existing Fund (for Client #1 for example)
2)Then go to the Client tab and select Client #2
3)Then go to Fund/Service tab and try to add Fund for Client #2.  
This will give me the same messge.  
FYI - The changed field on Client #1 is successful.

Error.bmp
The error message refers to field "ClientFundID".  But I do not see that field in any of your tables.  So, this is beginning to look like something besides a constraint violation.

Can you show the code the produced that error message?  I expect it's in the After_Update event.  You can probably jump right to it by pressing ctrl-break when you get the error message.
I think we're close...maybe it's my record source.  I've attached a picture of the query I'm NOW using with the current error message.  Since both tables in my query use the field FundID I renamed them.  For example FundId for dbo_tblClientFundSource was named ClientFundID:FundId.  I changed that and ran it again and posted the error.  I tried the query without that field and it says "Field cannot be updated." ...if I say OK it seems to take the Fund and it lets me enter a date.  However when I reopened the database it was not there.  Next I'll start looking to see if it put the data somewhere!!!  In the mean time if you have any suggestions that would be great!
Error2.JPG
FYI - The records I added were saved to tblFunds but not to tblClientFundSource.  Which explains why they don't show when I reopen the database.  I sure hope this gets us closer...thanks again!
When you add a new Fund Source record, where does the value for tblFunds.FundID come from and how does it get itno tblClientFundSource.FundID?

tblFunds.FundID is an autonumber key.  And as near as I can tell by adding tblClientFundSource.FundID to my query and linking it to tblFund it was getting updated automatically.  I wasn't doing anything...maybe I should be?
>And as near as I can tell by adding tblClientFundSource.FundID to my query and linking it to tblFund it was getting updated automatically

That is the source of your problem; SQL Server isn't aware of the relationship in your query and Access isn't aware of autonumber that gets generated by SQL Server.   Catch-22.

Next question:  where do you expect the value for tblClientFundSource.ClientFileNo comes from?
I'll take a wild guess and say the same as tblClientFundSource.FundID...a linking thing?  I really don't know.  When I was testing, and I changed all the key fields so they were visible they just filled in automatically...again I didn't code for it.  I was really hoping it was that easy.
So if
>SQL Server isn't aware of the relationship in your query and Access isn't aware of autonumber that gets generated by SQL Server"
Now what can I do?  It just seems like most of it works just the way it is...please tell me there's a fairly easy way to fix this.  
Thanks again!
The recordsource query that you showed me appears to return rows for all ClientFileNo's.   Something must be making it return just the funds for one ClientFileNo  (presumably the one shown on the form above the FundSources list.

Can you describe how the FundSources list is restricted to just the FundSources for one ClientFileNo?  
Funds and Services are 2 separate subforms and I have the Link Master Fields and the Link Child Fields both set to ClientFileNo for each of the subforms.

I hope that's the answer you are looking for.  If not I'll keep trying.  Thanks!

Some good news... I made a little application like yours and it works great!  So, at least we know it can be done.

But, I'm still puzzled about why yours is not working.  At this point, I have two suggestions.

First, double check your master-child links to make sure they are correct.  I think you should be linking the master to the child on ClientFileNo only.  

Second, add textboxes for Funds.FundID  and ClientFundSource.FundID to your left most subform.  Then observe what appears in those fields before/after the error.
That is good news.  Thank you so much for going to all that trouble.  
I guess I'll be doing the same.  I checked my links which seems fine and I'm still having the problem.
I also watched the key fields and even if there is no error...those fields don't fill until I leave the record.  If there is an error I can't leave the record.  So that didn't help me much.   By creating a new database from scratch I'm hoping I don't reporduce the error.
I'll keep you posted...thanks again!
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you VERY much!  I apologize for not responding sooner but I was gone a couple days.  As soon as I put out a couple fires I'll try to figure out what I need to do next based on the info above.  I was so hoping for a much easier fix but one way or another I need to fix this and move on...I'm sure you are thinking the same thing.  I really do appreciate all the time you've taken.  
Again I apologize for the delay.  Your comments and suggestions have been very helpful and I will just go ahead and award points.  As of this time a decision still hasn't been made on what we want to do to fix the problem.  It seems it's going to be a major change no matter what.
Thanks again for all your help!