Oracle and ADO command.execute- Create Trigger Fails

Have written a VB6 dll which reads any Access2000 Database and imports Tables and Data into
Oracle. ( Enterprise 8.1.7. running on Win2000 Server)

Dll uses ADO to connect to Oracle using Oracle's own OLE DB Provider. ie Provider=OraOLEDB.Oracle.
Code runs fine (20 Access tables and 50,000 records shunted into Oracle in @ 10mins)

But I want to automate migration further which means reproducing Access2000 AutoNumber fields.

I can create Sequences and Triggers manually on Access Primary Keys using DBA Studio or SQLPlus and everything works fine.

I can also create Sequences automatically in dll also:

 SQL = "CREATE SEQUENCE SEQ_2 INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER"  Set ThisCommand = New ADODB.Command
 Set ThisCommand.ActiveConnection = OracleConn

 ThisCommand.CommandType = adCmdText
 ThisCommand.CommandText = SQL
 ThisCommand.Execute , , adExecuteNoRecords

 But Creating a Trigger is DRIVING ME UP THE WALL!

CREATE TRIGGER TRIG_TEST1 BEFORE INSERT
    ON TABLE_TEST1
    FOR EACH ROW BEGIN
    SELECT SEQ_1.NEXTVAL
    INTO :NEW.ID
    FROM DUAL;END; Below is SQL for creating a Trigger...(same as above minus carraige returns)  SQL = "CREATE TRIGGER TRIG_TEST1 BEFORE INSERT ON TABLE_TEST1 FOR EACH ROW BEGIN SELECT SEQ_1.NEXTVAL INTO :NEW.ID FROM DUAL;END;" This above one-line string when cut and pasted in SQLPlus Worksheet Creates a Trigger perfectly.

But when ran thru' dll NOTHING HAPPENS!!

ie
 SQL = "CREATE TRIGGER TRIG_TEST1 BEFORE INSERT ON TABLE_TEST1 FOR EACH ROW BEGIN SELECT SEQ_1.NEXTVAL INTO :NEW.ID FROM DUAL;END;"   ThisCommand.CommandText = SQL
 ThisCommand.Execute , , adExecuteNoRecords

It appears to execute fine.No errors are generated. But a quick look in DBAStudio reveals a distinct lack of the Trigger!!

Just to be sure I wasn't being messed up by hidden characters I cut and pasted SQL for trigger from
VB6 back into SQLPlus. Trigger created perfectly! I logon using same user in SQLPlus Worksheet and dll. The user has sys priviliges so can pretty much do what it likes including CREATE ANY TRIGGER What I am missing????
I Would appreciate any pointers whatsoever! Received on Fri Mar 02 2001 - 12:46:56 CST