External Data Error

[Craig B]

So I coded up this nice macro to extract call by call data from a
Database.

This worked fine for months on end and now won't run more than once,
sometimes not at all. I get an error on oQT.refresh
backgroundrefresh:=false which throws "run Time Error
'-2147417848(80010108)': Method "refresh" of object "_QueryTable
Failed"


Any ideas on what went wrong? I replaced the text of the password
with ***** for my own piece of mind


dDate = Format(Month(Date - 1) & "/" & iStartDay & "/" & Year(Date -
1), "mm/dd/yyyy")


sDate = Format(dDate, "yyyymmdd")


Workbooks.Add


For r = iStartDay To Day(Date - 1)


sSName = Format(dDate, "mm-dd-yyyy")
sWBName = "Call by Call " & sSName & ".XLS"


If FileExists(sPath & sWBName) = False Then


sSql = ""
sConn =

"ODBC;DSN=**********;UID=********;PWD=*******;SRVR=******;DB=*****"
sSql = "SELECT " & sCW & sDate & ".Timestamp, "
sSql = sSql & sCW & sDate & ".CallEvent, "
sSql = sSql & sCW & sDate & ".CallEventName, "
sSql = sSql & sCW & sDate & ".CallID, "
sSql = sSql & sCW & sDate & ".TelsetLoginID, "
sSql = sSql & sCW & sDate & ".AssociatedData, "
sSql = sSql & sCW & sDate & ".Destination, "
sSql = sSql & sCW & sDate & ".EventData, "
sSql = sSql & sCW & sDate & ".Source, "
sSql = sSql & sCW & sDate & ".Time " & vbCrLf
sSql = sSql & "FROM blue.dbo.eCallByCallStat" & sDate & "
" & sCW & sDate & vbCrLf
sSql = sSql & " ORDER BY " & sCW & sDate & ".Timestamp"

Set oQT = ActiveSheet.QueryTables.Add(Connection:=sConn,
Destination:=Range("A1"), Sql:=sSql)


oQT.Refresh BackgroundQuery:=False <<<----Error occurs
here


Do While oQT.Refreshing = True
Loop


Columns.AutoFit


For Each WSh In ActiveWorkbook.Worksheets
For Each oQT In WSh.QueryTables
oQT.Delete
Next oQT
Next WSh


ActiveSheet.Name = sSName


ActiveWorkbook.SaveAs Filename:=sPath & sWBName


ActiveWorkbook.Close


Workbooks.Add


End If


sDate = sDate + 1
dDate = DateAdd("d", 1, dDate)


Next r