Connecting to Oracle Database

[nepotist]

Hello,
I am trying to create a table in Oracle through Excel. The version that I using is Oracle 10g and Excel 2010. I am using Microsoft's ODBC driver to Oracle to do the same.

Things that I have done so far:
Control Panle ---> Administrator Tools --> Data Sources (ODBC) --> and added Microsoft ODBC for Oracle under User DNS and System DNS. It does not ask me for any password when I do the above setting.

I am initially trying to establish a connection to database using a following Code;
Dim db As Database
Dim ws As Workspace

Dim ConnString As String
Dim LConnect As String

On Error GoTo Err_Execute
'Use ODBC Connection
LConnect = "ODBC;DSN=DataTool;UID=vinod;SERVER=VinodLocal"

'point to current workspace
Set ws = DBEngine.Workspaces(0)

'connecto to Oracle Database
Set db = ws.OpenDatabase("", False, True, LConnect)
db.Close
Upload = True
MsgBox "Connection Successful"
Err_Execute:
MsgBox Err.Description
Upload = False
End Function

At the LConnect string there should be a PWD parameter which should be set to match the password associated with the userid. Though the above code does not have the password parameter, when the code is run, it prompts for one.

I have tried all the passwords that I can think of but it fails to establish a connection to oracle database. Could some one please point in the right direction. I would greatly appreciate it.

Thank you

NOTE: I can login to the oracle database fine (not through VBA) and have tried the same credentials in VBA but still it isnt able to establish a connection

[Bob Phillips]

Go back to the DSN dialog, and click the Advanced button, you should be able to add a login and passowrd there.

[nepotist]

I don't see any advanced button :(. I about to start pulling my hair. I need some good directions :( :'(

[Bob Phillips]

This is for Access, I don't have Oracle, but I would think it is similar

[nepotist]

xld,

I am using Windows 7 and there is no advance option. There is a options buts , but when i click there there are no input fields for user ID or password

[Bob Phillips]

I have Windows 7 too, and it is there on mine ... albeit for an Access driver as I said.

[CareerChange]

Take a look at this site and see if you can figure out what you may be missing.

http://www.connectionstrings.com/oracle

[nepotist]

Hello XLD,
you are adding a ODBC driver for access and I agree that there is a advance option that lets you to input userID and Password. I am trying to add Microsoft's ODBC driver for Oracle. I have tried to even add driver for excel (which I believe is need when I am trying to connect to another excel workbook. Excel does not give an option of UserID and Password.

@Careerchange. I have looked at that site and still nto able to figure out what is wrong with my portion of code.