How do I connect Oracle to MS Access?

[OhioSteve]

About two years ago, I made an ODBC connection on my workstation to an Oracle 8 db on a server. Everything worked great for two years. Then, within a one week period everything changed. The company gave me a new computer. Then, the Orace DB was moved to a new server, and it was upgraded to Oracle 10.

I remember how to reach the ODBC tool in administrative tools. But I don't remember anything else about what I did two years ago. I googled and found that something called "Oracle Sql*net" is needed.

How do I restore my connection?

 

[KenCunningham]

Hi Steve. Sorry, I don't have time to go into much detail, but try a keyword search for Access ODBC in this forum - there seem to be plenty of threads which might give you a clue. Good luck.

 

[sem]

In any case you should install Oracle (sql*net) client software first. Normally Oracle distribution contains ODBC driver also. Then you should configure sql*net (ask your DBA).

Regards, Dima

http://seminihin.narod.ru

 

[w860098]

Looking for HELP with the Merge statement.

I have created a number of different (simple) merge instructions without problem but I now have hit a difficulty which is affecting several others.
To isolate the problem I have simplified one of those but still have the same error condition (i.e. ERROR at line 7:
ORA-00904: "CF1"."LOCAL_CLAIM_NO": invalid identifier).

The SQL code I submitted to PL/SQL was :-

merge into scram_mart.claim_facts cft
using (
select nvl(dlr.Dealer_id,0) as dealer_id
from scram_stage.claim_stage csg
left outer join scram_mart.dealers dlr
on (csg.wo_no=dlr.local_dealer_no)) cf1
on (cf1.local_claim_no = cft.local_claim_no)
when matched then
update set
cft.Dealer_ID = cf1.Dealer_ID
when not matched then
insert (cft.Dealer_ID)
values (cf1.Dealer_ID);

Can anyone advise me the 'error of my ways' ?!

 

[KenCunningham]

Hmm. One of them might be to start a new topic in a totally unrelated thread ;-)

Try starting your own to generate responses on your problem and good luck with it.

 

[oradba101]

Hi, OhioSteve

You will need to do the following:

1. Install the Oracle client on your PC.
2. This should prompt you to setup the name of the database you are trying to access. You will also need the ip address or hostname of the server where the database resides.
3. After completing the above step then go into the Data Sources (ODBC), Click the add button, choose oracle in OraHome1 (or whatever the home name is). It will then prompt you to enter the Data Source Name. Then on the TNS Service Name select the name of the Oracle database.

If this is not detailed enough, please reply back.

Regards,

William Chadbourne
Oracle DBA

 

[OhioSteve]

On my new server, I was eventually able to create an ODBC connection. In the ODBC interface, I choose "system DSN" and pressed "add". I choose the driver "Oracle in [home]...". For some reason the normal Oracle driver does not work. In the next screen, there are four large text boxes. I filled them out as follows~

Data Source Name: -
Description: -
TNS Service name: myDatabase.world
User ID: username

I tested the connection. It prompted me for a user ID & password. It said "connection successful"!

Unfortunately, what I really need is for it to work on a workstation, NOT the server. We work with a firm that provides us with an application with an Oracle backend. The server actually has the real oracle on it. The clients have Oracle "insta-client", and that is installed with our application's interface. If you just have the "insta-client", you don't see "oracle in [home]..." in the ODBC interface.

I realized that the insta-client did not contain all of the drivers. I googled to

http://www.oracle.com/technology/software/tech/windows/odbc/index.html

and did the download at the top of the page. The unzip and install worked. After that, I tried the ODBC utility again. This time, I WAS able to choose "oracle in [home]...". But when I filled in the correct values, it said "driver could not be loaded due to system error 126". Ugh! So it still does not work.

 

[oradba101]

I believe you still have the option to install the regular Oracle client if you wish to. I have not tried the instant client so I am not able to offer any advice on that.

Regards,

William Chadbourne
Oracle DBA