paddysheeran
03-16-2012, 10:01 AM
Hi All,
After a couple of hours of searching and not been able to find any solutions can anyone help with the below:
My Query is:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Lancaster;UID=gcs_bo_rep;;DBQ=LANCASTER.WORLD;DBA=W;APA=T;EXC=F;FE N=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllS" _
), Array("uccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;")), _
Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT TYS____FAULT.SEVERITY, TYS____FAULT.OLD_FAULT_ID, TYS____FAULT.SITE, TYS____FAULT.INCIDENT_START_TIME, TYS____FAULT.INCIDENT_END_TIME, TYS____FAULT.TOTAL_OUTAGE_TIME__SECS_, TYS____FAULT.FAULT_" _
, _
"ENQUIRY_DESCRIPTION, TYS____FAULT.ELEMENT, TYS____FAULT.INCIDENT_CLOSED_DATE, TYS____FAULT.STATUS, TYS____FAULT.CUSTOMERS_NAME, TYS____FAULT.CLOSED_BY, TYS____FAULT.CID" & Chr(13) & "" & Chr(10) & "FROM ARADMIN.TYS____FAULT TYS_" _
, _
"___FAULT" & Chr(13) & "" & Chr(10) & "WHERE (TYS____FAULT.INCIDENT_START_TIME>'" & Date_Search & "'" & " AND (TYS____FAULT.CID='CUS48628')" & Chr(13) & "" & Chr(10) & "ORDER BY TYS____FAULT.INCIDENT_START_TIME" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Lancaster"
.Refresh BackgroundQuery:=False
End With
When I run this I get an error message "Run-time error '1004' - General ODBC Error". I know this is down to the variable Date_Search as when I use a fixed number the query works fine.
Any help would be greatly appreciated.
thanks,
Paddy.
After a couple of hours of searching and not been able to find any solutions can anyone help with the below:
My Query is:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Lancaster;UID=gcs_bo_rep;;DBQ=LANCASTER.WORLD;DBA=W;APA=T;EXC=F;FE N=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllS" _
), Array("uccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;")), _
Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT TYS____FAULT.SEVERITY, TYS____FAULT.OLD_FAULT_ID, TYS____FAULT.SITE, TYS____FAULT.INCIDENT_START_TIME, TYS____FAULT.INCIDENT_END_TIME, TYS____FAULT.TOTAL_OUTAGE_TIME__SECS_, TYS____FAULT.FAULT_" _
, _
"ENQUIRY_DESCRIPTION, TYS____FAULT.ELEMENT, TYS____FAULT.INCIDENT_CLOSED_DATE, TYS____FAULT.STATUS, TYS____FAULT.CUSTOMERS_NAME, TYS____FAULT.CLOSED_BY, TYS____FAULT.CID" & Chr(13) & "" & Chr(10) & "FROM ARADMIN.TYS____FAULT TYS_" _
, _
"___FAULT" & Chr(13) & "" & Chr(10) & "WHERE (TYS____FAULT.INCIDENT_START_TIME>'" & Date_Search & "'" & " AND (TYS____FAULT.CID='CUS48628')" & Chr(13) & "" & Chr(10) & "ORDER BY TYS____FAULT.INCIDENT_START_TIME" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_Lancaster"
.Refresh BackgroundQuery:=False
End With
When I run this I get an error message "Run-time error '1004' - General ODBC Error". I know this is down to the variable Date_Search as when I use a fixed number the query works fine.
Any help would be greatly appreciated.
thanks,
Paddy.