VBA String Concatenation not working as expected

Question

Im trying to use concatenation to make a connection string. Heres the code:

objMyConn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=MyCat;Data Source=" & serverName & ";User ID=MyId;Password=MyPwd;"

serverName is a string defined like this: serverName = "ServerName\InstaceName"

I am expecting for objMyConn.Connection string to be = to the whole connection staring however it is = to this:

Provider=SQLOLEDB;Initial Catalog=MyCat;Data Source=ServerName\InstanceName

So I'm missing the last part of my connection string.

The \ char needing to be escaped was my first thought also. however this did not solve the problem. — user3839756
– user3839756, Commented Nov 25, 2015 at 21:52
What happens if you set serverName = "fred"? Do you still lose the last part of the conn string? — DeanOC
– DeanOC, Commented Nov 25, 2015 at 21:55
Possible duplicate of ADODB Connection.ConnectionString property doesn't return server name — DeanOC
– DeanOC, Commented Nov 25, 2015 at 22:04
According to MSDN: ADO Connection String, you should use single quote mark ' in your connection string, so objMyConn.ConnectionString = "Provider='SQLOLEDB';Initial Catalog='MyCat';Data Source='" & serverName & "';User ID='MyId';Password='MyPwd';" — Mincong Huang
– Mincong Huang, Commented Nov 25, 2015 at 22:04

user3839756 · Accepted Answer · 2016-01-13 17:36:11Z

0

Here is the solution to this, Just in case anyone else has the same problem.

When declaring my variable server name I had given it a length of 255, I changed the length to the appropriate length needed.

answered Jan 13, 2016 at 17:36

user3839756

8433 gold badges12 silver badges24 bronze badges

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

1 Answer 1