Re: Access / ODBC / Sequences

Tony Scott (asc_at_cix.compulink.co.uk) wrote:
: I've got Access connected to the Oracle tables I'm interested in via ODBC
: but in order to input new records I need to be able to get the numbers
: generated by an Oracle sequence. Can anyone tell me how I do this?

The only requirement for inserting/updating attached tables within Access is that the table must have a unique index on the server. If you're using a sequence to generate unique index values, the easiest way to populate that column is by creating a trigger like so:

create or replace trigger <trigger-name>

        before insert on <table-name> for each row
        declare key_value number;
        begin
            select <sequence-name>.nextval into key_value from dual;
            :new.row_key := key_value;
        end;

This way you can completely ignore that column, it will be automatically set to the next sequence value without any manual intervention.

-- 
========================================================================
"In sports, it's not who wins or   |   Mike Carmack
 loses; it's how drunk you get."   |   Vulcan Dragon -==(UDIC)==-
                 - Homer Simpson   |   mcarmack_at_freenet.columbus.oh.us