VBA: connect to Oracle db, password has a special symbol

0
votes

I'm using this to connect to Oracle:

Set mDBConnection = New ADODB.connection    
Dim Rett As String
Rett = "CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
Rett = Rett & "(HOST=myhost)(PORT=1521))(CONNECT_DATA =(SERVICE_NAME = myservice)));"
Rett = Rett & "uid=" & mDBUser & ";"
Rett = Rett & "pwd=" & mDBPassword & ";"
MsgBox Rett
mDBConnection.Open "DRIVER={Microsoft ODBC for Oracle};" & Rett

This works fine if the password does not contain symbol @. If it does - I get this error:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not resolve the connect identifier specified

How I can escape this symbol? Maybe I should connect in a different way?

2
oraclevbaodbcspecial-charactersadodb
Have you tried to enclosed the password in double quotes? – Florent B.
@FlorentB. I tried single quotes and it didn't work at all. How can I do with double quotes? This doesnt' compile: Rett = Rett & "pwd="" & mDBPassword & "";" – javagirl
Add another double quote to escape a double quote: pwd=""" & mDBPassword & """; – Florent B.
@FlorentB. thanks, compiles now, inserts double quotes, but didn't help, still same error. – javagirl
Can you login with your password (that has @ sign) directly to your database? – Zac

2 Answers

2
votes

Prior to 11g you couldn't even use @ sign in a password since it's part of the standard Oracle connection string (ie, sql*plus> connect scott/tiger@test to connect to the test instance). You were allowed only _, $ and # (referred to as the special characters below) after the first character up to 10g. Apparently, the driver you are using can't handle the required double quotes needed if you use such a character so I'd recommend either changing the driver or the password.

You must enclose the following passwords in double-quotation marks:

Passwords containing multibyte characters.

Passwords starting with numbers or special characters and containing alphabetical characters. For example:

"123abc"

"#abc"

"123dc$"

Passwords containing any character other than alphabetical characters, numbers, and special characters. For example:

"abc>"

"abc@",

" "

See Guidelines for Securing Passwords for full info.

1
votes

ODBC driver "Microsoft ODBC for Oracle" is deprecated for many year, you should not use it. Documentation says "Oracle 7.3x is supported fully; Oracle8 has limited support". Install ODBC driver from Oracle, this should work better.

Your connect command would be like this one:

mDBConnection.Open "DRIVER={Oracle in OraClient11g_home1};dbq=" & mDBServer & ";" & _
                   "uid=" & mDBUser & ";pwd=""" & mDBPassword & """;"

In case you don't know the exact name of ODBC driver you can search Registry at HKLM\Software\ODBC\ODBCINST.INI\*\Drivers for string SQORA32.dll. The parent key tells the exact name of ODBC driver.

You could also use OLE DB provider like this one:

mDBConnection.Open "Provider=OraOLEDB.Oracle;Data Source=" & mDBServer & ";" & _
                   "User ID=" & mDBUser & ";Password=""" & mDBPassword & """;"

Like ODBC you don't know whether the driver/provider is installed at all on other machines.