SQL Frustration

[Kathleen0422]

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

 

[blazonQC]

I'm not too farmiliar with ODBC, but I do see something that might be of interest.

From the code you said works:

SERVER=#####UID=OT_User;

From the modified code:

SERVER=#####;UID=OT_User;

I also noticed that it seem's that you are formatting your date differently than what was in the first string:

Date format first string: '2007-07-06 00:00:00'
Date format second string: '07/06/2007 00:00:00'

Hope I helped a little.
Chad

 

[Kathleen0422]

Thank you it was the date, I can't tell you how many times I rewrote that always mm-dd-yyyy and it needed to be yyyy-mm-dd

Thanks

 

[blazonQC]

no problem. i'm glad I could help you out.