CODEWITHSUNDEEP
sql-servervbaexcelconnection-string
StarDotStar
StarDotStar

Reputation: 3015

Error in VBA Connection String

I am facing issues with connecting to a SQL Server database from Excel VBA. I primarily suspect that the error is occurring because of the incorrect handling of the password containing double quotes.

Scenario :

The connection string I am using is:

Public Const ConnectionStringONLINE = "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=tejg3kl!"";"

An extra double quote is included in the Password value as an escape sequence character.

Code to connect to the database -

Dim DbConn As ADODB.Connection
Set DbConn = New ADODB.Connection  
DbConn.Open ConnectionStringONLINE

When the above code is executed, I receive an error -

Run-time error'-2147217843 (80040e4d)':

Login failed for user 'Test_User'.

I did an UDL test for the same credentials specified in the connection string and the connection worked fine.

Any suggestions on why the error is occurring in VBA? Do I need to modify the connection string?

Edit - Just changed the password string to include an exclamation mark before the double quotes to make it appear exactly like the real password that I am using.

Upvotes: 0

Views: 2848

Answers (2)

StarDotStar
StarDotStar

Reputation: 3015

Well, this is really a weird one! After Geoff suggested in the comments that it worked for him with the double quotes, I took a closer look at my case.

It turns out that the password to the database was tejg3kl!” And the one that I was using in the code was tejg3kl!"

Did you notice the difference in the double quotes?

Apparently there are different types of double quotes as explained in this question - Are there different types of double quotes in utf-8 (PHP, str_replace)? and also here - http://unicode.org/cldr/utility/confusables.jsp?a=%22&r=None

So, finally all I did was, to use ” instead of " in the connection string and the code worked perfectly fine. (Also, there was no need for including any escape sequence)

Can someone please explain the need for different types of double quotes?

Upvotes: 1

Spock
Spock

Reputation: 4910

escaping a double quote doesn't quite work that way in vb. You have to use the chr function to insert the quote into a string.
Try this. ..

ConnectionStringONLINE = "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=tejg3kl" & Chr(34) & ";"

Upvotes: 0

Related Questions