User unable to access Oracle DB with the Oracle driver
I developed a VB6 application which uses ADO to connect to an Oracle 8i database. One of my test users (others do not have a problem) is unable to connect to the database using the Oracle driver. He has no problem using the Microsoft driver for Oracle and he is also able to connect by creating a DSN, using the same driver, to the database. We have reinstalled the Oracle client on his machine as well as confirmed the entry in the tnsnames.ora file.
Thanks for any help in advance.
Leon
Thanks for any help in advance.
Leon
seazodiac
have you tried to connect oracle8i database using SQLPLUS?
ASKER
Yes, that worked.
sorry, I was not getting email from this thread.....
So, what error message do you get when you try to create a DSN for oracle database using Oracle ODBC driver and test for the connection?
So, what error message do you get when you try to create a DSN for oracle database using Oracle ODBC driver and test for the connection?
ASKER
yeah, I had a similar problem yesterday with the email, but then it went away.
The user gets an error message:
3706 Provider cannot be found. It may not be properly installed.
The user gets an error message:
3706 Provider cannot be found. It may not be properly installed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We tried this before. It did not help, but I will try again, just to make sure. The user is in Ireland so I won't know if it worked till tomorrow.
but the error 3706 is clearly saying : oracle odbc driver is not there....i.e. not properly installed.
ASKER
True, but if it was not installed why are we able to setup a DSN? And I was able to use it to retrieve data into Excel as a test.
Wait a sec, don't you say you get 3706 error when you were trying to set up DSN using ORACLE ODBC DRIVER?
this is our posts:
Comment from seazodiac Date: 10/02/2003 12:06PM PDT
sorry, I was not getting email from this thread.....
So, what error message do you get when you try to create a DSN for oracle database using Oracle ODBC driver and test for the connection?
Comment from leonstryker Date: 10/02/2003 12:11PM PDT
yeah, I had a similar problem yesterday with the email, but then it went away.
The user gets an error message:
3706 Provider cannot be found. It may not be properly installed.
But now you were saying in the last post:
Comment from leonstryker Date: 10/02/2003 12:36PM PDT
True, but if it was not installed why are we able to setup a DSN? And I was able to use it to retrieve data into Excel as a test.
What driver are you using to set up a DSN this time??
this is our posts:
Comment from seazodiac Date: 10/02/2003 12:06PM PDT
sorry, I was not getting email from this thread.....
So, what error message do you get when you try to create a DSN for oracle database using Oracle ODBC driver and test for the connection?
Comment from leonstryker Date: 10/02/2003 12:11PM PDT
yeah, I had a similar problem yesterday with the email, but then it went away.
The user gets an error message:
3706 Provider cannot be found. It may not be properly installed.
But now you were saying in the last post:
Comment from leonstryker Date: 10/02/2003 12:36PM PDT
True, but if it was not installed why are we able to setup a DSN? And I was able to use it to retrieve data into Excel as a test.
What driver are you using to set up a DSN this time??
ASKER
The problem I had yesterday refered to the email problem with EE site.
I was able to setup DSN through ODBC window. I tested it by using it to connect from Excel to Oracle from the problem PC.
The 3706 Provider cannot be found. error occurs then the user attempts to connect with my application using a DSN less connection.
I was able to setup DSN through ODBC window. I tested it by using it to connect from Excel to Oracle from the problem PC.
The 3706 Provider cannot be found. error occurs then the user attempts to connect with my application using a DSN less connection.
ok, here are my questions to clear this up:
1. what driver are you using for setting up DSN in ODBC window?
2. In your application, what driver is specified to connect oracle database?
make sure these two are the same.
also my other question is that
why don't you change your VB application to use DSN? It's easy and hassle-less. you don't need to manually specify the connection string, thus reducing human errrors.
1. what driver are you using for setting up DSN in ODBC window?
2. In your application, what driver is specified to connect oracle database?
make sure these two are the same.
also my other question is that
why don't you change your VB application to use DSN? It's easy and hassle-less. you don't need to manually specify the connection string, thus reducing human errrors.
ASKER
seazodiac,
I have been coding for close to 10 years and the one thing I can definitely tell you is DSN is far from "hassle-less". Imagine 50 workstations in 4 countries with users who have not finished high school in some cases. Change managment and DSN rollout would be a total nightmare.
I would much rather handle the connection inside my class module as a black box, so even other developers would only have to change the connection string even if the client goes to a totally different database. In this particular case the move was from Access to Oracle.
I am fairly sure the driver used is exactly the same one, but I will double check again tomorrow.
I have been coding for close to 10 years and the one thing I can definitely tell you is DSN is far from "hassle-less". Imagine 50 workstations in 4 countries with users who have not finished high school in some cases. Change managment and DSN rollout would be a total nightmare.
I would much rather handle the connection inside my class module as a black box, so even other developers would only have to change the connection string even if the client goes to a totally different database. In this particular case the move was from Access to Oracle.
I am fairly sure the driver used is exactly the same one, but I will double check again tomorrow.
Dim p2_smr_connection As New ADODB.Connection
Dim p2_smr_command As New ADODB.Command
Dim p2_smr_rs As New ADODB.Recordset
p2_smr_connection.Connecti
p2_smr_connection.Mode = adModeRead
p2_smr_connection.CursorLo
p2_smr_connection.Open "Provider= MSDAORA;" & "Data Source=my_db" & ";" & _
"User Id=user" & ";" & "Password=pa" & ";"
If p2_smr_connection.State = adStateOpen Then
Text1.Text = Text1.Text & "Connected to p2_smr" & vbCrLf
Else
Text1.Text = Text1.Text & "Unable to connect.Try again after some time." & vbCrLf
Exit Sub
End If
p2_smr_command.ActiveConne
p2_smr_command.CommandText
p2_smr_rs.CursorLocation = adUseClient
p2_smr_rs.CursorType = adOpenDynamic
Set p2_smr_rs = p2_smr_command.Execute
ok this is the codei use to get myself connected and working.
this is the dsn less way the one i prefer .. least of hassles. If u are able to connect using sqlplus then this should also work .
please make sure though that u have msdaora.dll on ur machine and its registered. Usually available in the follwing location.
C:\Program Files\Common Files\System\ole db
if u don;t have this dll on your machine then please install mdac .
hope that really helps.
?5dsine
Dim p2_smr_command As New ADODB.Command
Dim p2_smr_rs As New ADODB.Recordset
p2_smr_connection.Connecti
p2_smr_connection.Mode = adModeRead
p2_smr_connection.CursorLo
p2_smr_connection.Open "Provider= MSDAORA;" & "Data Source=my_db" & ";" & _
"User Id=user" & ";" & "Password=pa" & ";"
If p2_smr_connection.State = adStateOpen Then
Text1.Text = Text1.Text & "Connected to p2_smr" & vbCrLf
Else
Text1.Text = Text1.Text & "Unable to connect.Try again after some time." & vbCrLf
Exit Sub
End If
p2_smr_command.ActiveConne
p2_smr_command.CommandText
p2_smr_rs.CursorLocation = adUseClient
p2_smr_rs.CursorType = adOpenDynamic
Set p2_smr_rs = p2_smr_command.Execute
ok this is the codei use to get myself connected and working.
this is the dsn less way the one i prefer .. least of hassles. If u are able to connect using sqlplus then this should also work .
please make sure though that u have msdaora.dll on ur machine and its registered. Usually available in the follwing location.
C:\Program Files\Common Files\System\ole db
if u don;t have this dll on your machine then please install mdac .
hope that really helps.
?5dsine
ASKER
dsine - Thank you, but my code is running fine on over a dozen machines and I prefer using Oracle driver as opposed to MS driver for Oracle it has more functionality. I do believe that something is wrong with the driver set up on the problem station.
ASKER
The de-install did not help, however afterward I had the user check his Home Selector and we found that he had 2 possibilities. I had the user scan his entire C and D drives for any Oracle type files and we found another tnsnames.ora file on the D drive. After deleting it and that entire directory we rebooted the PC again veryfied that he only had 1 Home and ran the application with success.
Thank you
Thank you