Append many queries to tables

[Mhughes]

I'm trying to automate the running of 8 queries that append to 3 different
existing tables (for example):
qry1, qry2, qry3 appends to tbl1.
qry4, qry5, qry6 appends to tbl2.
qry7, qry8 appends to tbl3.

I've been doing this process manually, the tables already exist with the
proper field names that align with the field names in the qry's. There is no
duplication of fields between qrys or tbls. Since I do this once every week
I have to first make a blank copy of each table (keeping the original field
names and settings only), then I delete the old table and then I append each
qry to the new empty table. Is there an SQL or VBA programmatic solution
that would help me do some of this stuff automatically. I know some SQL and
a bit of VBA, but really have wasted time searching and reading to figure
this out. Any help would be fantastic and truly appreciated.

 

[John W. Vinson]

I'm trying to automate the running of 8 queries that append to 3 different
existing tables (for example):
qry1, qry2, qry3 appends to tbl1.
qry4, qry5, qry6 appends to tbl2.
qry7, qry8 appends to tbl3.

I've been doing this process manually, the tables already exist with the
proper field names that align with the field names in the qry's. There is no
duplication of fields between qrys or tbls. Since I do this once every week
I have to first make a blank copy of each table (keeping the original field
names and settings only), then I delete the old table and then I append each
qry to the new empty table. Is there an SQL or VBA programmatic solution
that would help me do some of this stuff automatically. I know some SQL and
a bit of VBA, but really have wasted time searching and reading to figure
this out. Any help would be fantastic and truly appreciated.

You don't really need to make all these copies of tables, delete the tables,
create new tables, etc. etc. Instead, make a backup of your entire .mdb file
(a good idea in any case!!) before the weekly operation. You can then run a
Delete query (DELETE * FROM tbl1; for example) against each table, then run
the append queries.

There are several ways to automate running a series of queries - macros for
one - but I'd really recommend using VBA code. If the list of queries that you
need to run every week is at all likely to change (and I'm guessing it will)
then you may want to create a little table WeeklyJob with two fields: Qname
and Sequence, e.g.

Qname Sequence
EmptyTbl1 1
EmtpyTbl2 2
EmptyTbl3 3
qry1 11
qry2 14
qry3 17
qry4 21
qry5 24

<etc.>

Note leaving gaps in case new queries need to be run between existing ones.

You can then open a Recordset based on WeeklyJob and execute the queries:

Public Sub RunWeeklyJob()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.Querydef
On Error GoTo Proc_Err
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Qname FROM WeeklyJob ORDER BY Sequence;", _
dbOpenDynaset)
Do Until rs.EOF
Set qd = rs!QName
qd.Execute dbFailOnError
rs.MoveNext
Loop
Set qd = Nothing
rs.Close
Set rs = Nothing

Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error in weekly: Error " & Err.Number & vbCrLf & Err.Description
Resume Proc_Exit
End Sub


For safety's sake you may want to wrap the whole batch of queries in a
Transaction (see the online help for the term).

John W. Vinson [MVP]