7/03/2012

Create a pass-through query to retrieve the nextval from an Oracle sequence

Question: I can get nextval from sequence in Oracle with this command:
select seq_name.nextval from dual;
Is there any way to get nextval from the Oracle sequence through Access 2003/XP/2000/97 when docmd.runsql("select seq_name.nextval from dual") doesn't work?
I need to insert nextval (the same value) into two tables.
Answer: To get the nextval from an Oracle sequence, you will need to use a pass-through query in Access.
To create a pass-through query, create a new query. Select Design View and click on the OK button.




When the Show Table window appears, do not add any tables. Click on the Close button.


Select SQL View from the toolbar.


Enter your SQL statement. In this example, we're selecting the next value from the Oracle sequence called supplier_seq.


Finally, under the Query menu, select SQL Specific > Pass-Through.

No comments:

Post a Comment