By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Run several append queries

P: n/a
I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)

Nov 15 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a

Enter the table names in a table Named tblOfTables
with one field, TableName.

Dim db as database
Dim rs as Recordset

Set db = currentdb
Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
rs.movefirst
Do While Not rs.EOF
strTblName = rs!TableName
'Use your code here to substitute the variable, strTblName for table
' references in your SQL statements
'Execute your queries
rs.movenext
Loop

set rs = nothing
set db = nothing

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
>I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)

Nov 15 '06 #2

P: n/a
I learned something new, thank you!

How would I go about it if I would not want pick the names form a table
nut just from a list in that piece of code?
Kc-Mass schreef:
Enter the table names in a table Named tblOfTables
with one field, TableName.

Dim db as database
Dim rs as Recordset

Set db = currentdb
Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
rs.movefirst
Do While Not rs.EOF
strTblName = rs!TableName
'Use your code here to substitute the variable, strTblName for table
' references in your SQL statements
'Execute your queries
rs.movenext
Loop

set rs = nothing
set db = nothing

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)
Nov 15 '06 #3

P: n/a
Copy your original code nine times and edit the last
nine copies to reflect the other 9 file names.
By the way your declaration of "Dim i, j, t As Integer"
only declares the t as integer. The other two come
out as variants. That's not true in other languages but is in VBA.

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
>I learned something new, thank you!

How would I go about it if I would not want pick the names form a table
nut just from a list in that piece of code?
Kc-Mass schreef:
>Enter the table names in a table Named tblOfTables
with one field, TableName.

Dim db as database
Dim rs as Recordset

Set db = currentdb
Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
rs.movefirst
Do While Not rs.EOF
strTblName = rs!TableName
'Use your code here to substitute the variable, strTblName for table
' references in your SQL statements
'Execute your queries
rs.movenext
Loop

set rs = nothing
set db = nothing

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googleg roups.com...
>I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)

Nov 15 '06 #4

P: n/a

Kc-Mass wrote:
Copy your original code nine times and edit the last
nine copies to reflect the other 9 file names.

Or, use an array in place of the recordset containing the table names:
Dim arrTables As Variant, strTableName As Variant

arrTables = Array("foo", "bar", "baz")

For Each strTableName In arrTables
' build and execute your SQL here
Next
By the way your declaration of "Dim i, j, t As Integer"
only declares the t as integer. The other two come
out as variants. That's not true in other languages but is in VBA.

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
I learned something new, thank you!

How would I go about it if I would not want pick the names form a table
nut just from a list in that piece of code?
Kc-Mass schreef:
Enter the table names in a table Named tblOfTables
with one field, TableName.

Dim db as database
Dim rs as Recordset

Set db = currentdb
Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
rs.movefirst
Do While Not rs.EOF
strTblName = rs!TableName
'Use your code here to substitute the variable, strTblName for table
' references in your SQL statements
'Execute your queries
rs.movenext
Loop

set rs = nothing
set db = nothing

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)
Nov 15 '06 #5

P: n/a

I did it like that, just because time is an issue. I did not have time
to wait for an answer.
I just wondered if there was a way to tell the code: "here is the code,
replace varTableName by the first table in this list, then do it again
using the second name on the list and so on.
In fact, just as you described using a table but then taking the names
from text in the code....
Kc-Mass schreef:
Copy your original code nine times and edit the last
nine copies to reflect the other 9 file names.
By the way your declaration of "Dim i, j, t As Integer"
only declares the t as integer. The other two come
out as variants. That's not true in other languages but is in VBA.

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
I learned something new, thank you!

How would I go about it if I would not want pick the names form a table
nut just from a list in that piece of code?
Kc-Mass schreef:
Enter the table names in a table Named tblOfTables
with one field, TableName.

Dim db as database
Dim rs as Recordset

Set db = currentdb
Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
rs.movefirst
Do While Not rs.EOF
strTblName = rs!TableName
'Use your code here to substitute the variable, strTblName for table
' references in your SQL statements
'Execute your queries
rs.movenext
Loop

set rs = nothing
set db = nothing

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)
Nov 15 '06 #6

P: n/a
Oops, did not see this when I replied........
Please regard my post from 16:something as not written please?

Gord schreef:
Kc-Mass wrote:
Copy your original code nine times and edit the last
nine copies to reflect the other 9 file names.


Or, use an array in place of the recordset containing the table names:
Dim arrTables As Variant, strTableName As Variant

arrTables = Array("foo", "bar", "baz")

