Append Records from Oracle to Access

I am trying to append data from an Oracle table to an Access table and I’m getting an error -  “Run time error 3167 - Record is Deleted”.  

The Access table I’m appending to is in a separate backend .accdb file / the front end is also .accdb format

The query joins multiple Oracle tables with a complex selection criteria

This query was working – I ran it multiple times while I was refining the selection criteria over the last two weeks – I’ve only started getting the error in the last few days - and yes multiple things have changed, the database is a work in progress  

Here are the things I’ve tried:

I can run the query in select mode and it displays all the records correctly
I can run the query as a make table query and it creates the table correctly
I was able to identify the specific records it was failing on, then exclude that record and the query will append a few more records – but with over 3000 records I never excluded all the failing records.
I’ve switched from our Oracle test environment to our production environment
There is nothing different about the records it’s failing on – they have not been ‘deleted’
I can use code with a recordset to read the data from the select query and append it to the access table – but it’s very slooooow!
I’ve done multiple compacts of both back & front end files

Since this query was working previously it’s got to be something stupid that changed but I’m stumped

does anyone have any sugestions??
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
...and the SQL for the query is.....?
Jeffrey CoachmanMIS LiasonCommented:
Try inserting the records into a temp table in Access (that you can clear out and reload for each session)
CountryGirlMDAuthor Commented:
Here's the append query

SELECT [a15-02-LMSDrivers].CPNT_ID, [a15-02-LMSDrivers].COMPL_DTE, [a15-02-LMSDrivers].CMPL_STAT_ID, [a15-02-LMSDrivers].STUD_ID, [a15-02-LMSDrivers].JP_ID, [a15-02-LMSDrivers].DMN_ID, [a15-02-LMSDrivers].ORG_ID, [a15-02-LMSDrivers].LNAME, [a15-02-LMSDrivers].FNAME, [a15-02-LMSDrivers].MI, [a15-02-LMSDrivers].NOTACTIVE, [a15-02-LMSDrivers].ADDR, [a15-02-LMSDrivers].CITY, [a15-02-LMSDrivers].STATE, [a15-02-LMSDrivers].POSTAL, [a15-02-LMSDrivers].SUPER, [a15-02-LMSDrivers].SUPER_NAME, [a15-02-LMSDrivers].HIRE_DTE, [a15-02-LMSDrivers].TERM_DTE, [a15-03-Birthdate].USER_VALUE AS BIRTHDATE, [a15-04-BusinessUnit].USER_VALUE AS BUSINESS_UNIT, [a15-05-OrgVP].USER_VALUE AS ORG_VP
FROM [a15-05-OrgVP] RIGHT JOIN ([a15-04-BusinessUnit] RIGHT JOIN ([a15-02-LMSDrivers] LEFT JOIN [a15-03-Birthdate] ON [a15-02-LMSDrivers].STUD_ID = [a15-03-Birthdate].STUD_ID) ON [a15-04-BusinessUnit].STUD_ID = [a15-02-LMSDrivers].STUD_ID) ON [a15-05-OrgVP].STUD_ID = [a15-02-LMSDrivers].STUD_ID;
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

CountryGirlMDAuthor Commented:
I created a new table - still can't append the oracle records to it
I can run a make table query - that works

but the table I am trying to append to is already a temp table that gets flushed before the append query is run -- the temp table is used to combine records from several sources before they are moved to the active records table

also - there are other querys that do appends from other oracle sources - they are working
it's just the queries from this one oracle source - but as I said it was working
Jeffrey CoachmanMIS LiasonCommented:
Access does not support Left and Right Joins in the same query.
Do the other queries also contain left and right joins?

Try creating a query with the left join...
Then join that query to the other, with the right join.

But lets also see if experts better at SQL will chime in...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Something may have changed in your Oracle.
Create a query of your linked Oracle table(s).   Use Access to build the query (not hand written using VBA).  Right-click on the query design and click PROPERTIES.  You may have to click twice to see the RECORDSET TYPE property.   Set this property to SNAPSHOT.

This will prevent your Oracle records to appear DELETED.

Scott C
CountryGirlMDAuthor Commented:
thanks for help -- it was the joins
I split the query up into one append and several update queries and it's working now
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.