Home » RDBMS Server » Server Utilities » how to load data from Ms Access to oracle
how to load data from Ms Access to oracle [message #70254] Wed, 15 May 2002 05:57 Go to next message
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
Re: how to load data from Ms Access to oracle [message #70266 is a reply to message #70254] Thu, 16 May 2002 06:51 Go to previous messageGo to next message
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!
Re: how to load data from Ms Access to oracle [message #70291 is a reply to message #70266] Tue, 21 May 2002 00:35 Go to previous messageGo to next message
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
Re: how to load data from Ms Access to oracle [message #70293 is a reply to message #70266] Tue, 21 May 2002 05:28 Go to previous messageGo to next message
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.
Re: how to load data from Ms Access to oracle [message #71486 is a reply to message #70291] Mon, 11 November 2002 21:31 Go to previous messageGo to next message
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
Re: how to load data from Ms Access to oracle [message #71558 is a reply to message #71486] Fri, 22 November 2002 10:25 Go to previous messageGo to next message
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,
Re: how to load data from Ms Access to oracle [message #235009 is a reply to message #70254] Thu, 03 May 2007 13:44 Go to previous messageGo to next message
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!
Re: how to load data from Ms Access to oracle [message #235025 is a reply to message #235009] Thu, 03 May 2007 15:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
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
icon10.gif  Re: how to load data from Ms Access to oracle [message #235243 is a reply to message #235025] Fri, 04 May 2007 08:16 Go to previous message
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
Previous Topic: How to estimate the size of export dump fille size?
Next Topic: Importing from dmp file problem
Goto Forum:
  


Current Time: Wed Dec 25 12:15:32 CST 2024