Creating VB Code To Increment The Number Of Fields In An SQL Create Table Statement

I have an SQL statement which i need to code in vb:

CODE:

Notice the fields prodno1 and prodno2 in the table structure.. this is part of a number of sql statements i need to run and put inside a loop. my problem is i want to automatically use this same code such that on the next loop, this sql statement is going to be:

CODE:

Note that in this "2nd loop"

- the table name is now r3 from r2 in the first SQL statement
- there are now prodno1, prodno2 and prodno3 instead of just prodno1 and prodno2
- in the WHERE clause, the p.prodNo becomes p.prodNo2

And so on and so forth.. so for the 3rd loop

- there's going to be r4
- there's going to be prodno4
- in the WHERE clause i will use p.prodNo3