vmandem
asked on
How to resolve this error:IM006: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed.
I'm using RDO method to connect to my database.
I'm using connection string like this:
Private cn As rdoConnection
Dim En As rdoEnvironment
Dim CPw As rdoQuery
Dim Rs As rdoResultset
Dim Conn As String
g_sUsername: "test"
g_spassword: "test"
Me.Databasename = "testdatabase"
Conn = "UID=" & g_sUserName & " ;" & _
"PWD=" & g_sPassword & ";" & _
"driver={Microsoft ODBC for Oracle};" _
& "CONNECTSTRING=" & Me.DatabaseName & ";"
Set En = rdoEnvironments(0)
Set cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn)
I get the above error at Set cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn).
I just now installed Oracle 9i client it is giving me this error. It worked fine with Oracle 8.0 client.
What is wrong I'm doing.
I checked all the refernces and everything is fine.
I'm using connection string like this:
Private cn As rdoConnection
Dim En As rdoEnvironment
Dim CPw As rdoQuery
Dim Rs As rdoResultset
Dim Conn As String
g_sUsername: "test"
g_spassword: "test"
Me.Databasename = "testdatabase"
Conn = "UID=" & g_sUserName & " ;" & _
"PWD=" & g_sPassword & ";" & _
"driver={Microsoft ODBC for Oracle};" _
& "CONNECTSTRING=" & Me.DatabaseName & ";"
Set En = rdoEnvironments(0)
Set cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn)
I get the above error at Set cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn).
I just now installed Oracle 9i client it is giving me this error. It worked fine with Oracle 8.0 client.
What is wrong I'm doing.
I checked all the refernces and everything is fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please show the new connection string as you have it.
ASKER
Sure!
Conn = "Driver={Microsoft ODBC Driver for Oracle};" & _
"Server=aep_test; Uid=backoffice; Pwd=backoffice"
Set En = rdoEnvironments(0)
Set cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn)
VM
Conn = "Driver={Microsoft ODBC Driver for Oracle};" & _
"Server=aep_test; Uid=backoffice; Pwd=backoffice"
Set En = rdoEnvironments(0)
Set cn = En.OpenConnection("", rdDriverNoPrompt, False, Conn)
VM
ASKER
Hello Leon, r u there.
vm
vm
Is "aep_test" the name of your server?
If it is, look in the tnsnames.ora file (it should be on your C:\ drive) for an entry like
aep_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hossxora02)(PORT = 1521))
)
(CONNECT_DATA = (SID = LABEL))
)
Can you find it?
If it is, look in the tnsnames.ora file (it should be on your C:\ drive) for an entry like
aep_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hossxora02)(PORT = 1521))
)
(CONNECT_DATA = (SID = LABEL))
)
Can you find it?
ASKER
Yes it is there, even I tried other servers too which I'm on network, but I get the same error.
vm
vm
Hmm, do you have multiple Oracle clients installed on your machine? You can see this by bringing up the Oracle Home Selector, which is located under the Program heading of Oracle Installed Products.
ASKER
No only one and that is Oracle 9i client. I can connect to the server with the same username,password using
SQL PLUS editor.
VM
SQL PLUS editor.
VM
ASKER
Leon
Alternatively I tried using ADO but I get an error using ADO too, but different one. My code
looks like this for ADO
Dim Conn As ADODB.Connection
Dim AdoCmd As ADODB.Command
Dim ORCLServerConnect As String
ORCLServerConnect = "driver={Microsoft ODBC for Oracle};" & _
"PWD= backoffice ;" & _
"UID= backoffice;" & _
"server= aep_test;"
With Conn
.ConnectionString = ORCLServerConnect
.Open
End With
I get an error as:
[Microsoft][ODBC driver for Oracle][Oracle]
I don't really understand what is happening to my connection. It worked fine with Oracle 8i
without any change in my code but now with 9i seems to be not working.
I really appreciate your help here
VM
Alternatively I tried using ADO but I get an error using ADO too, but different one. My code
looks like this for ADO
Dim Conn As ADODB.Connection
Dim AdoCmd As ADODB.Command
Dim ORCLServerConnect As String
ORCLServerConnect = "driver={Microsoft ODBC for Oracle};" & _
"PWD= backoffice ;" & _
"UID= backoffice;" & _
"server= aep_test;"
With Conn
.ConnectionString = ORCLServerConnect
.Open
End With
I get an error as:
[Microsoft][ODBC driver for Oracle][Oracle]
I don't really understand what is happening to my connection. It worked fine with Oracle 8i
without any change in my code but now with 9i seems to be not working.
I really appreciate your help here
VM
I am sorry I was having some E-mail problems. Try this function to connect to Oracle using ADO. It uses an Oracle driver, but you shaould have it with your client install. I know the function works, because I am using it :)
Public cnConn as ADODB.Connection
Public Function OpenConnOracle() As Boolean
Dim strConn As String '/ Connection string to open database
OpenConnOracle = True
On Error GoTo ERROR_FUNCTION
If cnConn Is Nothing Then
Set cnConn = New ADODB.Connection
If cnConn.State = adStateClosed Then
strConn = "Provider=OraOLEDB.Oracle. 1;Persist Security Info=False;" & _
"User ID=backoffice;Password=bac koffice;Da ta Source=aep_test"
With cnConn
.CursorLocation = adUseClient
.Open strConn
LogError "OpenConnOracle", 0, "Connection Opened."
End With
End If
End If
EXIT_FUNCTION:
Exit Function
ERROR_FUNCTION:
OpenConnOracle = False
LogError "OpenConnOracle", Err.Number, Err.Description
Err.Clear
MsgBox "OpenConnOracle error: " & Err.Number & " " & Err.Description
GoTo EXIT_FUNCTION
End Function
Public cnConn as ADODB.Connection
Public Function OpenConnOracle() As Boolean
Dim strConn As String '/ Connection string to open database
OpenConnOracle = True
On Error GoTo ERROR_FUNCTION
If cnConn Is Nothing Then
Set cnConn = New ADODB.Connection
If cnConn.State = adStateClosed Then
strConn = "Provider=OraOLEDB.Oracle.
"User ID=backoffice;Password=bac
With cnConn
.CursorLocation = adUseClient
.Open strConn
LogError "OpenConnOracle", 0, "Connection Opened."
End With
End If
End If
EXIT_FUNCTION:
Exit Function
ERROR_FUNCTION:
OpenConnOracle = False
LogError "OpenConnOracle", Err.Number, Err.Description
Err.Clear
MsgBox "OpenConnOracle error: " & Err.Number & " " & Err.Description
GoTo EXIT_FUNCTION
End Function
ASKER
leon
since I'm using Microsoft ODBC for Oracle, I have to use this, if I switch to OLE DB Provider then
i have to reconfigure in lots of code.
1)WHEN I SAY LIKE THIS I GET ERROR AS:
ORCLServerConnect = "driver={Microsoft ODBC for Oracle};" & _
"PWD=" & g_sPassword & ";" & _
"UID=" & g_sUserName & ";" & _
"server=" & Me.ServerName & ";"
With Conn
.ConnectionString = ORCLServerConnect
.Open
End With
ERROR:[Microsoft][ODBC driver for Oracle][Oracle]
2)WHEN I SAY LIKE THIS I GET ERROR AS:
ORCLServerConnect = "driver={Microsoft ODBC Driver for Oracle};" & _
"PWD=" & g_sPassword & ";" & _
"UID=" & g_sUserName & ";" & _
"server=" & Me.ServerName & ";"
With Conn
.ConnectionString = ORCLServerConnect
.Open
End With
ERROR:[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
I appreciate your response.
VM
since I'm using Microsoft ODBC for Oracle, I have to use this, if I switch to OLE DB Provider then
i have to reconfigure in lots of code.
1)WHEN I SAY LIKE THIS I GET ERROR AS:
ORCLServerConnect = "driver={Microsoft ODBC for Oracle};" & _
"PWD=" & g_sPassword & ";" & _
"UID=" & g_sUserName & ";" & _
"server=" & Me.ServerName & ";"
With Conn
.ConnectionString = ORCLServerConnect
.Open
End With
ERROR:[Microsoft][ODBC driver for Oracle][Oracle]
2)WHEN I SAY LIKE THIS I GET ERROR AS:
ORCLServerConnect = "driver={Microsoft ODBC Driver for Oracle};" & _
"PWD=" & g_sPassword & ";" & _
"UID=" & g_sUserName & ";" & _
"server=" & Me.ServerName & ";"
With Conn
.ConnectionString = ORCLServerConnect
.Open
End With
ERROR:[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
I appreciate your response.
VM
Where are a lot of differences between RDO and ADO which would require code changes, but not if you are just changing drivers from ADO MS driver to ADO Oracle driver.
Anyway, the way MS suggests (http://support.microsoft.com/default.aspx?scid=kb;EN-US;174679), it should be:
Conn = "UID=<user ID>;PWD=<password>; driver={Microsoft ODBC for Oracle};SERVER=RonOracle;"
Just for testing try creating a DSN and use that. If that works then we know we need to dig more. If it doesn't then something else is the matter.
Anyway, the way MS suggests (http://support.microsoft.com/default.aspx?scid=kb;EN-US;174679), it should be:
Conn = "UID=<user ID>;PWD=<password>; driver={Microsoft ODBC for Oracle};SERVER=RonOracle;"
Just for testing try creating a DSN and use that. If that works then we know we need to dig more. If it doesn't then something else is the matter.
ASKER
01S00: [Microsoft][ODBC driver for Oracle]Invalid connection string attribute.
I don't no what is wrong in that. will you please correct me what is wrong.
VM