Kathleen0422
Board Regular
- Joined
- Apr 12, 2006
- Messages
- 188
TheDivision = PE
TheLoadDate = 7/6/2007
This works wonderfully, but I want to add a parameter being that TheLoadDate is greater than Date_Loaded
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=#####UID=OT_User;pwd=######;APP=Microsoft Office 2003;WSID=#####00" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT view_" & TheDivision & "_Hours.TMID, view_" & TheDivision & "_Hours.CostCenter, view_" & TheDivision & "_Hours.TRC_Code, view_" & TheDivision & "_Hours.Name, view_" & TheDivision & "_Hours.Approval_Date, view_" & TheDivision & "_Hours.Date_Worked, view_" & TheDivision & "_Hours.Hours, view_" & TheDivision & "_Hours.Project_ID, " _
, _
"view_" & TheDivision & "_Hours.Title, view_" & TheDivision & "_Hours.Work_Location, view_" & TheDivision & "_Hours.Date_Loaded" & Chr(13) & "" & Chr(10) & "FROM OTRPT.dbo.view_" & TheDivision & "_Hours view_" & TheDivision & "_Hours" & Chr(13) & "" & Chr(10) & "WHERE (view_" & TheDivision & "_Hours.Date_Loaded>{ts '2007-07-06 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY view_PE_" _
, "Hours.Name")
.Name = "Query from Hours"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
so when I modify the Statement to this:
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=#####;UID=OT_User;pwd=#####;APP=Microsoft Office 2003;WSID=##### _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT view_" & TheDivision & "_Hours.TMID, view_" & TheDivision & "_Hours.CostCenter, view_" & TheDivision & "_Hours.TRC_Code, view_" & TheDivision & "_Hours.Name, view_" & TheDivision & "_Hours.Approval_Date, view_" & TheDivision & "_Hours.Date_Worked, view_" & TheDivision & "_Hours.Hours, view_" & TheDivision & "_Hours.Project_ID, " _
, _
"view_" & TheDivision & "_Hours.Title, view_" & TheDivision & "_Hours.Work_Location, view_" & TheDivision & "_Hours.Date_Loaded" & Chr(13) & "" & Chr(10) & "FROM OTRPT.dbo.view_" & TheDivision & "_Hours view_" & TheDivision & "_Hours" & Chr(13) & "" & Chr(10) & "WHERE (view_" & TheDivision & "_Hours.Date_Loaded>{ts '" & Format(TheLoadDate, "mm/dd/yyyy") & " 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY view_PE_" _
, "Hours.Name")
.Name = "Query from Hours"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
IT BOMBS error 1004 General ODBC , I am so frustrated PLEASE HELP
TheLoadDate = 7/6/2007
This works wonderfully, but I want to add a parameter being that TheLoadDate is greater than Date_Loaded
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=#####UID=OT_User;pwd=######;APP=Microsoft Office 2003;WSID=#####00" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT view_" & TheDivision & "_Hours.TMID, view_" & TheDivision & "_Hours.CostCenter, view_" & TheDivision & "_Hours.TRC_Code, view_" & TheDivision & "_Hours.Name, view_" & TheDivision & "_Hours.Approval_Date, view_" & TheDivision & "_Hours.Date_Worked, view_" & TheDivision & "_Hours.Hours, view_" & TheDivision & "_Hours.Project_ID, " _
, _
"view_" & TheDivision & "_Hours.Title, view_" & TheDivision & "_Hours.Work_Location, view_" & TheDivision & "_Hours.Date_Loaded" & Chr(13) & "" & Chr(10) & "FROM OTRPT.dbo.view_" & TheDivision & "_Hours view_" & TheDivision & "_Hours" & Chr(13) & "" & Chr(10) & "WHERE (view_" & TheDivision & "_Hours.Date_Loaded>{ts '2007-07-06 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY view_PE_" _
, "Hours.Name")
.Name = "Query from Hours"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
so when I modify the Statement to this:
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=#####;UID=OT_User;pwd=#####;APP=Microsoft Office 2003;WSID=##### _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT view_" & TheDivision & "_Hours.TMID, view_" & TheDivision & "_Hours.CostCenter, view_" & TheDivision & "_Hours.TRC_Code, view_" & TheDivision & "_Hours.Name, view_" & TheDivision & "_Hours.Approval_Date, view_" & TheDivision & "_Hours.Date_Worked, view_" & TheDivision & "_Hours.Hours, view_" & TheDivision & "_Hours.Project_ID, " _
, _
"view_" & TheDivision & "_Hours.Title, view_" & TheDivision & "_Hours.Work_Location, view_" & TheDivision & "_Hours.Date_Loaded" & Chr(13) & "" & Chr(10) & "FROM OTRPT.dbo.view_" & TheDivision & "_Hours view_" & TheDivision & "_Hours" & Chr(13) & "" & Chr(10) & "WHERE (view_" & TheDivision & "_Hours.Date_Loaded>{ts '" & Format(TheLoadDate, "mm/dd/yyyy") & " 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY view_PE_" _
, "Hours.Name")
.Name = "Query from Hours"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
IT BOMBS error 1004 General ODBC , I am so frustrated PLEASE HELP