PDA

View Full Version : QueryTables.add with SQL query



ddavis1
03-12-2008, 02:18 PM
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:

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;Integrated 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
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