Excel Hints

Results 1 to 2 of 2

Thread: Solved: Loop to create repeating SQL statment with incrementing variables

  1. #1

    Solved: Loop to create repeating SQL statment with incrementing variables

    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..




    [vba]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]

  2. #2
    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.

    [VBA]
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •