Cannot update access database table
Everyone,
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Arthur,
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.
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.
ASKER
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?