PDA

View Full Version : Solved: Loop to create repeating SQL statment with incrementing variables



rubytuesday1
03-29-2012, 02:18 PM
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

rubytuesday1
03-29-2012, 07:41 PM
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