VBA connection to oracle (1 Viewer)

[DVL]

Dears,

I am trying to create a VBA application which has a button that imports data from oracle database.
I searched the net on how to connect to oracle via VBA, and execute the import but couldnt find anything

Can anyone help ?

And is there a link that describes the import commands from oracle to access and how to use them ?

 

[Ron_dK]

Would this help :

http://www.techonthenet.com/access/questions/oracle_connect.php

or this :

http://www.johnsmiley.com/cis18/Smiley004.pdf

 

[DVL]

Thanks alot, the secound link is exactly what i needed

is there a link that describes the import commands from oracle to access and how to use them ?

 

[Ron_dK]

Not sure, but would this help :

http://download.oracle.com/docs/cd/B12037_01/server.101/b10825/exp_imp.htm

 

[DVL]

Thanks Ron_dK for your reply, unfortunately the links are for ORACLE emp/exp commands..
I need VBA commands, to import data into access database

 

[Banana]

I think the issue is that Oracle wouldn't even know how to respond to VBA's command even if you managed to send them such. Thus when working with other data source, SQL is the only way to do it. Since you want to import, all you would need to do is send a single SQL statement per table:

SELECT * INTO MyNewLocalTable FROM OracleTable IN ''ODBC;<full connection string>;"

 

[DVL]

Thanks Banana, actually thats exactly what i needed to do..
(query oracle database from access and import the data into the access database..)

do you have any links where i can find extra information on the same ? or sample codes ? I am a newbie to access

 

[Banana]

If you need a connection string, two sites are good to start with: Carl Prothman's connection site and connectionstrings.com. You can google for both and look at what you need for Oracle. Of course, it goes without saying you need to have Oracle's ODBC driver.

I don't have specific reference on Oracle, but would encourage you to play a bit by using Access's File -> Get External Data -> Import Table. You should be then prompted to select a DSN (or create one if you need it), then you would select Oracle's driver and fill in all credentials (usually server's name, database name, your username & password) then select the tables you want to import.

Once you've had a successful experiment, we can then look into doing it in a more automated fashion if needed.

HTH.

 

[DVL]

well, I am quiet comfortable with the manual process, I've been importing/exporting table into and from access for a while now and, I want to take it to the next level and automate the process

 

[Banana]

Sorry to ask yet another question, but exactly how do you want it automated? Do you just want to import on a regular basis? Would you consider linking to Oracle table which would be used in an append query to make the process simpler?

The simplest way to set up is to use the SQL I gave you earlier and filling in the connection string, unless there's more to than just importing...

 

[DVL]

Don't worry Banana, reallly appreciating your assistance

well, I will explain what i am trying to do, it might help..

I am trying to create a small application in access, with two buttons, one button imports data from oracle into the access table (based on an SQL query), then the other button export the same table into MSSQL database.

I am trying to find the code syntax for connecting to the database and importing from oracle, but i am not able to find anything..