Error in VBA Connection String

Question

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 :

SQL Server Name - FOO\BAR
Initial Catalog - ScrollBar
User - Test_User
Password - tejg3kl!"

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.

Does the password actually contain ""? Your connection string as it stands would cause a syntax error ... — Alex K.
– Alex K., Commented Jan 9, 2015 at 17:45
Sorry, I misread. Try it quoted: "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=""tejg3kl"""";" — Alex K.
– Alex K., Commented Jan 9, 2015 at 17:50
@StarDotStar - it works fine for me with the exclamation point too. I'm using the Microsoft ActiveX Data Objects 2.0 Library connecting to SQL Server 2008R2, from Excel 2013. — Geoff
– Geoff, Commented Jan 9, 2015 at 18:58

Community · Accepted Answer · 2017-05-23 10:26:10Z

1

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?

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jan 9, 2015 at 19:51

StarDotStar

3,0553 gold badges23 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex K. Over a year ago

They are called curly/smart/pretty quotes, unlike the regular " there are different glyphs for opening and closing (so basically look nicer), see en.wikipedia.org/wiki/Quotation_mark#Curved_quotes_and_Unicode

Spock · Accepted Answer · 2015-01-09 18:15:44Z

0

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) & ";"

answered Jan 9, 2015 at 18:15

Spock

4,9802 gold badges19 silver badges22 bronze badges

3 Comments

Geoff Over a year ago

In fact, it does work that way; you can double-up a double quote inside a string to escape it. Example: Const myString = "Try ""this"" on for size"

Geoff Over a year ago

@StarDotStar - Right - your code is fine. Check your user credentials.

StarDotStar Over a year ago

But even this gave the same error - "Login failed for user 'Test_User'." This is despite the fact that the connection string looked correct at run-time : "Provider=SQLOLEDB.1;Data Source=FOO\BAR;Initial Catalog=ScrollBar;User Id=Test_User;Password=tejg3kl";"

Collectives™ on Stack Overflow

Error in VBA Connection String

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related