Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cross DB query (2002 & 97)

    QUick question. Does anyone have a quick solution for doing a query incorporating information from an Oracle db and access db. All I could think of is to dump the oracle info into a temp table. If this is the only fix does anyone have a quick one for dumping a recordset to a table.

    Thanks, been out of access programming for a while and trying to do a quick fix to an existing application.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Cross DB query (2002 & 97)

    Through ODBC, you should be able to link to Oracle tables, then you can work with them from Access like any table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cross DB query (2002 & 97)

    Mark is correct, but one caution - if the Oracle table is large, Access may well try to pull the whole table in to memory to run queries - it depends on the indexing and the vagaries of the ODBC driver being used. If it does that, your queries are likely to be very slow. In that case, you may want to create an Access query to reduce the Oracle recordset size, and then join that on the Access table.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross DB query (2002 & 97)

    I should have stated that I wont dns-less connections. If you link it means you are required to setup the connection to the db on all PC's before the users can use the system.

    That was my reasoning behind temp tables, but I don't know how to dump the contents of a recordset into a temp table, without stepping through each field.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cross DB query (2002 & 97)

    I think you may be required to use ODBC connections with Access97, as ADO doesn't really work with it. In 2002 you may be able to get by with ADO if the version of Oracle you are using is OLE DB compliant, but you will need to confirm that. If you want to avoid having to deal with those kind of issues, then I think you are going to have to create a temporary table in Access from a workstation using ODBC connections to Oracle, and then connect other users to the Access back-end. That of course means that you have static data, and all users see the same data. Hope this all makes sense.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cross DB query (2002 & 97)

    I have been also working on this issue. It is slow through the ODBC. Also, depending which version of oracle and which version of Access and Windows you are running, you will need different ODBC drivers. If you have the misfortune to be running a P 4 computer with ORacle 7 or 8 there is special patch to download before the ODBC will work.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cross DB query (2002 & 97)

    I should add that if you use ODBC, you will have a couple of drivers to choose from - one from Oracle and One from Microsoft, and as Zave notes, there may also be different ones depending on the PC you use. The other solution to be considered would be to use the Oracle tools to dump the data to a delimited format, Excel format or whatever, and then import into Access.
    Wendell

  8. #8
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross DB query (2002 & 97)

    All very good but doesn't anyone know a quick way to populate a recordset into a table.

    I have rstData full with the data I require and want to put into a temp table, without looping through each field. Does anyone know how to do this?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cross DB query (2002 & 97)

    We need more information in order to try to help you. Is rstData a recordset in Access that you are manipulating in DAO or in ADO, and if it is, which version of Access are you using?
    Wendell

  10. #10
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross DB query (2002 & 97)

    access 2002 using ado

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cross DB query (2002 & 97)

    Ah, that helps. Unfortunately with ADO you don't have a persistent connection to the table so you can't use simple stored queries to do the trick. But you should be able to create a SQL statement that is executed in code that in one pass selects the appropriate data from Oracle, and uses an Insert statement to create a table in Access. Are you pretty comfortable with Access SQL statements?
    Wendell

  12. #12
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross DB query (2002 & 97)

    I have never used Access SQL statements, always Oracle or SQL Server.

    How does this work. I am using an ado connection to Oracle, so querying and returning to a recordset. How do I then insert this into access. I do not want to use linked tables.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Cross DB query (2002 & 97)

    Sorry for the long delay in responding - I spent all day at a client where I don't have Internet access <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    Any how, the basic syntax to create a query which does a make table would be something like:
    <font color=blue><font face="Georgia">SELECT * INTO myAccessTable From mySQLTable</font face=georgia></font color=blue>
    where mySQLTable is an Oracle table in a database you have created an ADO connection to, and myAccessTable is the table being created. Now I have never tried this using an ADO connected table, but it seems to me it should work. Unfortunately, at the moment my SQL Server is down, so I can't test it. It is possible it will try to create a table in the Oracle table, but I think the syntax for that would be different in Oracle so the query would fail rather than creating such a table. In any event, if you can figure out a way to do it, it should be considerably faster than steping through a recordset and creating an Access table one record at a time using VBA.
    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
  •