Complex Queries (Append/Update/Delete?)

[ewong1]

I am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!

 

[iago18]

The only way I cant think of doing this is to grab all the records you need into an ADODB Recordset object. Then run two SQL commands to update your local database:

1. Run an UPDATE command to update records that have matching primary keys between your selected ORACLE records and your current local records.

2. Run an INSERT command to append the rest of the records that had no primary key match. You might want to use a 'Not In' statement in this command to assist you.

Something like "INSERT INTO Table1.Field1 ('Value') WHERE Table1.Key Not In (SELECT LocalTable.Key FROM LocalTable);"

Without more info its hard to say if this is a practical solution...but give it a try.