Error Connecting SQL Server 2005 using Excel VBA

Vishesh's picture

 This is the code that I am using 

 

conn.Open "Provider=MSDASQL.1;Server=VISHESH-NC\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;", "abc", "abc" 

 

This gives the error: 

Err No. -2147217887 

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. 

 

on changing the command to 

conn.Open "Provider=MSDASQL.1;Server=VISHESH-NC\SQLEXPRESS;Initial Catalog=Northwind;", "abc", "abc" 

 

this throws the following error: 

Err No. -2147467259 

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 

 

However if I define ODBC DSN and use the following it works fine 

conn.Open "Provider=MSDASQL.1;Data Source=abc;Initial Catalog=Northwind;" 

 

Where am I going wrong with my first 2 approaches.