CODEWITHSUNDEEP
vbaexcelodbcconnection-string
Ned
Ned

Reputation: 1207

How to specify "Service Name" in Oracle ODBC connection string

I have an Excel macro that has an Oracle database connection. When I call the macro, it fills out user name and password but not service name. User has to enter service name manually every time.

How can I specify it in the connection string?

Connection string:

CN.ODBCConnection.Connection = _
"ODBC;DRIVER={Oracle in OraClient11g_home2};" & _
"UID=" & inputUser & ";PWD=" & inputPassword & ";" & _
"HOST=" & inputHost & ";PORT=1521;DB=" & inputHost & ";" & _
"DefaultIsolationLevel=READUNCOMMITTED"

Excel prompt:

enter image description here

Upvotes: 1

Views: 8605

Answers (2)

Ned
Ned

Reputation: 1207

Found it. It's DBQ

New connection string:

CN.ODBCConnection.Connection = _
"ODBC;DRIVER={Oracle in OraClient11g_home2};" & _
"DBQ=" & inputHost & ";UID=" & inputUser & ";PWD=" & inputPassword & ";" & _
"HOST=" & inputHost & ";PORT=1521;DB=" & inputHost & ";" & _
"DefaultIsolationLevel=READUNCOMMITTED"

Upvotes: 4

Jimmy Smith
Jimmy Smith

Reputation: 2451

It depends on your driver, check this out

But without a config file, you have to specify the options in the connection string. Give this a try,

CN.ODBCConnection.Connection = _
"ODBC;DRIVER={Oracle in OraClient11g_home2};" & _
"SERVICE_NAME=" & inputHost & ";UID=" & inputUser & ";PWD=" & inputPassword & ";" & _
"HOST=" & inputHost & ";PORT=1521;" & _
"DefaultIsolationLevel=READUNCOMMITTED"

Also, make sure inputHost should be used in both cases where it is currently. One should be an instance of a server with the other being the database within it.

Upvotes: 1

Related Questions