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.

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vmandem

ASKER

I get this error when I try in the above methond you mentioned:

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

leonstryker

Please show the new connection string as you have it.

vmandem

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

vmandem

ASKER

Hello Leon, r u there.

vm

leonstryker

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?

vmandem

ASKER

Yes it is there, even I tried other servers too which I'm on network, but I get the same error.

vm

leonstryker

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.

vmandem

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

vmandem

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

leonstryker

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=backoffice;Data 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

vmandem

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

leonstryker

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.