General ODBC Error

[justinua]

When I run the macro below in VBA I get the error message: "General ODBC Error"

I've tried looking through various message boards, but I haven't been able to find anything that fixes my issue.

Sub LookupBCAddOns()

Dim ConnectionString As String
 MyID = Sheets("Start").Range("ID")
MyFCDate = Sheets("Start").Range("Date")
Dim SQLString As String

    SQLString = Sheets("SQL").Range("BCLookup")
    SQLString = Replace(SQLString, "@FCDate", MyFCDate)
    SQLString = Replace(SQLString, "@ID", MyID)
    
Worksheets("Start").Select

 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=____________;UID=__________;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=____________;DATABASE=__________" _
        , Destination:=Range("$F$17")).QueryTable
        .CommandText = SQLString
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_BCLookUp"
        .Refresh BackgroundQuery:=False
End With


End Sub

The code errors out at the ".Refresh BackgroundQuery:=False" line. From there if I lookup the Connection in Excel, it shows the Command Text as being empty If I change it to ".Refresh BackgroundQuery:=True" it just shows ExternalData_1: Getting Data; however the command text does show up. The SQL query works fine when i test it directly in SQL. The query is as follows:

[TABLE="width: 214"]
<tbody>[TR]
[TD]declare @id as varchar
declare @fc_date as smalldatetime

set @id = '@ID'
set @fc_date = '@FcDate'

select sum(quantity) as BC_AddOns
from dbo.EXBC
where
id = @id
and start_date =  @fc_date
and 2nd_id in ('1178', '1179', '1179')[/TD]
[/TR]
</tbody>[/TABLE]

Can someone please help? Thank you.