access to oracle

knowledge · Post by **knowledge** » Wed Dec 10, 2008 9:03 am

Hi ,

My source is MS access but I can not load using ODBC as datastage is installed on unix and access is windows ..
We do not have drivers for ODBC ..
What is the other way I can load MS access to Oracle.

Can we do it on Database (MS access - Oracle) level without using Datastage ?
I found one tool which converts Access to oracle(online) but it is one time load and converts from local copy of database ( on my desktop)
I want to conect to remote Ms access database ,extract data and load into staging area in oracle tables .

Please guide ....

Thanks.

chulett · Post by **chulett** » Wed Dec 10, 2008 9:06 am

You can do separate steps - one to dump the Access data to files and transfer them to UNIX, then a DataStage job to load to Oracle. An Enterprise Scheduler can easily tie them together. Or purchase ODBC drivers and do everything from DataStage.

knowledge · Post by **knowledge** » Wed Dec 10, 2008 9:24 am

Thanks for quick replay,

How can i dump the Ms access into files ...i have read priveleges to MS access database ( on remote server) ..there is no funding to buy ODBC drive ...have to find some round about way to load data on regular basis ...

chulett · Post by **chulett** » Wed Dec 10, 2008 9:33 am

There must be some "Access People" there you can work with to accomplish that. Me, I have no clue and don't really want one.

knowledge · Post by **knowledge** » Wed Dec 10, 2008 9:39 am

Thanks ,

I will look into it ...

where can i find more info on odbc and its requirement...?

chulett · Post by **chulett** » Wed Dec 10, 2008 9:57 am

For DataStage? The pdfs in your PC's "Docs" directory. Otherwise, the vendor: http://www.datadirect.com/index.ssp would be a good place, I would think.

knowledge · Post by **knowledge** » Wed Dec 10, 2008 10:07 am

Thanks .

vinnz · Post by **vinnz** » Wed Dec 10, 2008 3:41 pm

You could check out Oracle Heterogeneous Services which is supposed to enable access to a non-oracle database using oracle - this would require some DBA work. Hope that helps.

knowledge · Post by **knowledge** » Fri Dec 12, 2008 1:07 pm

Not sure what it is ...we r still looking for other options, i will try to get more information on it ,
Thanks .

dslisa · Post by **dslisa** » Fri Dec 12, 2008 1:40 pm

Knowledge, don't know if this will help you. Can you convert your Access Database into a .csv file and then read it in Datastage as a sequential file? Once you are able to read it you should be able to do what ever you need to do with it. Once the .csv is created FTP it from windows to your UNIX source directory before doing anything else. Don't know how big your Access database is and how frequently it is updated? You would manually need to do the .csv conversion process or write a Macro to do it.

knowledge · Post by **knowledge** » Fri Dec 12, 2008 3:32 pm

hi lisa ,

Thanks , That is one of the option we r planning to implement ,
But again many factors r involved ...

ray.wurlod · Post by **ray.wurlod** » Fri Dec 12, 2008 7:17 pm

The present tense plural form of the verb "to be" is spelled "are", not "r".
DSXchange is not a mobile telephone, and we expect users to employ professional standards of written English, not least because it is a difficult enough language for those whose first language is not English without resorting to SMS-style abbreviations and other pretensions.

knowledge · Post by **knowledge** » Fri Dec 12, 2008 9:42 pm