For Each strTableName In arrTables
' build and execute your SQL here
Next
By the way your declaration of "Dim i, j, t As Integer"
only declares the t as integer. The other two come
out as variants. That's not true in other languages but is in VBA.

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
>I learned something new, thank you!
>
How would I go about it if I would not want pick the names form a table
nut just from a list in that piece of code?
>
>
Kc-Mass schreef:
>
>Enter the table names in a table Named tblOfTables
>with one field, TableName.
>>
>Dim db as database
>Dim rs as Recordset
>>
>Set db = currentdb
>Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
>rs.movefirst
>Do While Not rs.EOF
> strTblName = rs!TableName
> 'Use your code here to substitute the variable, strTblName for table
> ' references in your SQL statements
> 'Execute your queries
> rs.movenext
>Loop
>>
>set rs = nothing
>set db = nothing
>>
>>
>>
>"Henrootje" <He****@gmail.comwrote in message
>news:11**********************@k70g2000cwa.googleg roups.com...
>I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21
>
Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21
>
Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31
>
This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4
>
The tables for which this has to be done are
>
tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB
>
Notice how the tblQ part is consistent as is the inclination of the
numbers
>
I was thinking of using a for next loop:
>
'========CODE========
>
Dim i, j, t As Integer
>
For i = 2 To 5
j = i - 1
>
>
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j & ".*
FROM tblQGBB" & j & " 1;"
>
Next
>
'========END OF CODE========
>
This works fine but how do I tell the code to replace the GBB part with
the names mentioned in the list before? In other words, How can I tell
the code to repeat this action for every name in the list?
>
Thanks for thinking!
>
(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)
>
>
Nov 15 '06 #7

P: n/a
The table based version that I showed you is the correct way to do it.
Doing it as you did or with and an array loaded from literal table names
in the code, is hard coding variables.
The next time you want to do the same thing with different tables
or even if you just need to do another table in this db you have to go back
and rewrite the code. With the table based version you just edit the table
records.

On the other hand you do have what you needed for now.

Kevin C

"Henrootje" <He****@gmail.comwrote in message
news:11*********************@e3g2000cwe.googlegrou ps.com...
>
I did it like that, just because time is an issue. I did not have time
to wait for an answer.
I just wondered if there was a way to tell the code: "here is the code,
replace varTableName by the first table in this list, then do it again
using the second name on the list and so on.
In fact, just as you described using a table but then taking the names
from text in the code....
Kc-Mass schreef:
>Copy your original code nine times and edit the last
nine copies to reflect the other 9 file names.
By the way your declaration of "Dim i, j, t As Integer"
only declares the t as integer. The other two come
out as variants. That's not true in other languages but is in VBA.

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googleg roups.com...
>I learned something new, thank you!

How would I go about it if I would not want pick the names form a table
nut just from a list in that piece of code?
Kc-Mass schreef:

Enter the table names in a table Named tblOfTables
with one field, TableName.

Dim db as database
Dim rs as Recordset

Set db = currentdb
Set rs = db.openrecordset("tblOfTables", dbopensnapshot)
rs.movefirst
Do While Not rs.EOF
strTblName = rs!TableName
'Use your code here to substitute the variable, strTblName for
table
' references in your SQL statements
'Execute your queries
rs.movenext
Loop

set rs = nothing
set db = nothing

"Henrootje" <He****@gmail.comwrote in message
news:11**********************@k70g2000cwa.googleg roups.com...
I have to run a lot of append queries.
I have data in tables in old databases that has to be appended to
tables in a new database.
I made a new database in which I linked the old tables and the new
tables.
Such would look like this:
Old table: tblQGBB1
New Table: tblQGBB21

Table tblQGBB21 should be emptied if filled
Data has to go from tblQGBB1 --tblQGBB21

Table tblQGBB31 should be emptied if filled
Data has to go from tblQGBB2 --tblQGBB31

This repeats itself for tblQGBB21 -tblQGBB51
and tblQGBB1 - tblQGBB4

The tables for which this has to be done are

tblQGBB
tblQROCK
tblQROCR
tblQROMK
tblQROMR
tblQROSK
tblQROSR
tblQROVW
tblQSNISNU
tblQGBB

Notice how the tblQ part is consistent as is the inclination of the
numbers

I was thinking of using a for next loop:

'========CODE========

Dim i, j, t As Integer

For i = 2 To 5
j = i - 1
DoCmd.RunSQL " Delete * from tblQGBB" & i & "1;"
DoCmd.RunSQL "INSERT INTO tblQGBB" & i & "1 SELECT tblQGBB" & j &
".*
FROM tblQGBB" & j & " 1;"

Next

'========END OF CODE========

This works fine but how do I tell the code to replace the GBB part
with
the names mentioned in the list before? In other words, How can I
tell
the code to repeat this action for every name in the list?

Thanks for thinking!

(PS, I am not a code guru, so telling me 'Use an array' does ring a
bell but not much more then that, could you please give examples?)


Nov 17 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.