Please help me before this laptop becomes a Frisbee!
I almost have this working but I'm getting stuck on this error every time I execute it. I have an ODBC with read-only privileges and I recorded part of the code from the macro. The "from" and "to" date fields I added ( I think I'm correct). I would truly appreciate the help.
Here's the code:
Private Sub CommandButton1_Click()
Dim smpldatea As Date
Dim smpldateb As Date
Dim fdate1 As String
Dim fdate2 As String
smpldatea = UserForm1.TextBox1.Text
smpldateb = UserForm1.TextBox2.Text
fdate1 = Format(smpldatea, "m/d/yyyy hh:mm:ss")
fdate2 = Format(smpldateb, "m/d/yyyy hh:mm:ss")
Sheets("Sheet1").Activate
Range("A1:H10000").ClearContents
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Oracle in OraHome92};SERVER=XXXXXX;UID=DT_I;PWD=RO2ACCESS;DBQ=XXXXXXP;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10" _
), Array( _
";FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DT_CENTERLINE_AUDIT.AUDIT_DATE, DT_CENTERLINE_AUDIT.CENTERLINE_ID, DT_CENTERLINE_AUDIT.COMPLIANT, DT_CENTERLINE_AUDIT.CORRECTED, DT_CENTERLINE_AUDIT.LINE, DT_CENTERLINE_AUDIT.LOCATION" & Chr(13) & "" & Chr(10) & "FROM DTD" _
, _
"BA.DT_CENTERLINE_AUDIT DT_CENTERLINE_AUDIT" & Chr(13) & "" & Chr(10) & "WHERE (DT_CENTERLINE_AUDIT.AUDIT_DATE>= '" & fdate1 & "' And DT_CENTERLINE_AUDIT.AUDIT_DATE<= '" & fdate2 & "') AND (DT_CENTERLINE_AUDIT.L" _
, "OCATION='0902')")
.Name = "Query from YODA2_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
I almost have this working but I'm getting stuck on this error every time I execute it. I have an ODBC with read-only privileges and I recorded part of the code from the macro. The "from" and "to" date fields I added ( I think I'm correct). I would truly appreciate the help.
Here's the code:
Private Sub CommandButton1_Click()
Dim smpldatea As Date
Dim smpldateb As Date
Dim fdate1 As String
Dim fdate2 As String
smpldatea = UserForm1.TextBox1.Text
smpldateb = UserForm1.TextBox2.Text
fdate1 = Format(smpldatea, "m/d/yyyy hh:mm:ss")
fdate2 = Format(smpldateb, "m/d/yyyy hh:mm:ss")
Sheets("Sheet1").Activate
Range("A1:H10000").ClearContents
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Oracle in OraHome92};SERVER=XXXXXX;UID=DT_I;PWD=RO2ACCESS;DBQ=XXXXXXP;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10" _
), Array( _
";FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DT_CENTERLINE_AUDIT.AUDIT_DATE, DT_CENTERLINE_AUDIT.CENTERLINE_ID, DT_CENTERLINE_AUDIT.COMPLIANT, DT_CENTERLINE_AUDIT.CORRECTED, DT_CENTERLINE_AUDIT.LINE, DT_CENTERLINE_AUDIT.LOCATION" & Chr(13) & "" & Chr(10) & "FROM DTD" _
, _
"BA.DT_CENTERLINE_AUDIT DT_CENTERLINE_AUDIT" & Chr(13) & "" & Chr(10) & "WHERE (DT_CENTERLINE_AUDIT.AUDIT_DATE>= '" & fdate1 & "' And DT_CENTERLINE_AUDIT.AUDIT_DATE<= '" & fdate2 & "') AND (DT_CENTERLINE_AUDIT.L" _
, "OCATION='0902')")
.Name = "Query from YODA2_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub