topic Re: MS Access database in Operating System - HP-UX

MS Access database

intp — Thu, 20 Oct 2005 11:26:20 GMT

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

Re: MS Access database

TwoProc — Thu, 20 Oct 2005 11:29:03 GMT

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).

Re: MS Access database

intp — Thu, 20 Oct 2005 11:43:30 GMT

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

Re: MS Access database

Jean-Luc Oudart — Thu, 20 Oct 2005 11:55:23 GMT

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

Re: MS Access database

TwoProc — Thu, 20 Oct 2005 11:59:07 GMT

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.

Re: MS Access database

Yogeeraj_1 — Fri, 21 Oct 2005 01:04:58 GMT

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

Re: MS Access database

rmueller58 — Fri, 21 Oct 2005 09:06:21 GMT

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.

Re: MS Access database

rmueller58 — Fri, 21 Oct 2005 10:25:28 GMT

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