Results 1 to 6 of 6
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Writing Oracle data to a local table (A2k SR1a)

    I'm probably overlooking something blindingly obvious, but here's the situation:
    I have an Access front end to an Oracle database, which uses ADO and the Oracle OLEDB provider to retrieve data. I don't want to use linked tables if I can avoid it as it seems to be slower from the limited testing I've done. Is there any way (easy or otherwise!) of returning the data into a local table? (I can't create tables in the Oracle database for licensing reasons) I have tried loading the data into an array and then loading that into a local table but it seems to be remarkable slow - I may of course be going about it the wrong way!
    The Oracle version is 7.3 though it will soon be upgraded to 8, so if there's a solution involving the Oracle Objects for OLE that I believe is provided with version 8, that would be great.
    All assistance gratefully received!
    Regards,
    Rory

    Microsoft MVP - Excel

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing Oracle data to a local table (A2k SR1a)

    Do you actually *need* a local table? You can simply persist the recordset without creating a table at all.
    Charlotte

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Writing Oracle data to a local table (A2k SR1a)

    I did think of that, though I have yet to figure out the actual mechanics of doing so, but there has been talk recently of this database turning into a data warehouse so I think at some point I will have to address this issue. I suspect I may have to persuade the powers that be that Access is not going to be suitable for this task beyond the short term! Unfortunately the cheapest ready-built system that will do everything we need will cost
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Writing Oracle data to a local table (A2k SR1a)

    I would suggest you not write off the idea of linked tables too fast - they are often fast enough, especially if you can get Oracle to limit the amount of data you need. The advantages of working with what appears to be a table from the Access perspective often outweighs the speed advantage of OLEBD. We run a linked database with several multi-million record tables that are displayed on a look-up form with sub-second display time. In your case you could easily create queries that do calculated values, you can bind forms to recordsets, and so on. I've never tried it with Oracle, but we do occasionally use a pass-through query to get SQL Server data when speed is the principal concern.
    Wendell

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Writing Oracle data to a local table (A2k SR1a)

    Wendell,
    Thanks for that - I suspect that given the time constraints upon me (I've got about 2 weeks to do this on top of my normal work!) I'll probably go with your suggestions and leave figuring out an OLEDB way for another day. Apart from anything else, a few minutes delay rather than the week or so the process is currently taking shouldn't really concern the users! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I'll just have to make sure they can't get at the linked tables as I have some very, um, inquisitive users....(much like I used to be, so I can't really blame them)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing Oracle data to a local table (A2k SR1a)

    No, you would have to use a query for that. You can manipulate the persisted recordset, reading and writing to it, but there's no way to convert a recordset to the equivalent of a table or query except by stepping through it and writing the contents to a table. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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