Best way in MS Access to edit data enriched with data from an ODBC table

Question

This is the simplified version of the problem: We have a table on an Oracle Database. We cannot extend this table (bought app). We need to describe each row of that Oracle table with some text.

So the descission has been to use MS Access for this. So I created in Access a table (Call it ACCESS_TABLE). The table consists of ID and MEMOTEXT (and more). The ID is the link to the table on Oracle (Call it ORACLE_TABLE). I have successfully linked up MS Access thru ODBC to Oracle and have both tables available.

My first idea was to JOIN them. But then I cannot do edit. MS Access locks all fields up because of the JOIN with an ODBC table.

Then I thought of creating a form to just edit the ACCESS_TABLE and display the data from the ORACLE_TABLE. This might work but I abandoned that idea also because I want to show the user the ORACLE_TABLE (as the leading table) so that the user knows what rows still need a description.

So I was wondering whether I copy all data from the ORACLE_TABLE and add it to the ACCESS_TABLE. I then have to implement some replication algorithm of deleting/updating rows in the ACCESS_TABLE when they change but this might work. And I wondered how this could be done best.

Also I wondered whether I am at all on the right track or should tackle the problem differently in general.

Answer 1

Why did you abandon the form so quickly? Did you try Oracle as the main form and Access as a subform? It is also possible to have two subforms synchronized, so that subform 1 shows all Oracle records, joined to Access, if required to show whether an Access record exists, and subform 2 shows the Access record for the selected record in subform 1. There is an example in the Northwind sample database (customer ordser form, AFAIR).