Run multiple queries

[Guest]

I have some queries that will append NEW data from an identical external data
dase. I just import the tables and append the new data. The problem is I have
to run one query, one table at a time. Is there a way to run a macro or some
code that would run all the queries and append all the tables at one time? I
would appreciate some help on this.
Thanks
Ron

 

[John W. Vinson/MVP]

I have some queries that will append NEW data from an identical external
data
dase. I just import the tables and append the new data. The problem is I
have
to run one query, one table at a time. Is there a way to run a macro or
some
code that would run all the queries and append all the tables at one time?
I
would appreciate some help on this.

You can certainly use the RunSQL method from a macro; better would be to use
VBA code, using the Querydef EXECUTE method. One thing I've done is to
create a table with a field for the name of the query to be executed, a
sequence number to specify the order in which the queries should be run, and
a Yes/No field to turn execution on or off. You can open a Recordset based
on a query sorting this table by the sequence number, step through it, and
use code like

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
.... <your loop code>
Set qd = db.Querydefs(rs!QueryName)
qd.Execute dbFailOnError
.... <end of loop>

John W. Vinson [MVP]

 

[Guest]

Hi John,
This is exactly what I need. I like the table idea. My knowledge is limited
so it will be another learning experience for me. I may need to ask you a
question or two along the way.
Thanks very much.
Ron

 

[Guest]

John,
Please tell me if I'm on the right track. I have created a table
'tblAppendTables' with the fields: 'Queries', 'Sequence Numbers', and 'Run
Option'. I have created a query based on the new table sorted by Sequence
Number. This is where I get confused. Next I created a form based on the
query, 'qryAppendTables'. Is this the recordset? What do I need for loop
code? I have looked at this in VBA help, but I can't figure out what I need.
Finally, where do I put the code you gave me? Would that go in the On Click
event of a cmdButton on the form? Thanks for your help in this.
Ron