Importing records from Oracle to Access

[oni29]

I'm using the following tools/systems:

* Oracle 9i Enterprise Edition Release 9.2.0.5.0
* Microsoft Access 2002
* Access is running on a XP Professional desktop

What I'm doing at the moment is:

* Linking the Oracle tables in Access via ODBC
* Using Access to run develop & run queries against the linked Oracle tables

What I'm trying to figure out:

* What is the easiest way to export the query results to a local (on my machine) Access table?

Some of the queries return < 65000 rows, so I can export to excel & import the excel file back into access. Unfortunately this doesn't work for the bigger queries.

Suggestions?

 

[w1dge]

I'm using the following tools/systems:

* Oracle 9i Enterprise Edition Release 9.2.0.5.0
* Microsoft Access 2002
* Access is running on a XP Professional desktop

What I'm doing at the moment is:

* Linking the Oracle tables in Access via ODBC
* Using Access to run develop & run queries against the linked Oracle tables

What I'm trying to figure out:

* What is the easiest way to export the query results to a local (on my machine) Access table?

Some of the queries return < 65000 rows, so I can export to excel & import the excel file back into access. Unfortunately this doesn't work for the bigger queries.

Suggestions?

I'm not sure why you would want to export to Excel and then import as you have already done the hard part in linking and developing the queries!

If you want to keep the data in Access, the easiest thing to do is change your queries from ordinary Select queries to Append queries. That way, you can insert the data from your Oracle data tables into your local Access tables .

The only problem you will have is if you keep running the queries, you may end up with multiple copies of the data in your local Access tables. To get around this, just delete all the data from your local tables before you run the Append queries


You can even do all this through code, so there will be no need for you manual link to your ODBC source

Hope this helps

W1dge

 

[The_Doc_Man]

Unless you are planning to do mulitple reports from one import operation, I'm with w1dge on this one. However, if you were planning to do many reports at once, then w1dge's idea to erase the local table and append from ORACLE to Access is sound.

My question is why you aren't using ORACLE's report capability. My second question is why you would EVER export from Access to Excel and then import back into Access from Excel? Your statement implies that Access would be doing the export in the first place. That just confuses the issue. Not to mention that it runs the risk of truncating something if you aren't careful, since Excel is noted for having a mind of its own during exports to it.

 

[oni29]

I didn't think of that

If you want to keep the data in Access, the easiest thing to do is change your queries from ordinary Select queries to Append queries. That way, you can insert the data from your Oracle data tables into your local Access tables.

Unfortunately it has been a while since I touched SQL in general. I didn't think of getting the query to handle inserting the data. I've found changing the query to an 'Access Make-Table Query' works quite well.

The only problem you will have is if you keep running the queries, you may end up with multiple copies of the data in your local Access tables. To get around this, just delete all the data from your local tables before you run the Append queries

I'm now using the above approach to generate three local tables. I've been deleting the tables prior to re-running the queries each time. This works nicely in with how I want the database to function.

 

[oni29]

I don't know that much Oracle

My question is why you aren't using ORACLE's report capability.

Unfortunately I don't know that much Oracle. Setting up a new database connection & running simple queries is the extent of it.

My goal is to have a simple local store of important data. I then use other applications (Excel etc..) to import the relevant bits.

 

[w1dge]

Unfortunately it has been a while since I touched SQL in general. I didn't think of getting the query to handle inserting the data. I've found changing the query to an 'Access Make-Table Query' works quite well.



I'm now using the above approach to generate three local tables. I've been deleting the tables prior to re-running the queries each time. This works nicely in with how I want the database to function.

Forgot to mention one quirk of Access, and that is how much space can be taken up by continually creating and deleting tables! It may be worth compacting and repairing once in a while, especially if you find that the database starts to slow down. Obviously, this will depend on how much data you are copying out from your Oracle database into Access, but I know for a fact on the database I work with, 60mb of data from Oracle equates to about a 500Mb Access mdb file!

W1dge