When I run VBA below I get the following:

"Run-Time error '1004': The query did not run, or the database could not be opened. Check the database servcer or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again."

The SQL query runs perfectly in Microsoft SQL Server Management Studio, but bugs out every time when called on from the VBA. I have also included a sample query that illustrates what operation I am trying to run.

Any help would be appreciated.


VBA:

[vba] Application.ScreenUpdating = False

'Clear data sheet
Worksheets("Dashboard_Data").Select
Worksheets("Dashboard_Data").Range("A1").CurrentRegion.Clear

Dim strText As String
Dim fs, f, ts, p
p = "C:\Documents and Settings\ddavis1\Desktop\Aaron Project\TestQuery.sql"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(p)

'Open sql file as text stream and read all
Set ts = f.OpenAsTextStream(1, 0)
strText = ts.Readall '";" +
ts.Close
Set f = Nothing
Set fs = Nothing

'Fill data sheet
With ActiveSheet.QueryTables.Add(Connection:="OLEDB;Provider=SQLOLEDB.1;Integrat ed Security=SSPI;Persist Security Info=False;Initial Catalog=dw;Data Source=CINSHRSQL05;Connection Timeout=0", Destination:=Range("A8"))
.CommandText = strText
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.FieldNames = True
.Refresh
End With[/vba]
SQL:
 DECLARE @Start ASdatetime
DECLARE @End asdatetime
DECLARE @TempTable AStable
(RecordID int, AgentName nvarchar(50))
 
SET @Start =CONVERT(datetime,(CAST(DATEPART(m,GETDATE())ASnvarchar(2))+'/01/'+CAST(DATEPART(yyyy,GETDATE())ASnvarchar(4))), 101)
SET @End =GETDATE()
INSERTINTO @TempTable
SELECTTOP 100 
dw.dbo.cmsAERDaily_vw.RecordID
, dw.dbo.cmsAERDaily_vw.AgentName
FROM 
dw.dbo.cmsAERDaily_vw
WHERE 
dw.dbo.cmsAERDaily_vw.DateID BETWEEN @Start AND @End
SELECT*FROM @TempTable