[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Cannot update access database table


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?

Frank Bryant
Frank Bryant
  • 2
1 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).


Frank BryantJOATAuthor Commented:

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?
Frank BryantJOATAuthor Commented:

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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now