It appears that this error is so generic that many of the existing solutions address different issues.
In my case I have created a macro that works in Excel 2010 and does not work in Excel 2007.
Here is a summary of the code:
cn = "ODBC;Driver={SQL Server Native Client 10.0};Server=serverName;Database=dbName;Trusted_Connection=yes;"
sql = "select top 10 * from tableName"
Dim S As Worksheet
Set S = ActiveWorkbook.Sheets("Medical")
With S.QueryTables.Add(Connection:=cn, Destination:=S.Range("B1"))
.CommandText = sql
.Refresh BackgroundQuery:=False
End With
This code executes perfectly in Excel 2010, but errors out in Excel 2007.
Do I need to change the connection string structure for 2007, or is there another issue at work?
Best Answer
Solution:
All machines I was using with Excel 2010 have "SQL Server Native Client 10.0" as a possible driver for ODBC Data Sources. The machines with Excel 2007 only have "SQL Server".
I changed my connection string to be:
and it worked like a charm.
Thanks to those that responded.