cancel
Showing results for 
Search instead for 
Did you mean: 

MS Access database

intp
Frequent Advisor

MS Access database

Hi,

I need to move data from oracle table to MS Access table. I dont want to do an import , cos it creates a new table in MS Access.

I have the empty structure (exact) of Oracle table in MS Access database. I need only the data to be pulled from oracle and insert it into MS Access table.

Any provision in MS Access?

Hope i'm clear.

~Jil
7 REPLIES
TwoProc
Honored Contributor

Re: MS Access database

Just create your ODBC connection into Oracle,
and then create your Oracle link into the Access database. Then create an insert query in Access, selecting from the Oracle tables, and inserting into the access (local ones).
We are the people our parents warned us about --Jimmy Buffett
intp
Frequent Advisor

Re: MS Access database

Problem is ..i have around 200 tables..i need to create link for each table ...link will create a new copy of table (with data) in MS Access.

~Jil
Jean-Luc Oudart
Honored Contributor

Re: MS Access database

Hi

you can use SQL script to generate a "dump" for each table.
The output format is character with a separator.
I've done the other way around (MS Access to Oracle) and the script generates the CTL file too.

interested ?

Regards
Jean-Luc
fiat lux
TwoProc
Honored Contributor

Re: MS Access database

Link doesn't actually create a copy. It just makes the link.

Yes, you'd have to repeat it 200 times, if you want the links to show up.

You can use the ODBC connection directly and from VB(within Access) open up a pass-through query right into Oracle, and skip the Link process. But, you will have traded that for a coding process in which you'd have to specify the 200 tables via code segments instead of clicking around. Might be better, might not...

There is a product called DataJunction which could probably assist you on this. I know groups here use that for moving data in and out of Oracle, Access, and SQLServer. I'm not familiar with it myself however.

Regardless of method, I'm pretty sure you're going to have actually specify what tables you want to pull from individually, and not a whole schema - much less a database.
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: MS Access database

hi ~jil,

Did you try the "Append data to existing tables" option when using the IMPORT option?

It works for me!

This is what i did:
1. Export data from Oracle table to xml format
2. Run MS access open MS access Database
3. File -> Get External Data -> Import ->
then on the imprt XML window, click options, then "Import OPtions" = "Append Data to Existing table(s)"

hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
rmueller58
Valued Contributor

Re: MS Access database

It has been my experience the ODBC import to an access temp table is one way, or you can create an import filter in and access query to map the fields..

I had created a macro with "button" to launch my import using the filter, it imported only the records I required and was fairly simple to setup.

rmueller58
Valued Contributor

Re: MS Access database

Make sure your Oracle ODBC driver is correct for your Oracle Version.