Excel VBA - connection string to SQL Server 2008

0
votes

I am trying to connect to SQL Server 2008 from Excel VBA using the connection string (ADODB.Connection).

"Driver={SQL Native Client};Data Source=" & Range("Svr").Value & ";Database=" & Range("DB").Value & ";Trusted_Connection=yes"

When I try to open the connection I get this error

Error (-2147467259) Description - [Microsoft][ODBC Driver Manager] Data source name too long

Can some one please provide me a sample

1
excelvba
Are Svr and DB named ranges in your workbook? – Widor
@Widor...Yes they are in my workbook – S..
Can you show us a bit more of the code? Maybe your connection string isn't the problem. It looks like you haven't instantiated the object you are using to make your connection. – JMK
@JMK .....Yes you are right.....I Misspelled the obj name, but after that when i try to open the connection i get the below error "Error (-2147467259) Description - [Microsoft][ODBC Driver Manager] Data source name too long " – S..
Are there any special characters in your data source name? Or is it really long? – JMK

1 Answers

1
votes

I think the backslash in your connection string is causing your issues, try and escape it like so:

xxxxxxxxxxxx.xxx.xx.xxxxxxxx.xxx\\xxxx01xxx

Also, putting quotes around each value wouldn't hurt:

"Driver={SQL Native Client};Data Source='" & Range("Svr").Value & "';Database='" & Range("DB").Value & "';Trusted_Connection=yes"

Based on this answer.