Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    18

    Unanswered: I still need help with this!

    I posted this yesterday and I received some help but I still have questions. It was suggested that I do Create table script with this insert statement, but the table already exists in my Oracle database. Any other ideas why this insert query will not run in Access 97 as a pass-through query.

    Here is my query again:

    Our current system is in Access. We are now creating a new system in PeopleSoft with an Oracle backend database. I need to insert the data in the Access database into the Oracle database. Here is what I have:

    Insert into sysadm.PS_KM_Hearing_Test(NATIONAL_ID, DATETESTED, L500, L1K, L2K, L3K, L4K,
    L6K, L8K, R500, R1K, R2K, R3K, R4K, R6K, R8K, TSHIFT, TSL, TSR, LASTUPDOPRID, LASTUPDDTTM, EFFDT, COMMENTS)
    SELECT NVL("Hearing Test".SSNO,' '), To_date("Hearing Test".TDATE,'MM/DD/YY'), NVL("Hearing Test".L500,' '), NVL("Hearing Test".L1K,' '), NVL("Hearing Test".L2K,' '), NVL("Hearing Test".L3K,' '), NVL("Hearing Test".L4K,' '), NVL("Hearing Test".L6K,' '), NVL("Hearing Test".L8K,' '), NVL("Hearing Test".R500,' '), NVL("Hearing Test".R1K,' '), NVL("Hearing Test".R2K,' '), NVL("Hearing Test".R3K,' '), NVL("Hearing Test".R4K,' '), NVL("Hearing Test".R6K,' '), NVL("Hearing Test".R8K,' '), NVL("Hearing Test".TSHIFT,' '), NVL("Hearing Test".TSL,' '), NVL("Hearing Test".TSR,' '), NVL("Hearing Test".UpdatedBy,' '), to_date("Hearing Test".DateLastUpdated,'MM/DD/YY:hh:mi:ssam'), to_date("Hearing Test".DateCreated,'MM/DD/YY:hh:mi:ssam'), NVL("Hearing Test"."COMMENT",' ')
    FROM "Hearing Test";[/SIZE]

    I receive an error that states the table or view does not exist. I have never written a Pass through query and would appreciate any help.


    thank you all for any help you can give.
    Karen

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Have you tried using a linked table in access to Oracle and appending to the linked table?

    I can see why Oracle would throw a "Table does not exist" at you, because you do "Insert into ORACLETABLE () Select from AccessTable"

    Try the linked table.... It will work...

    Regards

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Insert into tableA [(columns)] Values (field1, field2)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jun 2004
    Posts
    18

    Thumbs up Thank you

    It worked. Thank you for all of your help.

    Karen

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I am curious as to how you solved it, if only for any other people having the same problem you are having.

    Greetz

  6. #6
    Join Date
    Jun 2004
    Posts
    18
    I followed your advice. I Linked the Oracle table and then referenced that table when I wrote my insert query.

    I can also right click on my Access table and export that table into my Oracle DB and then insert the data through SQL Plus or PL/SQL Developer.

    Thanks Again,

    Karen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •