Copying the entire data from a range of tables into one

[PeteCan]

Can anyone help:

I have over 1000 tables called ILCommentx (where x=1 to 1000+). I want to copy the entire contents of each table into one table called ILComments.
I could do this manually but as I'm sure you'll appreciate this would take a considerable amount of time, has anyone any idea how I can setup a macro to automatically do this?

 

[lupins46]

Take a backup first:
Create a sub as follows.

dim x as integer
for x = 1 to 1000
strsql = "Insert into ilcomments Select * from Ilcomments" & x
currentdb.execute strsql, dbfailonerror

next x

 

[PeteCan]

This gonna sound really thick but how do I do this in Access?

I've created a module called test with the above code in it,
then how do I run it?

thanks

 

[PHV]

With the cursor inside this sub, press F5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PeteCan]

Thanks, it does the first table,just gotta get it to do the rest now