If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
I wrote an append query that successfully gets new Unique ID's, loads them as
the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#2
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
For the most part an Oracle sequence is run by a trigger on the table. So you
should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#3
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
Thanks Jerry. I wrote the query in Design View of Access, rather than VBA
directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#4
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
The trigger "should" automatically grab the next sequence number as you
insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#5
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
That's what I thought too, but this is the whole reason for the post ... it
did sequence just fine ... the new numbers are IN the table. What happened was that the Sequence Generator in Oracle remained at the last number PRIOR to the insert query, so, even though the new larger numbers went in, Oracle still thinks the next number is the next number BEFORE the insert. See my dilemma? FYI, Access could read Oracles next number to start the sequence. It then sequenced just fine as inserted. The only issue is after the insert was complete, Oracle didnt' recognize the NEW last number. I read something that said Access doesn't "commit" till the end of the insert query. Could this be it? Is Oracle EXPECTING to insert, then commit, record by record?? If so, how can I include this in my Access query? "Jerry Whittle" wrote: The trigger "should" automatically grab the next sequence number as you insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#6
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
Oracle does not expect a commit after every record change. You could insert a
thousand records into a table and roll them all back out by not committing. Also committing should not make any difference to the sequence or trigger. If possible, post the script for both the trigger and sequence. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: That's what I thought too, but this is the whole reason for the post ... it did sequence just fine ... the new numbers are IN the table. What happened was that the Sequence Generator in Oracle remained at the last number PRIOR to the insert query, so, even though the new larger numbers went in, Oracle still thinks the next number is the next number BEFORE the insert. See my dilemma? FYI, Access could read Oracles next number to start the sequence. It then sequenced just fine as inserted. The only issue is after the insert was complete, Oracle didnt' recognize the NEW last number. I read something that said Access doesn't "commit" till the end of the insert query. Could this be it? Is Oracle EXPECTING to insert, then commit, record by record?? If so, how can I include this in my Access query? "Jerry Whittle" wrote: The trigger "should" automatically grab the next sequence number as you insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#7
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
That's a HUGE bit of info .. thank you. I actually thought it commited after
every insertion. I'll post that as soon as I can, but I've got a few pressing things immediately ... thank you so much for your help. Jim "Jerry Whittle" wrote: Oracle does not expect a commit after every record change. You could insert a thousand records into a table and roll them all back out by not committing. Also committing should not make any difference to the sequence or trigger. If possible, post the script for both the trigger and sequence. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: That's what I thought too, but this is the whole reason for the post ... it did sequence just fine ... the new numbers are IN the table. What happened was that the Sequence Generator in Oracle remained at the last number PRIOR to the insert query, so, even though the new larger numbers went in, Oracle still thinks the next number is the next number BEFORE the insert. See my dilemma? FYI, Access could read Oracles next number to start the sequence. It then sequenced just fine as inserted. The only issue is after the insert was complete, Oracle didnt' recognize the NEW last number. I read something that said Access doesn't "commit" till the end of the insert query. Could this be it? Is Oracle EXPECTING to insert, then commit, record by record?? If so, how can I include this in my Access query? "Jerry Whittle" wrote: The trigger "should" automatically grab the next sequence number as you insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! |
#8
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
Two tables are being appended to one after the other. TASSIGNMENT and
TASSIGNINTERVAL. There are two Triggers apparently for TASSIGNMENT and there doesn't appear to be a Trigger for TASSIGNINTERVAL I looked for some code related to SEQUENCING and it's just a table asking Start Value, Min Value, Max Value, Increment, Cache and Last Number (the Last Number is what's NOT being updated after the Insert Query). There are also two check boxes: Recycle after min and max AND Guaranteed order. Neither are checked. I have found this all using SQL Navigator. ======================================= Trigger code #1 for the TASSIGNMENT table DECLARE assignmentVariable0 INTEGER := 0; DBSTARTDATE DATE; assignmentVariable8 INTEGER := 0; assignmentVariable9 INTEGER := 0; assignmentVariable11 INTEGER := 0; BEGIN /* Prevent insert of task linked to charges*/ BEGIN SELECT COUNT(*) INTO assignmentVariable0 FROM TTASK WHERE :NEW.ORGANIZATIONID = TTASK.ORGANIZATIONID AND :NEW.TASKID = TTASK.UNIQUEID AND TTASK.CHARGEID 0; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable0 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable0) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20303#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; IF :NEW.ENDDATE = :NEW.STARTDATE THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20023#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; SELECT DATESTART INTO DBSTARTDATE FROM TGLOBALPERIOD WHERE TGLOBALPERIOD.ORGANIZATIONID = :NEW.ORGANIZATIONID; BEGIN SELECT COUNT(*) INTO assignmentVariable8 FROM DUAL WHERE MOD(ROUND(:NEW.STARTDATE - DBSTARTDATE), 7) 0; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable8 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable8) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20021#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; BEGIN SELECT COUNT(*) INTO assignmentVariable9 FROM DUAL WHERE ( MOD(ROUND(:NEW.ENDDATE - DBSTARTDATE), 7) 0) AND (:NEW.ENDDATE != TO_DATE('11/27/2737', 'MM/DD/YYYY')); EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable9 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable9) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20021#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; BEGIN TASSIGNMENTDATA.V_NUMENTRIES := TASSIGNMENTDATA.V_NUMENTRIES + 1; TASSIGNMENTDATA.V_USERIDs(TASSIGNMENTDATA.V_NUMENT RIES) := :NEW.USERID; TASSIGNMENTDATA.V_TASKIDs(TASSIGNMENTDATA.V_NUMENT RIES) := :NEW.TASKID; TASSIGNMENTDATA.V_WORKFLOWENTRYIDs(TASSIGNMENTDATA .V_NUMENTRIES) := :NEW.WORKFLOWENTRYID; TASSIGNMENTDATA.V_ORGANIZATIONIDs(TASSIGNMENTDATA. V_NUMENTRIES) := :NEW.ORGANIZATIONID; TASSIGNMENTDATA.V_STARTDATEs(TASSIGNMENTDATA.V_NUM ENTRIES) := :NEW.STARTDATE; TASSIGNMENTDATA.V_ENDDATEs(TASSIGNMENTDATA.V_NUMEN TRIES) := :NEW.ENDDATE; TASSIGNMENTDATA.V_UNIQUEIDs(TASSIGNMENTDATA.V_NUME NTRIES) := :NEW.UNIQUEID; END; BEGIN SELECT COUNT(*) INTO assignmentVariable11 FROM TTASK WHERE TTASK.UNIQUEID = :NEW.TASKID AND :NEW.ORGANIZATIONID = TTASK.ORGANIZATIONID AND (TTASK.STARTDATE :NEW.STARTDATE OR TTASK.ENDDATE + 1 :NEW.ENDDATE); EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable11 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable11) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20233#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; END TASSIGNMENT_ITRIG; ========================================= Trigger code #2 for the TASSIGNMENT table DECLARE -- local variables here assignmentVariable10 INTEGER := 0; V_USERID TASSIGNMENT.USERID%TYPE; V_TASKID TASSIGNMENT.TASKID%TYPE; V_WORKFLOWENTRYID TASSIGNMENT.WORKFLOWENTRYID%TYPE; V_ORGANIZATIONID TASSIGNMENT.ORGANIZATIONID%TYPE; V_STARTDATE TASSIGNMENT.STARTDATE%TYPE; V_ENDDATE TASSIGNMENT.ENDDATE%TYPE; V_UNIQUEID TASSIGNMENT.UNIQUEID%TYPE; BEGIN FOR V_LOOPINDEX IN 1..TASSIGNMENTDATA.V_NUMENTRIES LOOP V_USERID := TASSIGNMENTDATA.V_USERIDs(V_LOOPINDEX); V_TASKID := TASSIGNMENTDATA.V_TASKIDs(V_LOOPINDEX); V_WORKFLOWENTRYID := TASSIGNMENTDATA.V_WORKFLOWENTRYIDs(V_LOOPINDEX); V_ORGANIZATIONID := TASSIGNMENTDATA. V_ORGANIZATIONIDs(V_LOOPINDEX); V_STARTDATE := TASSIGNMENTDATA.V_STARTDATEs(V_LOOPINDEX); V_ENDDATE := TASSIGNMENTDATA.V_ENDDATEs(V_LOOPINDEX); V_UNIQUEID := TASSIGNMENTDATA. V_UNIQUEIDs(V_LOOPINDEX); BEGIN SELECT COUNT(*) INTO assignmentVariable10 FROM TASSIGNMENT WHERE TASSIGNMENT.USERID = V_USERID AND TASSIGNMENT.TASKID = V_TASKID AND TASSIGNMENT.WORKFLOWENTRYID = V_WORKFLOWENTRYID AND TASSIGNMENT.ORGANIZATIONID = V_ORGANIZATIONID AND ((TASSIGNMENT.STARTDATE = V_STARTDATE AND TASSIGNMENT.STARTDATE V_ENDDATE) OR (TASSIGNMENT.ENDDATE V_STARTDATE AND TASSIGNMENT.ENDDATE = V_ENDDATE) OR (TASSIGNMENT.STARTDATE = V_STARTDATE AND TASSIGNMENT.ENDDATE = V_ENDDATE)) AND TASSIGNMENT.UNIQUEID != V_UNIQUEID; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable10 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable10) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20058#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; END LOOP; -- Reinitialize the counter in order the next execution will use new data TASSIGNMENTDATA.V_NUMENTRIES :=0; END TASSIGNMENT2_ITRIG; "Jerry Whittle" wrote: Oracle does not expect a commit after every record change. You could insert a thousand records into a table and roll them all back out by not committing. Also committing should not make any difference to the sequence or trigger. If possible, post the script for both the trigger and sequence. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: That's what I thought too, but this is the whole reason for the post ... it did sequence just fine ... the new numbers are IN the table. What happened was that the Sequence Generator in Oracle remained at the last number PRIOR to the insert query, so, even though the new larger numbers went in, Oracle still thinks the next number is the next number BEFORE the insert. See my dilemma? FYI, Access could read Oracles next number to start the sequence. It then sequenced just fine as inserted. The only issue is after the insert was complete, Oracle didnt' recognize the NEW last number. I read something that said Access doesn't "commit" till the end of the insert query. Could this be it? Is Oracle EXPECTING to insert, then commit, record by record?? If so, how can I include this in my Access query? "Jerry Whittle" wrote: The trigger "should" automatically grab the next sequence number as you insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! ================================ |
#9
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
That is one very, very complex set of triggers. The logic is very complex and
does a lot more than just update records with a sequence. I very much doubt that you can insert a lot of records into those tables through an append query. You really need to talk to whoever developed the triggers and find the logic for them before inserting records this way. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Two tables are being appended to one after the other. TASSIGNMENT and TASSIGNINTERVAL. There are two Triggers apparently for TASSIGNMENT and there doesn't appear to be a Trigger for TASSIGNINTERVAL I looked for some code related to SEQUENCING and it's just a table asking Start Value, Min Value, Max Value, Increment, Cache and Last Number (the Last Number is what's NOT being updated after the Insert Query). There are also two check boxes: Recycle after min and max AND Guaranteed order. Neither are checked. I have found this all using SQL Navigator. ======================================= Trigger code #1 for the TASSIGNMENT table DECLARE assignmentVariable0 INTEGER := 0; DBSTARTDATE DATE; assignmentVariable8 INTEGER := 0; assignmentVariable9 INTEGER := 0; assignmentVariable11 INTEGER := 0; BEGIN /* Prevent insert of task linked to charges*/ BEGIN SELECT COUNT(*) INTO assignmentVariable0 FROM TTASK WHERE :NEW.ORGANIZATIONID = TTASK.ORGANIZATIONID AND :NEW.TASKID = TTASK.UNIQUEID AND TTASK.CHARGEID 0; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable0 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable0) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20303#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; IF :NEW.ENDDATE = :NEW.STARTDATE THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20023#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; SELECT DATESTART INTO DBSTARTDATE FROM TGLOBALPERIOD WHERE TGLOBALPERIOD.ORGANIZATIONID = :NEW.ORGANIZATIONID; BEGIN SELECT COUNT(*) INTO assignmentVariable8 FROM DUAL WHERE MOD(ROUND(:NEW.STARTDATE - DBSTARTDATE), 7) 0; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable8 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable8) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20021#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; BEGIN SELECT COUNT(*) INTO assignmentVariable9 FROM DUAL WHERE ( MOD(ROUND(:NEW.ENDDATE - DBSTARTDATE), 7) 0) AND (:NEW.ENDDATE != TO_DATE('11/27/2737', 'MM/DD/YYYY')); EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable9 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable9) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20021#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; BEGIN TASSIGNMENTDATA.V_NUMENTRIES := TASSIGNMENTDATA.V_NUMENTRIES + 1; TASSIGNMENTDATA.V_USERIDs(TASSIGNMENTDATA.V_NUMENT RIES) := :NEW.USERID; TASSIGNMENTDATA.V_TASKIDs(TASSIGNMENTDATA.V_NUMENT RIES) := :NEW.TASKID; TASSIGNMENTDATA.V_WORKFLOWENTRYIDs(TASSIGNMENTDATA .V_NUMENTRIES) := :NEW.WORKFLOWENTRYID; TASSIGNMENTDATA.V_ORGANIZATIONIDs(TASSIGNMENTDATA. V_NUMENTRIES) := :NEW.ORGANIZATIONID; TASSIGNMENTDATA.V_STARTDATEs(TASSIGNMENTDATA.V_NUM ENTRIES) := :NEW.STARTDATE; TASSIGNMENTDATA.V_ENDDATEs(TASSIGNMENTDATA.V_NUMEN TRIES) := :NEW.ENDDATE; TASSIGNMENTDATA.V_UNIQUEIDs(TASSIGNMENTDATA.V_NUME NTRIES) := :NEW.UNIQUEID; END; BEGIN SELECT COUNT(*) INTO assignmentVariable11 FROM TTASK WHERE TTASK.UNIQUEID = :NEW.TASKID AND :NEW.ORGANIZATIONID = TTASK.ORGANIZATIONID AND (TTASK.STARTDATE :NEW.STARTDATE OR TTASK.ENDDATE + 1 :NEW.ENDDATE); EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable11 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable11) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20233#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; END TASSIGNMENT_ITRIG; ========================================= Trigger code #2 for the TASSIGNMENT table DECLARE -- local variables here assignmentVariable10 INTEGER := 0; V_USERID TASSIGNMENT.USERID%TYPE; V_TASKID TASSIGNMENT.TASKID%TYPE; V_WORKFLOWENTRYID TASSIGNMENT.WORKFLOWENTRYID%TYPE; V_ORGANIZATIONID TASSIGNMENT.ORGANIZATIONID%TYPE; V_STARTDATE TASSIGNMENT.STARTDATE%TYPE; V_ENDDATE TASSIGNMENT.ENDDATE%TYPE; V_UNIQUEID TASSIGNMENT.UNIQUEID%TYPE; BEGIN FOR V_LOOPINDEX IN 1..TASSIGNMENTDATA.V_NUMENTRIES LOOP V_USERID := TASSIGNMENTDATA.V_USERIDs(V_LOOPINDEX); V_TASKID := TASSIGNMENTDATA.V_TASKIDs(V_LOOPINDEX); V_WORKFLOWENTRYID := TASSIGNMENTDATA.V_WORKFLOWENTRYIDs(V_LOOPINDEX); V_ORGANIZATIONID := TASSIGNMENTDATA. V_ORGANIZATIONIDs(V_LOOPINDEX); V_STARTDATE := TASSIGNMENTDATA.V_STARTDATEs(V_LOOPINDEX); V_ENDDATE := TASSIGNMENTDATA.V_ENDDATEs(V_LOOPINDEX); V_UNIQUEID := TASSIGNMENTDATA. V_UNIQUEIDs(V_LOOPINDEX); BEGIN SELECT COUNT(*) INTO assignmentVariable10 FROM TASSIGNMENT WHERE TASSIGNMENT.USERID = V_USERID AND TASSIGNMENT.TASKID = V_TASKID AND TASSIGNMENT.WORKFLOWENTRYID = V_WORKFLOWENTRYID AND TASSIGNMENT.ORGANIZATIONID = V_ORGANIZATIONID AND ((TASSIGNMENT.STARTDATE = V_STARTDATE AND TASSIGNMENT.STARTDATE V_ENDDATE) OR (TASSIGNMENT.ENDDATE V_STARTDATE AND TASSIGNMENT.ENDDATE = V_ENDDATE) OR (TASSIGNMENT.STARTDATE = V_STARTDATE AND TASSIGNMENT.ENDDATE = V_ENDDATE)) AND TASSIGNMENT.UNIQUEID != V_UNIQUEID; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable10 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable10) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20058#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; END LOOP; -- Reinitialize the counter in order the next execution will use new data TASSIGNMENTDATA.V_NUMENTRIES :=0; END TASSIGNMENT2_ITRIG; "Jerry Whittle" wrote: Oracle does not expect a commit after every record change. You could insert a thousand records into a table and roll them all back out by not committing. Also committing should not make any difference to the sequence or trigger. If possible, post the script for both the trigger and sequence. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: That's what I thought too, but this is the whole reason for the post ... it did sequence just fine ... the new numbers are IN the table. What happened was that the Sequence Generator in Oracle remained at the last number PRIOR to the insert query, so, even though the new larger numbers went in, Oracle still thinks the next number is the next number BEFORE the insert. See my dilemma? FYI, Access could read Oracles next number to start the sequence. It then sequenced just fine as inserted. The only issue is after the insert was complete, Oracle didnt' recognize the NEW last number. I read something that said Access doesn't "commit" till the end of the insert query. Could this be it? Is Oracle EXPECTING to insert, then commit, record by record?? If so, how can I include this in my Access query? "Jerry Whittle" wrote: The trigger "should" automatically grab the next sequence number as you insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! ================================ |
#10
|
|||
|
|||
Access Append Query to update Sequence Generator in Oracle
Thank you Jerry for your time. The triggers were developed by the software
company we purchased the front end web app from, but we maintain the Oracle tables locally. I simply wish on occasion, to do bulk work in the tables that will affect many users all over the country who use the front end app via secure login. This has ended up being a lot more difficult than imagined and the software company has not been very helpful. Thank you very much for trying. I've learned more from you than I have them, during our short discussion. Happy Holidays. Take care. Jim "Jerry Whittle" wrote: That is one very, very complex set of triggers. The logic is very complex and does a lot more than just update records with a sequence. I very much doubt that you can insert a lot of records into those tables through an append query. You really need to talk to whoever developed the triggers and find the logic for them before inserting records this way. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Two tables are being appended to one after the other. TASSIGNMENT and TASSIGNINTERVAL. There are two Triggers apparently for TASSIGNMENT and there doesn't appear to be a Trigger for TASSIGNINTERVAL I looked for some code related to SEQUENCING and it's just a table asking Start Value, Min Value, Max Value, Increment, Cache and Last Number (the Last Number is what's NOT being updated after the Insert Query). There are also two check boxes: Recycle after min and max AND Guaranteed order. Neither are checked. I have found this all using SQL Navigator. ======================================= Trigger code #1 for the TASSIGNMENT table DECLARE assignmentVariable0 INTEGER := 0; DBSTARTDATE DATE; assignmentVariable8 INTEGER := 0; assignmentVariable9 INTEGER := 0; assignmentVariable11 INTEGER := 0; BEGIN /* Prevent insert of task linked to charges*/ BEGIN SELECT COUNT(*) INTO assignmentVariable0 FROM TTASK WHERE :NEW.ORGANIZATIONID = TTASK.ORGANIZATIONID AND :NEW.TASKID = TTASK.UNIQUEID AND TTASK.CHARGEID 0; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable0 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable0) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20303#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; IF :NEW.ENDDATE = :NEW.STARTDATE THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20023#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; SELECT DATESTART INTO DBSTARTDATE FROM TGLOBALPERIOD WHERE TGLOBALPERIOD.ORGANIZATIONID = :NEW.ORGANIZATIONID; BEGIN SELECT COUNT(*) INTO assignmentVariable8 FROM DUAL WHERE MOD(ROUND(:NEW.STARTDATE - DBSTARTDATE), 7) 0; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable8 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable8) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20021#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; BEGIN SELECT COUNT(*) INTO assignmentVariable9 FROM DUAL WHERE ( MOD(ROUND(:NEW.ENDDATE - DBSTARTDATE), 7) 0) AND (:NEW.ENDDATE != TO_DATE('11/27/2737', 'MM/DD/YYYY')); EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable9 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable9) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20021#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; BEGIN TASSIGNMENTDATA.V_NUMENTRIES := TASSIGNMENTDATA.V_NUMENTRIES + 1; TASSIGNMENTDATA.V_USERIDs(TASSIGNMENTDATA.V_NUMENT RIES) := :NEW.USERID; TASSIGNMENTDATA.V_TASKIDs(TASSIGNMENTDATA.V_NUMENT RIES) := :NEW.TASKID; TASSIGNMENTDATA.V_WORKFLOWENTRYIDs(TASSIGNMENTDATA .V_NUMENTRIES) := :NEW.WORKFLOWENTRYID; TASSIGNMENTDATA.V_ORGANIZATIONIDs(TASSIGNMENTDATA. V_NUMENTRIES) := :NEW.ORGANIZATIONID; TASSIGNMENTDATA.V_STARTDATEs(TASSIGNMENTDATA.V_NUM ENTRIES) := :NEW.STARTDATE; TASSIGNMENTDATA.V_ENDDATEs(TASSIGNMENTDATA.V_NUMEN TRIES) := :NEW.ENDDATE; TASSIGNMENTDATA.V_UNIQUEIDs(TASSIGNMENTDATA.V_NUME NTRIES) := :NEW.UNIQUEID; END; BEGIN SELECT COUNT(*) INTO assignmentVariable11 FROM TTASK WHERE TTASK.UNIQUEID = :NEW.TASKID AND :NEW.ORGANIZATIONID = TTASK.ORGANIZATIONID AND (TTASK.STARTDATE :NEW.STARTDATE OR TTASK.ENDDATE + 1 :NEW.ENDDATE); EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable11 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable11) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20233#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; END TASSIGNMENT_ITRIG; ========================================= Trigger code #2 for the TASSIGNMENT table DECLARE -- local variables here assignmentVariable10 INTEGER := 0; V_USERID TASSIGNMENT.USERID%TYPE; V_TASKID TASSIGNMENT.TASKID%TYPE; V_WORKFLOWENTRYID TASSIGNMENT.WORKFLOWENTRYID%TYPE; V_ORGANIZATIONID TASSIGNMENT.ORGANIZATIONID%TYPE; V_STARTDATE TASSIGNMENT.STARTDATE%TYPE; V_ENDDATE TASSIGNMENT.ENDDATE%TYPE; V_UNIQUEID TASSIGNMENT.UNIQUEID%TYPE; BEGIN FOR V_LOOPINDEX IN 1..TASSIGNMENTDATA.V_NUMENTRIES LOOP V_USERID := TASSIGNMENTDATA.V_USERIDs(V_LOOPINDEX); V_TASKID := TASSIGNMENTDATA.V_TASKIDs(V_LOOPINDEX); V_WORKFLOWENTRYID := TASSIGNMENTDATA.V_WORKFLOWENTRYIDs(V_LOOPINDEX); V_ORGANIZATIONID := TASSIGNMENTDATA. V_ORGANIZATIONIDs(V_LOOPINDEX); V_STARTDATE := TASSIGNMENTDATA.V_STARTDATEs(V_LOOPINDEX); V_ENDDATE := TASSIGNMENTDATA.V_ENDDATEs(V_LOOPINDEX); V_UNIQUEID := TASSIGNMENTDATA. V_UNIQUEIDs(V_LOOPINDEX); BEGIN SELECT COUNT(*) INTO assignmentVariable10 FROM TASSIGNMENT WHERE TASSIGNMENT.USERID = V_USERID AND TASSIGNMENT.TASKID = V_TASKID AND TASSIGNMENT.WORKFLOWENTRYID = V_WORKFLOWENTRYID AND TASSIGNMENT.ORGANIZATIONID = V_ORGANIZATIONID AND ((TASSIGNMENT.STARTDATE = V_STARTDATE AND TASSIGNMENT.STARTDATE V_ENDDATE) OR (TASSIGNMENT.ENDDATE V_STARTDATE AND TASSIGNMENT.ENDDATE = V_ENDDATE) OR (TASSIGNMENT.STARTDATE = V_STARTDATE AND TASSIGNMENT.ENDDATE = V_ENDDATE)) AND TASSIGNMENT.UNIQUEID != V_UNIQUEID; EXCEPTION WHEN NO_DATA_FOUND THEN assignmentVariable10 := 0; WHEN TOO_MANY_ROWS THEN NULL; END; IF( assignmentVariable10) 0 THEN RAISE_APPLICATION_ERROR(-20001, 'EX-ERR:20058#' ); /* ROLLBACK; */ /* Manual Intervention required.*/ END IF; END LOOP; -- Reinitialize the counter in order the next execution will use new data TASSIGNMENTDATA.V_NUMENTRIES :=0; END TASSIGNMENT2_ITRIG; "Jerry Whittle" wrote: Oracle does not expect a commit after every record change. You could insert a thousand records into a table and roll them all back out by not committing. Also committing should not make any difference to the sequence or trigger. If possible, post the script for both the trigger and sequence. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: That's what I thought too, but this is the whole reason for the post ... it did sequence just fine ... the new numbers are IN the table. What happened was that the Sequence Generator in Oracle remained at the last number PRIOR to the insert query, so, even though the new larger numbers went in, Oracle still thinks the next number is the next number BEFORE the insert. See my dilemma? FYI, Access could read Oracles next number to start the sequence. It then sequenced just fine as inserted. The only issue is after the insert was complete, Oracle didnt' recognize the NEW last number. I read something that said Access doesn't "commit" till the end of the insert query. Could this be it? Is Oracle EXPECTING to insert, then commit, record by record?? If so, how can I include this in my Access query? "Jerry Whittle" wrote: The trigger "should" automatically grab the next sequence number as you insert the records. It's probably fired during the BEFORE INSERT event on the table. I don't think that ODBC will mess it up; however, it's always worth testing first. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: Thanks Jerry. I wrote the query in Design View of Access, rather than VBA directly. It is a multiuser app, so of course that point is well taken. I was under the assumption that Access would be reading the available largest Unique ID on the fly (as records are appending). Sounds like that's not the case. There is a trigger in Oracle as you guessed, but I don't know how to "trigger" this from the action query. I have a datasource representing the records to insert and I append this to the actual dB. The code you offer below, does indeed look like the Sequence information in Oracle. How would include this in my query? OR are you even saying this. I'm a novice ... your help is greatly appreciated. Jim "Jerry Whittle" wrote: For the most part an Oracle sequence is run by a trigger on the table. So you should normally let Oracle get the next unique ID as the records are appended. If for some reason you do need to do this, there are problems. If this is a multi-user app, you don't know if someone else is inserting a record at the same time. That could cause a problem or two. As far as I know, to update a sequence to a different number manually, you need to drop the sequence then recreate it with something like below. I'm not sure that you can do this sort of DDL over an ODBC connection. DROP SEQUENCE COINS.AMCRTE_ID; CREATE SEQUENCE COINS.AMCRTE_ID START WITH 105527 MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER; DROP PUBLIC SYNONYM AMCRTE_ID; CREATE PUBLIC SYNONYM AMCRTE_ID FOR COINS.AMCRTE_ID; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Jim" wrote: I wrote an append query that successfully gets new Unique ID's, loads them as the append query runs, and appends to the Oracle tables just as expected (OBDC connection). However, the Sequence Generator in Oracle IS NOT updated to recognize the newly inserted records. What do I do to make this happen? The current result is any new additions after the action query "think" the sequence begins at the number just prior to the inserted records (as if they weren't successfully inserted) ... but they were. Help! ================================ |
Thread Tools | |
Display Modes | |
|
|