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??
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??
Jeffrey Coachman
...and the SQL for the query is.....?
Try inserting the records into a temp table in Access (that you can clear out and reload for each session)
ASKER
Here's the append query
INSERT INTO TempDriverList ( CPNT_ID, COMPL_DTE, CMPL_STAT_ID, STUD_ID, JP_ID, DMN_ID, ORG_ID, LNAME, FNAME, MI, NOTACTIVE, ADDR, CITY, STATE, POSTAL, SUPER, SUPER_NAME, HIRE_DTE, TERM_DTE, BIRTHDATE, BUSINESS_UNIT, ORG_VP )
SELECT [a15-02-LMSDrivers].CPNT_I
FROM [a15-05-OrgVP] RIGHT JOIN ([a15-04-BusinessUnit] RIGHT JOIN ([a15-02-LMSDrivers] LEFT JOIN [a15-03-Birthdate] ON [a15-02-LMSDrivers].STUD_I
INSERT INTO TempDriverList ( CPNT_ID, COMPL_DTE, CMPL_STAT_ID, STUD_ID, JP_ID, DMN_ID, ORG_ID, LNAME, FNAME, MI, NOTACTIVE, ADDR, CITY, STATE, POSTAL, SUPER, SUPER_NAME, HIRE_DTE, TERM_DTE, BIRTHDATE, BUSINESS_UNIT, ORG_VP )
SELECT [a15-02-LMSDrivers].CPNT_I
FROM [a15-05-OrgVP] RIGHT JOIN ([a15-04-BusinessUnit] RIGHT JOIN ([a15-02-LMSDrivers] LEFT JOIN [a15-03-Birthdate] ON [a15-02-LMSDrivers].STUD_I
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
thanks for help -- it was the joins
I split the query up into one append and several update queries and it's working now
Thanks
I split the query up into one append and several update queries and it's working now
Thanks