I just Migrated a MS Access 2010 Database to an Oracle 11g Backend with the SQL Developer Tool.
The Migration went fine, all the Tables and Views are migrated.
I'm working with MS Access as Frontend.
The application has some Datasheets with Subdatasheets with Parent/Child Relationship. (1-n Relationship)
After changing to Oracle, it's not possible, to Insert a new Record in a Subdatasheet I always get the following Error Message:
"The Microsoft Access database engine cannot find a record in the table 'xxxx' with key matching field(s) 'zzzzz'"
It used to work perfect with the MS Access Backend, do I need a trigger which first adds the child Record ?
Or what should I do?
Tested it with a small example but didn't get this kind of error message. Could you please upload a test MDB file to a share so that we could download it and test the scenario?
As an alternative, please log a service request at Oracle Support.
Thanks for your answer. I still haven't solved my problem. The only way would be to use a singel 1:n Relationship, but in fact I need a n:m Relationship.
I tried the same scenario with a new Access Application, same result.
To clearify my problem.
Goal: Parent Form with Parent Records, Linked Child Form with Child Records in a Datasheet View => Insert of a NEW Child Record.
I have 3 Tables (table1 = Parent tabel, table2 = Child Table, table12 = n:m Tabel with PK and two FK)
The Recordsource of the Parent Form is Tabel1
The Recordsource of the Child Form is Table2 joined with Table12.
In my Old Access Project, Access Triggered the Insert and filled Table12 with the NEW PK of Table2.
It seems like Access can't do that anymore....
I'm pretty desperate and I'm sure it is just a litte thing to fix.....
Have you checked actual values of foreign and primary keys in Parent, Table12 and Child tables? Are they in the same case?
Can you post a sample join query including all 3 tables in Oracle to show that it returns the required data?
Edited by: 997263 on 01.04.2013 3:00
If I try to insert a new Recordset in Access I get the following Error:
"THe Microsoft Access datbase engine fcannot find a record in the table 'Table2' with Key matching field(s) 'xy_ID'"
The Joins are normal 1:n Joins both Tables to the Table12.