Speeding up Append Queries

[DZ]

I need to append all records, from several tables (several fields) to an
empty table, programmatically. The entire process takes about 12 seconds.

I am using a loop that loops thru the tabledef (DAO)and using
DoCmd.RunSQL "INSERT INTO etc"
Inside the loop to run the append queries.

Can anyone suggest a different way to do this that would be substantially
faster?

Thanks in advance

 

[Lord Kelvan]

why dont you just do it in a query

insert into table (field1,field2,field3)
select field1,field2,field3
from mytable

and that will insert all records form a table into another table

then do it again for a different table

then in your code you have push a button to activate all of thoes
queries one after the other at the clikc of a button or youcan just go
run them each seperatally

but is there any reason you are wanting to do this the point of a
database is to remove redundancy and use queires to match things up

unless you are doinging this in order to delete all the other tables
you can use a union query to gather all the information from these
tables into on query for use in reporting or something

regards
Kelvan

 

[John W. Vinson]

I need to append all records, from several tables (several fields) to an
empty table, programmatically. The entire process takes about 12 seconds.

I am using a loop that loops thru the tabledef (DAO)and using
DoCmd.RunSQL "INSERT INTO etc"
Inside the loop to run the append queries.

Can anyone suggest a different way to do this that would be substantially
faster?

Thanks in advance

Perhaps you could post your code. Are you inserting one record at a time by
stepping through a recordset? or one table at a time? If so... WHY? Do you
have multiple tables of the same basic structure that you're combining, or
what?

 

[DZ]

Hi

Yes Of course. Great idea. I can great one big union query

and then append from there to the main table.

Thank you