Can not connect to Oracle via VBA - Driver's SQLSetConnectAttr Failed

1
votes

I have created an ODBC connection for oracle using "Microsoft ODBC for Oracle" driver under user DSN. I can connect to oracle 10g using SQL developer but when i try to connect via ODBC its saying

[Microsoft][ODBC DRIVER Manager] Driver's SQLSetConnectAttr Failed

I have tried following code for connecting.

data_src = Oracle
Database = sys
Uid = <my id>
pwd = <my pwd>
Set cn=new ADOBB.Connection
Cn.open "DataSource=" + data_src + ";" + Database + "; persist security Infor = True; UserID= " + Uid + "; Password=" + pwd + "; SessionMode = ANSI;"

I have already connected to Teradata using same code with different setting. Please help me to connect Oracle 10g from VBA

1
oraclevbaexcelodbc
The concatenator for VBA is & not +, + can lead to problems with null values. - Fionnuala
But the same is working fine when i tried connecting Teradata using same code. - logan

1 Answers

1
votes

Known issue - see IBM support post. Solution - use the Oracle driver.

Also - connection strings are different between different database drivers. For example, a connection string for the Microsoft Oracle driver would look like

Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

while one for Oracle's own driver would be similar to

Driver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;

Share and enjoy.