Cannot update access database table

Posted on 2006-05-26
Last Modified: 2010-04-23

I am at a loss to truly explain it; however In short, I cannot update an access database table as the error I recieve says that ...

An Error occurred in Step 6C: Updating the Details Database Table

Error Number: 5
Description: You cannot add or change a record because a realted record is required in table 'CO ID Master'.

I use this code to try an tell me what is going on:
MsgBox("An Error occurred in " & PBText & Chr(13) & Chr(10) & Chr(10) & "Error Number: " & Err.Number & Chr(13) & Chr(10) & "Description: " & Err.Description & Chr(13) & Chr(10), MsgBoxStyle.OKOnly, PBText)

I have it designed that two (Master and Detail) data grids get populated when an error occurs; when I compare the offending detail data line (marked with a red "!") there is corresponding data in master table. I have a screen shot that shows that both are in each.

So can someone tell me what is going on?

Question by:d2beetle
    LVL 44

    Accepted Solution

    You cannot add or change a record because a realted record is required in table 'CO ID Master'

    indicartes the you have REFERNTIAL INTEGRITY tuned on, and you are attemting to insert a record into the DEATILS table which includes a value that needs to have a related record in the "CO ID Master" tbale.  Until the asociated record in the 'CO ID Master' table is presnt, you cannot insert a record into the Details table.  It is no the record in the Master table that it the issue, but rather a required record in the 'CO ID MASTER' table.

    Perhaps you are attempting to insert the record into the Details table BEFORE you insert the associated record into the Master table - the order of insert should be MASTER  then DETAIL.

    Every detail record must have a pre-existing Master record.  It is NOT the case that every Master must have an associated Detail record.

    Think of this like the relationships between parents and children in a family - Every child in the family must have a PARENT (Adult), but every Adult in the family does not need a child (ther are lots of families that do not have any children, but there are no children who did not (do not) have a parent).



    Author Comment


    I understand what the error means, however its explanation is not the case.

    Yes, referential integrity is turned on; however the CO ID Master table is a static table with only 18 entries and gets updated manually if a new CO ID is needed. I'm only trying to add records to the detail table, not the CO ID Master. The record that VB indicates is the problem, is the record using the default value of "0000000000000” (field width is 13 and text) and this value is in the CO ID Master table as it means no CO ID needed.

    FYI, I have four other tables linked to the details table and all tables have referential integrity turned on and all use "000..." (Zeros) as a default value if no value is needed. I have no problem with them. The CO ID is the last field in the details table; if I remark out the VB code for the CO ID, the update completes as expected. As a result the CO ID field in the details table is then NULL.

    So, now what do I do?

    Author Comment


    I solved the problem; your suggestion was not cause, but it did point me in the right direction. The problem was related to VB and how it interacts with access; in short the CO ID field size was the issue; on the Detials table (originally) it was 12 and in the Master table it was 13. A "partner in crime" (who shall remain nameless) that I work with changed the CO ID Detail table size to 13 to match the Master table.

    Apparently VB did not like this. What I did was (probably the long way around), I created another VB project, copy and pasted my code, then configured my data adapaters, created the dataset and everything works as advertised.

    I have learned that VB has issues with access databases when any changes are made to the table design regardless of how simple they may be.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now