nepotist
04-20-2011, 10:04 AM
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
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