Append Query w/sequence.NextVal to Oracle DB?

[Jack]

I have an Access front end linked to an Oracle database via MS Oracle ODBC.

How do I use the sequence.NextVal in an Access append query to the Oracle DB?

I tried this approach, which does not work:

INSERT INTO FUEL_CAPTURE ( RECORDID, FuelID, FactionID )
SELECT RECORDIDFUEL_CAPTURE_SEQ.NextVal, Fuel.FUELID, Fuel.FACTIONID
FROM Fuel;

Notes:
RECORDID is the primary key in the Fuel_Capture table. The name of the
sequence defined in the Oracle database is RECORDIDFUEL_CAPTURE_SEQ.
Fuel and Fuel_Capture are tables in the Oracle database.

Thanx,
J

 

[[MVP] S.Clark]

My best WAG would be to use a Pass Through Query. Otherwise Jet will have
no idea what you're talking about.

 

[Jack]

I solved the problem by creating a trigger in the Oracle database:

create trigger TI_FUEL_CAPTURE
before insert on FUEL_CAPTURE
for each row
begin
select RECORDIDFUEL_CAPTURE_SEQ.NextVal
into :new.RECORDID
from dual;
end;

and now this version of the Access append query works (SQL view):

INSERT INTO FUEL_CAPTURE (FuelID, FactionID)
SELECT Fuel.FUELID, Fuel.FACTIONID
FROM Fuel;

BTW: It is not necessary to specify the sequence field (RECORDID) and it is
not necessary to make this a pass-through query in Access.

J