Solved: Loop to create repeating SQL statment with incrementing variables

[rubytuesday1]

I have a list of ID numbers in an excel sheet and those numbers will be the fields in the SQL SELECT statment like this:

SELECT [IDnumber1] from mytable where Field=[Variable] and Field1=[Variable1]

I'm trying to get VBA to run the SELECT statement, write the results to Excel then Run it again and move to the next variable in the list so the second time thru would be:

SELECT [IDnumber2] from mytable where Field=[Variable] and Field2=[Variable2]

Keep going until it goes all the way through the list of numbers. Some of the variables in the Select dont change and others do. The length of the list varies so I planning to have a COUNTA return the total items in the list then use that result to form a For i = 1 to [COUNTA result]

All this is due to a poor database design and the use of many VBA reserved words in the database fields. (Not my design!)

Here is what I have so far..I put several breaks in the SQL statment so the variables would be on a new line by themselves..

Sub RolloverSimple() 
     
     'Declare variables'
    Dim objMyConn As ADODB.Connection 
    Dim objMyCmd As ADODB.Command 
    Dim objMyRecordset As ADODB.Recordset 
    Dim i As Integer 
    Dim c As Range 
     
    Set objMyConn = New ADODB.Connection 
    Set objMyCmd = New ADODB.Command 
    Set objMyRecordset = New ADODB.Recordset 
     
     'Open Connection'
    objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\RealogicTools\TestDB_v14.mdb" 
    objMyConn.Open 
     
     'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn 
     
     'Clear Contents
    Sheets("Sheet1").Select 
    ActiveSheet.Range("A4:ab9000").ClearContents 
     
     
     '--BEGIN LOOP-------- each range to increment until the end of the list
     ' For Each c In Range("a1:c2")
     
    objMyCmd.CommandText = "" 
    objMyCmd.CommandText = "SELECT  OP1.Name,  OP1.P" & _ 
    "" & Range("d1") & " " & _ 
    "FROM OAOutput AS OP1 " & vbCrLf 
    objMyCmd.CommandText = objMyCmd.CommandText & "WHERE (((OP1.[Section])=""Base Rent"" Or (OP1.[Section])=""Miscellaneous"") AND " & _ 
    "((OP1.LineID)=" & _ 
    "" & Range("c1") & "" & _ 
    ") AND ((OP1.PropID)=" & _ 
    """" & Range("StaticVar1") & """" & _ 
    ") AND ((OP1.VersionID)=" & _ 
    "" & Range("StaticVar2") & "" & _ 
    "));" 
     
     ' Debug.Print objMyCmd.CommandText
    objMyCmd.CommandType = adCmdText 
     
     'Open Recordset'
    Set objMyRecordset.Source = objMyCmd 
    objMyRecordset.Open 
     
     'Copy Data to Excel increment by 10 each time'
    ActiveSheet.Range("a5").CopyFromRecordset objMyRecordset 
     
    For i = 1 To objMyRecordset.Fields.Count 
        ActiveSheet.Cells(4, i).Value = objMyRecordset.Fields(i - 1).Name 
    Next i 
     'Next c
     '--------------END LOOP--------------
     
End Sub 

VBA tags courtesy of www.thecodenet.com

[rubytuesday1]

Couldnt figure out how to edit my post...

Still working on this...I think it needs to be done witha nested loop, the outer loop builds the sql statment while the inner loop updates the variables in the sql statment. I updated the code by breaking the SQL statment into several pieces so that the strings containing the variables can be separated and updated.

Sub RolloverSimple() 
     
     'Declare variables'
    Dim objMyConn As ADODB.Connection 
    Dim objMyCmd As ADODB.Command 
    Dim objMyRecordset As ADODB.Recordset 
    Dim i As Integer 
    Dim c As Range 
     
    Dim SQL1 As String 
    Dim SQL2 As String 
    Dim SQL3 As String 
    Dim SQL4 As String 
    Dim SQL5 As String 
    Dim SQL6 As String 
    Dim SQL7 As String 
    Dim SQL8 As String 
    Dim SQL9 As String 
    Dim SQL10 As String 
    Dim SQL11 As String 
     
    Set objMyConn = New ADODB.Connection 
    Set objMyCmd = New ADODB.Command 
    Set objMyRecordset = New ADODB.Recordset 
     
     'Open Connection'
    objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\RealogicTools\TestDB_v14.mdb" 
    objMyConn.Open 
     
     'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn 
     
     'Clear Contents
    Sheets("Sheet1").Select 
    ActiveSheet.Range("A4:ab9000").ClearContents 
     
     
     '--BEGIN OUTER LOOP-------- This section repeats until the end of the list of IDnumbers
     ' For Each c In Range("a1:c2")
     
    SQL1 = "SELECT OP1.LineID, OP1.Name, OP1.[Section], OP1.P" 
    SQL2 = "" & Range("d1") & " " 
    SQL3 = "FROM OAOutput AS OP1 " & vbCrLf 
    SQL4 = "WHERE (((OP1.[Section])=""Base Rent"" Or (OP1.[Section])=""Miscellaneous"") AND " 
    SQL5 = "((OP1.LineID)=" 
    SQL6 = "" & Range("c1") & "" 
    SQL7 = ") AND ((OP1.PropID)=" 
    SQL8 = """" & Range("StaticVar1") & """" 
    SQL9 = ") AND ((OP1.VersionID)=" 
    SQL10 = "" & Range("StaticVar2") & "" 
    SQL11 = "));" 
     
     
    objMyCmd.CommandText = "" 
    objMyCmd.CommandText = SQL1 & SQL2 & SQL3 & SQL4 & SQL5 & SQL6 & SQL7 & SQL8 & SQL9 & SQL10 & SQL11 
     
     ' SQL2 and   SQL 6 need to be updated on the inner loop
     
    Debug.Print objMyCmd.CommandText 
    objMyCmd.CommandType = adCmdText 
     
     'Open Recordset'
    Set objMyRecordset.Source = objMyCmd 
    objMyRecordset.Open 
     
     'Copy Data to Excel increment by 10 each time'
    ActiveSheet.Range("a5").CopyFromRecordset objMyRecordset 
     
     
     'Next c
     '--------------END OUTER LOOP--------------
     
End Sub 
 
 
 
 

VBA tags courtesy of www.thecodenet.com