Excel New Database Query

[liam_conor]

I am creating a userform that will take information from the user necessary to complete a SQL statement. This information will return the data related to what the user has entered into the textboxes. For example:

Private Sub CommandButton1_Click()
Dim X As String

X = TextBox1.Value
Y = TextBox2.Value
begin_date = TextBox3.Value
end_date = TextBox4.Value
'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=*****;UID=*****;PWD=*****;APP=Microsoft® Query;WSID=****;DATABASE=*****;Network=*****" _
, Destination:=Range("B5"))
.CommandText = Array( _
"SELECT RT.RTNAME, BLE.BLENAME, AME.TIME_, AME.VALUE_" & Chr(13) & "" & Chr(10) & "FROM DESPC.DESPC.RT RT, DESPC.DESPC.BLE BLE, DESPC.DESPC.AME AME" & Chr(13) & "" & Chr(10) & "WHERE_ (RT.RTNAME='X') AND (RT.RTID=BLE.RTID) AND (BLE.BLENAME='Y') AND (BLE.BLEID=AME.BLEID) AND (AME.TIME_>={ts 'begin_date 07:00:00'} And AME.TIME_<={ts 'end_date 06:59:59'})")
.Name = "Query from baltints10"
.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

This of course does not work. Any suggestions on how to do something like this?

 

[Marc]

Liam, If you can modify your .CommandText parameter to a string you can force your variable in there. The .CommandText is an array, and I have no idea how to stuff variables into this array. So if you can break the whole thing down into a string you just might get what you’re looking for. I modified the WHERE statement for both X and Y, but I’m not sure what you’re doing with the dates. Maybe you can alter these portions of the WHERE using the concept I applied to X and Y. Regards, Marc

'.CommandText = Array( _
"SELECT RT.RTNAME, BLE.BLENAME, AME.TIME_, AME.VALUE_" & Chr(13) & "" & Chr(10) & "FROM DESPC.DESPC.RT RT, DESPC.DESPC.BLE BLE, DESPC.DESPC.AME AME" & Chr(13) & "" & Chr(10) & "WHERE_ (RT.RTNAME='X') AND (RT.RTID=BLE.RTID) AND (BLE.BLENAME='Y') AND (BLE.BLEID=AME.BLEID) AND (AME.TIME_>={ts 'begin_date 07:00:00'} And AME.TIME_<={ts 'end_date 06:59:59'})")
.CommandText = _
"SELECT RT.RTNAME, BLE.BLENAME, AME.TIME_, AME.VALUE_" & Chr(13) & "" & Chr(10) & "FROM DESPC.DESPC.RT RT, DESPC.DESPC.BLE BLE, DESPC.DESPC.AME AME" & Chr(13) & "" & Chr(10) & "WHERE_ (RT.RTNAME='" & X & "') AND (RT.RTID=BLE.RTID) AND (BLE.BLENAME='" & Y & "') AND (BLE.BLEID=AME.BLEID) AND (AME.TIME_>={ts 'begin_date 07:00:00'} And AME.TIME_<={ts 'end_date 06:59:59'})"