#  Other Applications & Softwares  > Access Tables & Databases >  >  How to regularly import data from Oracle database without linking the table?

## shadestreet

I have a table in Oracle that is updated with about 1,000 rows each day.  Currently I link this table to Access so I can run several reports using tables in Access that tie back to this Oracle table.

Two problems with this method:
1) When I am offline from my company intranet the database is pretty much useless as it cannot connect to the Oracle table - would like to be able to play around with the database whenever I want
2) Not sure I agree with some of the format output on the Oracle table, would like to convert text to date.

What is the simplest way to import the data from this table on a daily basis?  I only want to pull forward new records from the Oracle table, behind the scenes and automated if possible.

The only two methods I see available are:

1) Import the whole table one time only
2) Link to the table

The first option allows me to work offline but I can't get new records appended to the table

The second option is what I am currently doing

Help?

----------


## ConneXionLost

Hi shadestreet,

Maybe I'm not understanding the issue you're facing.  If you make a copy of the table (and keep that in your Access database), why can't you maintain the "up-to-date-ness" of that copied table with an append query while the master table (from Oracle) is available?

Cheers,

----------


## shadestreet

Thanks, just a bit of warning, I am a complete novice with Access.  I know the basics of what is possible but not the "know how" to achieve what I want to do.

In this case I thought of using an append query but got stuck pretty early on this step.

I know the SQL should look like this:

INSERT INTO TBL_ORACLE_COPY (Column1, Column2,...ColumnN)
SELECT Column1, Column2, ... ColumnN
FROM TBL_ORIGINAL_ORACLE_TABLE
WHERE date = sysdate-1 {whatever syntax to get me previous date}

HOWEVER, I don't know how to define the Oracle table to point Access in the right direction.  Do I need to put the server name?  DSN name?  Not sure how to tell Access to connect to the Oracle database through SQL.

Also, suppose I get this to work (and that would be great), can this be easily automated to run in the background say every morning at 6 a.m.?

----------


## ExlGuru

Hey i m telling you about a single module about how to connect Access with Oracle database by using VBA code:

First of all you'll have to create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.

To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.

SEE THE Attachment:

Then Set up ODBC connection.

Here is the example with the particular details you can change according to your settings.we've setup the Data Source with a name of AAAA, with a user name of BBBB, and an Oracle server called CCCC. You'll need to configure the ODBC connection.

See the attachment


Then create a module and usr this code:





```
Please Login or Register  to view this content.
```


Try this Hope it helps.

----------


## shadestreet

ExclGuru - thank you for the time you spent helping me.  I have the ODBC conncection setup and added that module to my DB, but not sure where to go from here.  I wasn't exactly clear what this module will do for me, haven't used them yet.  Right now I have the module in my DB and when I click on it I get the design view.  

next step?

----------

