Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data not flowing from Oracle to Access

    I'm pulling down data from an Oracle DB into Access via VBA. The pertinent code is as follows, and is running ok:

    strSQL = "INSERT INTO TEMP_DAGENT (dur_signon) SELECT " & txtDatabase & "_DTA_DAGENT.dur_signon FROM " & txtDatabase & "_DTA_DAGENT;"
    Debug.Print strSQL
    db.Execute strSQL

    There are several identical databases in Oracle, represented by & txtDatabase &. All of them have identical _dta_dagent tables with identical fields, and the temp_dagent table in Access has identical fields for dur_signon. Problem is, even though there's data in dur_signon, it's not being dumped into Access. The immediate window is showing Oracle rotate between the various databases represented by & txtDatabase &, but my data isn't coming to Access.

    Any ideas as to why will be greatly appreciated, because I don't see anything glaring that would cause the problem.

    Thanks in advance.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Are those tables linked to Oracle?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes they are.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Does that mean you are using an ODBC Data Source (DSN) when linking to the Oracle tables, or are you using ADO? If it is ODBC, you should be able to see the data in Access, so I suspect that is not the case. You might try using the Oracle ODBC driver to link to the tables and see if that works. If it does, then I suspect your connect string isn't correct in ADO.
    Wendell

Posting Permissions

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