sql loop test

[Samantha]

I want to loop through a table to get the Bill Of Materials of an assembly.
Because the number of levels for assembly may be different, I set the
For-loop to 10, but wanting a more efficient way to shorten the loop.
Here's my code:

Dim str1 As String, i As Integer
For i = 1 To 10
level = i
str1 = "INSERT INTO table1 ( Assembly, Component, ItemSequence,
QuantityPer, BOMLevel )"
str1 = str1 & " SELECT dbo_BOM.Assembly, dbo_BOM.Component,
BOM.ItemSequence, BOM.QuantityPer, " & level
str1 = str1 & " FROM table1 LEFT JOIN BOM ON table1.Component =
BOM.Assembly"
str1 = str1 & " WHERE Not (BOM.Assembly) Is Null "
str1 = str1 & " AND (table1.BOMLevel = " & level - 1 & ")"
db.Execute str1, dbFailOnError
Next i

The problem is the the variable "i". I'm thinking that if there is a way to
check if there is any records on the db.Execute, then I can use the DoWhile
loop and stop the loop if there's no records for str1.
Thanks in advance for all your help.

 

[Dirk Goldgar]

I want to loop through a table to get the Bill Of Materials of an
assembly. Because the number of levels for assembly may be different,
I set the For-loop to 10, but wanting a more efficient way to shorten
the loop. Here's my code:

Dim str1 As String, i As Integer
For i = 1 To 10
level = i
str1 = "INSERT INTO table1 ( Assembly, Component, ItemSequence,
QuantityPer, BOMLevel )"
str1 = str1 & " SELECT dbo_BOM.Assembly, dbo_BOM.Component,
BOM.ItemSequence, BOM.QuantityPer, " & level
str1 = str1 & " FROM table1 LEFT JOIN BOM ON table1.Component =
BOM.Assembly"
str1 = str1 & " WHERE Not (BOM.Assembly) Is Null "
str1 = str1 & " AND (table1.BOMLevel = " & level - 1 & ")"
db.Execute str1, dbFailOnError
Next i

The problem is the the variable "i". I'm thinking that if there is a
way to check if there is any records on the db.Execute, then I can
use the DoWhile loop and stop the loop if there's no records for str1.
Thanks in advance for all your help.

You can check after the db.Execute to see if any records were inserted:

db.Execute str1, dbFailOnError
If db.RecordsAffected = 0 Then
' exit the loop
End If