Array help

[d4m]

I have the following which works okay...

Dim arrayTable As Variant
Dim varTable As Variant

arrayTable = Array("SG", "Oakland", "Los Angeles", "Server")

For Each varTable In arrayTable

SQL1 = "INSERT INTO tblServer ( Server, Description, [Date Audited]
) " & _
"SELECT TOP 1 Server, Description, Date() AS [Date Audited]
FROM " & varTable &";"

Next varTable

but now I need to add another array so I can change the "SELECT TOP 1"
to select different numbers. For instance ("1","2","1","5") so that SG
selects 1, Oakland selects 2, etc.

I'm still new to VBA and arrays, so is there an easy way to do this?

Thanks for any help.

[rkc]

d4m wrote:

I have the following which works okay...

Dim arrayTable As Variant
Dim varTable As Variant

arrayTable = Array("SG", "Oakland", "Los Angeles", "Server")

For Each varTable In arrayTable

SQL1 = "INSERT INTO tblServer ( Server, Description, [Date Audited]
) " & _
"SELECT TOP 1 Server, Description, Date() AS [Date Audited]
FROM " & varTable &";"

Next varTable

but now I need to add another array so I can change the "SELECT TOP 1"
to select different numbers. For instance ("1","2","1","5") so that SG
selects 1, Oakland selects 2, etc.

I'm still new to VBA and arrays, so is there an easy way to do this?

Thanks for any help.

Set up another array and use a for/next loop and use the counter
variable to address the index of both arrays.

<air code>

dim i as long
arrCity = Array("SG", "Oakland", "Los Angeles", "Server")
arrNum = Array("1","2","1","5")

for i = 0 to ubound(arrCity)

SQL1 = "INSERT INTO tblServer " & _
"(Server, Description, [Date Audited] " & _
"SELECT TOP " & arrNum(i) & " Server, Description, " & _
"Date() AS [Date Audited] " & _
"FROM " & arrCity(i) & ";"
next
</air code>

[Randy Harris]

"d4m" <d4****@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

I have the following which works okay...

Dim arrayTable As Variant
Dim varTable As Variant

arrayTable = Array("SG", "Oakland", "Los Angeles", "Server")

For Each varTable In arrayTable

SQL1 = "INSERT INTO tblServer ( Server, Description, [Date Audited]
) " & _
"SELECT TOP 1 Server, Description, Date() AS [Date Audited]
FROM " & varTable &";"

Next varTable

but now I need to add another array so I can change the "SELECT TOP 1"
to select different numbers. For instance ("1","2","1","5") so that SG
selects 1, Oakland selects 2, etc.

I'm still new to VBA and arrays, so is there an easy way to do this?

Thanks for any help.

rkc has provided a nice solution for you, but I am concerned about your use
of the TOP predicate. You don't have an ORDER BY clause. You won't be able
to reliably predict which 1, 2, or 5 records get selected without using
ORDER BY.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.