Writing Oracle data to a local table (A2k SR1a)

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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! 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)

Reply | Quote

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.

Reply | Quote