Solved: Adding records in a relational table

[Kicker]

This one could be for the guru's.

Situation:
I have access to a set of Oracle databases Due to a lot of company restrictions, I do not have "write" priviledges through Access and I have absolutely no way of getting NIS to modify the table structure.

I managed to get Access authorized on my work computer and am easily reading the data I need. Therefore, I have 5 "Linked" tables from which I have several queries to present the data I need for some special analysis and reports.

I do have the ability to create and maintain local and personal tables. Therefore, If I have OracleTable from the network and an AccessTable locally, I can create a relationship between them and locally add some fields I need. I understand that the "local" table will grow and grow and grow but will only show the records related to the data records from the OracleTable.

Is there a way to "automatically" add records to the AccessTable? For example:

One or more records are added to the OracleTable. When I run my sql, there will be several OracleTable records without matching AccessTable records. Some of the fields I want to add would be the days of the week.

Ttfn
Kicker

[Paleo]

Hi Kicker,

one idea would be to store in a local table the last records id from Oracle and then copy to your local table all records newer than that stored and then renew the id in your table.

Just to think about. Will this table really grow and grow and grow? Why dont you use MSDE instead of access on this? Its a SQL Server.

[Kicker]

There are several problems, the least to mention is that the Oracle databases are accessabl ONLY through a company provided NIS program called Mosaic which is a far far far far cry from the old mosaic we had years ago. The operative word is ONLY.

Any access I have through MS Access is read-only. Absolutely no data can be modified or added. I'm working on it and believe there is always more than one way to skin a cat.

ttfn

Kicker

[zilpher]

The obvious answer is to create a trigger on the Oracle table and run an extproc, *however* it doesn't sound like that'll be an option.

TBH, I'm not sure I understand what you mean by local tables, unless you mean an Access table and tbh, I never use Access so forgive me if this is a bum steer.

In Oracle, you can select minus. I'll give an example showing how I just tested this very quickly:

I have a table called triggeradmin_audit, it contains data I need so I created a backup to work with:

create table trigger1 as select * from triggeradmin_audit;

This creates an exact copy, 1005 rows....

now I want another table, same structure, less data, so:

create table trigger2 as select * from triggeradmin_audit where agency = 'PT';

same structure, only 325 rows....

to see the differences I use this:

select * from trigger1 minus select * from trigger2;

and to line them up I use this:

insert into trigger2 (select * from trigger1 minus select * from trigger2);

I suspect that Access will not support the minus syntax in sql, so the only other way I can think to use is the primary key (assuming you have a unique pk on both db)

select * from trigger1 where pk not in (select pk from trigger2);

HTH

[Paleo]

Hi Kicker,

I didnt mean you to create tables on Oracle, but to create then on Access or MSDE as you wish, locally. Disconnected tables from your Oracle database, just to save your queries.


Any access I have through MS Access is read-only. Absolutely no data can be modified or added. I'm working on it and believe there is always more than one way to skin a cat.

[Kicker]

Thanks

I'll try these ideas

ttfn

Kicker

[Paleo]

You are welcome!

[Anne Troy]

Hey, Kicker! :) Is this resolved?