Microsoft Access Insert....Select using oracle as a backend 1

[Beilstwh]

I am using an Oracle Global Temp Table as a backend work table and an Oracle Perm table as a repository. My code follows

db.Execute "INSERT INTO ID_KEY ( insert_date ) values(now());"

    Set rs = db.OpenRecordset("SELECT @@IDENTITY;")
    showidentity = rs(0)
    rs.Close
    Set rs = Nothing
    
    db.Execute "INSERT INTO CUSTOM_ZONE_QUEUE(id,submitted_by,submitted_date,status,run_date) values(" & Str(showidentity) & ",'" & Me!myname & "',now(),'SUB', #" & Me!rundate & "#);"
    sqltext = "INSERT INTO CUSTOM_ZONE_QUEUE_LINES(ID,SEQ#,TYPE_CODE,ZONE_CODE,ZIP_CODE) SELECT " & Str(showidentity) & ",SEQ#,TYPE_CODE,ZONE_CODE,ZIP_CODE FROM LOAD_TABLE;"
    MsgBox sqltext
    db.Execute sqltext
    db.Execute "DELETE FROM LOAD_TABLE;"

The code all works except the insert using the select. sqltext is

"INSERT INTO CUSTOM_ZONE_QUEUE_LINES(ID,SEQ#,TYPE_CODE,ZONE_CODE,ZIP_CODE) SELECT 10,SEQ#,TYPE_CODE,ZONE_CODE,ZIP_CODE FROM LOAD_TABLE;"

I am getting error 3134. I can see nothing wrong with the code and I am able to use LOAD_TABLE with no problem. I know that I could make this into a recordset and do individual inserts, but I am stubborn. My version of Oracle is 8i and my version of access is 2003. Any help would be appreciated. Hopefully it is something simple and stupid.

Bill
Lead Application Developer
New York State, USA

 

[PHV]

Replace this:
SEQ#
with this:
[SEQ#]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Beilstwh]

Bingo, that was masking a bad column name (embedded space) and the code is repaired. Thank you very much.... Have a star.

Bill
Lead Application Developer
New York State, USA