Into Access Database from Oracle

[uparna]

Hi,
I need to import data into an access database from Oracle. Right now i am using Microsoft Sql Server's Export/Import utility for doing the conversion.....does anyone have a more efficient and speedy form of conversion?


Best Regards,
Jayaram

Regards,
S. Jayaram Uparna .

 

[Turkbear]

Hi,
Why not use Access's Import function?

May not be more efficient, but at least it is the same program as you already have open..

( its under the File menu)

 

[kjnorman]

I would just let access odbc into oracle if the table is not that large - very easy.

If very large, take a look at Oracle Objects for OLE (OO4O), that should be installed as part of your client tools. Right some VBA code to use OO4O to connect to your database and tables and then use can control your ETL tasks more than you can using ODBC. You can specify chunksize and other such parameters to deal with large tables.

 

[uparna]

Hi KJNorman ,
The second option...the one with the Oracle Objects sounds very interesting as my table is a LARGE one... could you please throw some light on it? I would also like to remind thta this is some thing thta i have to do over the next 1 month only , so i cant afoord to have an app built for this one....





Regards,
S. Jayaram Uparna .

 

[kjnorman]

I can not give that much help unfortunately. I have only recently started using OO4O myself, and have been doing this to interface Excel into an Oracle 8 database. But I know it should work with Access as well.

OO4O is installed with the client side of Oracle 8. I do not know if it is include or called something else with higher versions of Oracle.

I would advise that you look at the Oracle Objects for OLE 2.3 documentation as it contains many useful examples and it can leapfrog your knowledge. I find it easier than ODBC coding and it is more optimized.

I had OO4O pulling data into Excel within an afternoon of first looking at the help files.

Though I have not specifically used these yet, the properties FetchSize and FetchLimit may interest you and GetChunk/AppendChuck if you are dealing with long or long raw fields. Fetch deals with calls to the database and storing of retrieved data into a local cache. You can use this to tune your data handling. The documentation is pretty sparse on how this works but do a google search and your will get more information.

Sorry I can not help much more at the moment.

Kerry

 

[uparna]

Hi KJNorman,
Thats wonderful ... but i doubt if i will be able to start using these things within an afternoon. Actually i installed the thing from OTN but just dont know what to do next and where to click. I know it sounds dumb.... but i would rather appear dumb and get the matter resolved than sit silently spending hours how to reinvent the wheel. Thank you for your continued support. Any idea what i have to do next??

Regards,
S. Jayaram Uparna .

 

[bookouri]

It is really easy to just set up an odbc connection and link your oracle table into access. Then run a make table query on the linked table. Even with tables with hundreds of thousands of rows you can easily import that way.