Passing a variable's value into an SQL Statement

[Ripsnorta]

I've got another very Noobish question which I know has a very simple solution which I can't seem to find.

I want to build an SQL statement in code which includes the value stored in a string variable where the variable name includes a loop counter.

This is a much simplified example of what I am trying to do:

Dim i as integer
dim Strtable1 as string
dim Strtable2 as string
dim Sqlstring as string
...

Strtable1 = "tblEmployees"
Strtable2 = "tblSales"
...
Sqlstring = " select * from ... where....."


for i = 1 to 10

DoCmd.RunSQL "INSERT INTO " & [COLOR="Red"]StrTable & i [/COLOR] & sqlstring

Next i

I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.

Can anyone tell me how to do this?

 

[Bat17]

You need an array to hold the tables

Dim i As Integer
Dim Strtable(9) As String
'array are 0 based so this will give 10 elements

Strtable(0) = "tblEmployees"
Strtable(1) = "tblSales"
...
Sqlstring = " select * from ... where....."

For i = 0 To 9

DoCmd.RunSQL "INSERT INTO " & Strtable(i) & Sqlstring

Next i

HTH

Peter

 

[john471]

I've got another very Noobish question

Seeing as you're asking noobish questions - why are you writing code to put the same data into numerous tables ?

That would seem to indicate a fundamental design flaw in your database.

 

[Ripsnorta]

You need an array to hold the tables

Dim i As Integer
Dim Strtable(9) As String
'array are 0 based so this will give 10 elements

Strtable(0) = "tblEmployees"
Strtable(1) = "tblSales"
...
Sqlstring = " select * from ... where....."

For i = 0 To 9

DoCmd.RunSQL "INSERT INTO " & Strtable(i) & Sqlstring

Next i

HTH

Peter

Thanks Peter. That should do the trick.

 

[Ripsnorta]

Seeing as you're asking noobish questions - why are you writing code to put the same data into numerous tables ?

That would seem to indicate a fundamental design flaw in your database.

I am not actually copying the same data into different tables and the database is normalised. I am writing a function to copy a previous year's categories into a new year. For example, the Primary Key for one table is Year and Sales Category. Another table uses Year and Expense Category. The user has the option to set up a new year of operation, and they can choose to copy over the previous year's categories into the new year if they wish to. It is simpler to loop through the selected tables to do this as the SQL statement to do the copying is the same for each table (I pick up the field names from the fields collection). Hope this makes sense.