Updating via Form to linked tables

[Dave]

I have a set of linked tables (To Oracle DB) that I need
to update via a form.

Up to now, I have had pretty straightforward forms that
basically relate to one table. I now have a situation
where I've created a form based on a multi-table query in
which I need to update some of the fields. I naively
create the form on the query, but now have realized that
it won't let me update via the query. My question is:
How can I have a form that is built on a multi join table
query that allows a user to do an update?

Another related question I have is: I've noticed that if
I update a field via forms I've built on a single table,
that the records are updated when I navigate to the next
record. Is there a concept of buffering changes until
a "save" button is pressed by the user? The user may be
navigating through quite a few records and making
updates. In many situations, it is common to have a save
button so that the user can explicity press to save the
changes to the databse. This of course assumes that the
changes are buffered. What is the most common way of
addressing this issue?

Thanks,

Dave

 

[Rick Brandt]

I have a set of linked tables (To Oracle DB) that I need
to update via a form.

Up to now, I have had pretty straightforward forms that
basically relate to one table. I now have a situation
where I've created a form based on a multi-table query in
which I need to update some of the fields. I naively
create the form on the query, but now have realized that
it won't let me update via the query. My question is:
How can I have a form that is built on a multi join table
query that allows a user to do an update?

Another related question I have is: I've noticed that if
I update a field via forms I've built on a single table,
that the records are updated when I navigate to the next
record. Is there a concept of buffering changes until
a "save" button is pressed by the user? The user may be
navigating through quite a few records and making
updates. In many situations, it is common to have a save
button so that the user can explicity press to save the
changes to the databse. This of course assumes that the
changes are buffered. What is the most common way of
addressing this issue?

Use a different product

Seriously; you can do this but it would involve using work tables and then using
queries to submit the data and clear the work tables when your button is
pressed. Too much trouble for very little benefit (IMO).

 

[Albert D. Kallal]

You will have to ask in the Oracle group if joined queries are updateable.
If they are, then create a view in Oracle and use that as your source for
the form.

if you are just using JET database engine, and not Oracle, then many Joined
query's are in fact updateable (quite amazing feature).

however, in most cases, the relationship is a one to many, and thus it makes
sense to use a sub-form. Thus, a customer invoice will have invoice info at
the top, and then the repeating data (the many side of the join), will be
the details in a sub form.

So, in most cases, the recommend approach to update two tables is NOT to use
a joined query, but use a sub-form. (thus, you don't use joins).

There was a time when joined queries were NOT updateable, but ms-access is
capable of such a feat.

You usually has to include BOTH fields (pk from main table, and both id, and
forking key from the child table). Not all are updateable, and there is a KB
article that explains when, and when not the query is updateable. However,
this rule applied only to JET (mdb) based databases. In the case of
Oracle...you will have to ask them. (and if the answer is yes, then create
view on the Oracle side that is the join).

You are not going to be able to make the join updateable on the ms-access
side, as it don't know much about Oracle! However, a joined query on the
oracle side exposed as a view will work assumng Oracle allows joined queries
to be updatable.

 

[Dave]

Thanks guys,

I appreciate the input. Oracle does provide for updates
via views, but I was encountering a "ora-01732 data
manipulation operation not legal on this view" error when
trying to do it via an ODBC link.

I did discover a workaround though. You can put a
database trigger (INSTEAD OF UPDATE) on the view in
Oracle. You can then handle the update within the trigger
itself.

Thanks,

Dave