Excel 2007 - 1004 Run-time error Refresh Query Table

Question

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?

Solution

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:

cn = "ODBC;Driver=SQL Server;Server=serverName;Database=dbName;Trusted_Connection=yes;"

and it worked like a charm.

Thanks to those that responded.