OraFAQ Forum: Server Utilities » how to load data from Ms Access to oracle

Home » RDBMS Server » Server Utilities » how to load data from Ms Access to oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to load data from Ms Access to oracle [message #70254]

Wed, 15 May 2002 05:57

babjee
Messages: 2
Registered: May 2002

Junior Member

I wanted to know how to load data using SQLLOADER (Direct & Parallel) from ms access to Oracle tables. I need to come up with a strategy to load literally 1000's of millions of records into 8-10 field tables in batch as quickly as possible. I like to know how to split a big ms table in small amount of csv files?.

Thank for ur help

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #70266 is a reply to message #70254]

Thu, 16 May 2002 06:51

Grant
Messages: 578
Registered: January 2002

Senior Member

Because Access doesn't allow you to create views I would say you will probably need to export the whole table and use another method to split it up. Export doesn't give you a lot of options.

You could create seperate tables based on queries of the original table to split up the table and then export into seperate csv files.

You could import the whole thing at once but I take it that is not waht you want to do.

I may get slammed for this but... You could use ODBC from access and link the table in Oracle and do insert queries. This would save the hassle of creating the csv files, moving them to the server and loading them one-by-one. You would be loading over the network so it would be slower. OK I said it!

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #70291 is a reply to message #70266]

Tue, 21 May 2002 00:35

Prathee
Messages: 9
Registered: May 2002

Junior Member

Hi
can we use SQL PLUS (8.1) to connect to an access DB?

I gave user name Admin
Password Admin
Host String mydb@ODBC

but it is not connecting . Is this the right way ?

Pls Tell me immediately
-Prathee

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #70293 is a reply to message #70266]

Tue, 21 May 2002 05:28

Grant
Messages: 578
Registered: January 2002

Senior Member

You connect from Access.

1. On your client create an ODBC Data Source Name (DSN) (under system tab) with the same name as the tns alias entry. For example, if you have a tns entry called PROD create a DSN called PROD using the Oracle ODBC driver. You can use Microsoft's ODBC for Oracle but I wouldn't recommend it.
2. Open your Access DB.
3. With your DB window open select File/External Data Source/Link Tables
4. In the link window select the drop down box "Files of type".
5. Scroll to the bottom and select "ODBC Database()".
6. Select the tab "Machine Data Source".
7. Select the DSN you created.
8. Log in.

Once you link a table it will show a world icon next to it in your table view. You can access it in the query window (I use SQL window) as PROD.TABLENAME and the local table will be TBALENAME with no world. I cannot teach Access so from here you are on your own. At this point you will be able to move records back and forth with insert/update/delete from the SQL worksheet. You will need to create primary/unique keys on the table I beleive to make the linked table updateable not sure any more. Oracle ODBC will ask or make the primary key/index on the linked table. Again it has been some time and ODBC/Access/Client/Oracle have all changed since I used this. It does work and is an option for you though. Nice thing about this is you can create a form/query and just click a button to do the job. Good luck.

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #71486 is a reply to message #70291]

Mon, 11 November 2002 21:31

B.Srikanth Patnayak
Messages: 6
Registered: November 2002

Junior Member

hi Prathee ,
You can always connect MS Access through Oracle.
1.First you odbc connection of your access database from control panel 32 bit odbc
2.conn from oracle scott/tiger@odbc:test
if test is the odbc string you have created earlier
it will connect to your .mdb and you can directly access the data from the tables.

Incase you have any problem ,please get in touch

srikanth

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #71558 is a reply to message #71486]

Fri, 22 November 2002 10:25

Pinckney Ma
Messages: 1
Registered: November 2002

Junior Member

I follow what you said and got:

ORA-03121: no interface driver connected - function not performed.
Warning: You are no longer connected to ORACLE.

What should I do next?

Thanks,

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #235009 is a reply to message #70254]

Thu, 03 May 2007 13:44

gunalmel
Messages: 5
Registered: May 2007

Junior Member

You can use Oracle Heterogeneous Service to connect to an Access DB from Oracle.

Follow the instructions on:
http://orafaq.com/node/60

Then you can issue standard SQL statements or can use PL/SQL on Access through the db link you created using the note above. It worked for me!

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #235025 is a reply to message #235009]

Thu, 03 May 2007 15:28

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think getting an answer 4 1/2 years after is a bit late.
Don't you think? Razz

Regards
Michel

Report message to a moderator

Re: how to load data from Ms Access to oracle [message #235243 is a reply to message #235025]

Fri, 04 May 2007 08:16

gunalmel
Messages: 5
Registered: May 2007

Junior Member

Yup, you're right. I am a new member and I have noticed the date after I replied. Next time I'll be quicker Razz

Report message to a moderator

Previous Topic:	How to estimate the size of export dump fille size?
Next Topic:	Importing from dmp file problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 14 07:51:46 CST 2025