Append many queries to tables

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.
0
Utf
5/4/2007 6:13:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
598 Views

Similar Articles

[PageSpeed] 12

On Fri, 4 May 2007 11:13:01 -0700, Mhughes <Mhughes@discussions.microsoft.com>
wrote:

>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]
0
John
5/4/2007 10:04:59 PM
Reply:

Similar Artilces:

Forms to update queries
I have created multiple tables: cust_tbl - list of customers(will not change) prod_tbl - list of products (will not change) bud_tbl - commitment level at the beginning of the year (will not change) mon.spend_tbl - customer's monthly spend (will vary monthly) I have a query that will combine the tables to provide a list of customers, the customer's related prodcuts, the customer's commitment, the monthly spend and the variance between the commitment and monthly spend. How can I set up a form that will update the criteria in the query based off a list of customers and produ...

Quote Product Many to Many Relationship
Although we can create many to many relationships between the Quote Product and custom entities, we are not able to see the details pane of the quote product entity to view related objects. We are using the quote product entity to store more detailed information about the particular line items in an order to leverage native price engine capability in CRM, but in order for this design to work we need to see related custom entities from the quote product, order product and invoice product entities. Anyone have any idea how to facilitate visibility from the quote product form to related...

pivot table #36
Dear Debra, Could you please help me to solve this problem? I need to make a 4 quarts rolling chart. The chart data source is from a Pivot table. Pivot table data source is updated via a link with external data source. When External Data Source is updated, a new quarter Q2-07 comes in ,Q2-06 will be out, so there will be always 4 quarts data in the external data source. Pivot Table Data Source will be updated via a link with External data. My problem is every time new colum of quartly data comes in, pivot table is not showing the new data. I have to manuelly go into pivot table lay...

Without Matching Query Limit ?
Hi All, The Without Matching query wizard works fine until you select more than 13 columns to compare. My guess is that the 13 " AND " 's in the SQL left join statement are the problem. Unfortunely, my tables have 16 fields to compare. Any sugestions ? Stuart wrote: >The Without Matching query wizard works fine until you select more than 13 >columns to compare. My guess is that the 13 " AND " 's in the SQL left join >statement are the problem. >Unfortunely, my tables have 16 fields to compare. Don't use the wizard ;-) Seriously, after you d...

editing table frames
I downloaded something that was in table frame format. Now each section of the page is in table frames. I want to convert them to text or merge them into one continuous page in text format. This is driving me nuts because I can't find a way to do it. If I highlight the text in a frame and cut it to the clipboard, the whole page disappears and I'm left with this annoying blank grey page. At this point, I'm thinking that I will have to print the page, create a new textbox at the end of my story then type the page into it, but if I have to do that, I'm not going to be happy using ...

Run macro for each record in a query
Hello All I have a table [practices], which contains details of 'client organisations'. From time to time I need to send an email to certain 'client organisations'. I have a macro called 'send_emails' which uses the SendObject command, which I use to send the emails (a button on a form runs the macro). The emails include a report that is specific to the 'client organisation'. Currently I select the 'client organisation' with a combobox, run the macro, select another 'client organisation', run the macro again, etc. etc. It would be extrem...

Query Delete and append
I am trying to add information to a table with an append query and that works fine since I duplicated the table. The information comes from a linked XLS sheet, I need to delete the information and replace it every day. When I created a delete query it works fine but when I try to add the information again using the append query it does not work unless I open the query in design view, save it and run it. I have dozens of other queries doing the same and they all work fine. What am I doing wrong? I looks like I am getting a 3349 error but why does it work once and then when I delete the ...

Problem with query dates
Hi My records date range in the database is Betwene 20/05/20010 and 22/05/20010 . When my dateObject has a date of 24/05/2010 00:00:00, is shown me all the records less than this date, OK. But if I change the month, this date to 24/04/2010 00:00:00 shows the same records. That is even if I change the month the records that appear are always less than the 24 days without following month. There lies my problem. Note: My date style is dd-mm-yyyy and time is hh: mm: ss I really need to fix this.Seabra Dim Q1, SQL As String Dim DateTime1 As Date Dim ConnString As S...

Can a column be deleted with a query based on criteria?
I have a table in access that needs to be exported to an Excel spreadsheet. One of the requested design specifications for the resulting spreadsheet is that it not contain empty columns. (No columns with Null fields) Currently I am manually deleting them in Excel and this is incredibly tedious, since there are about 80 columns of data and I am checking whether they are empty by using the auto-filter feature and observing the options that appear in the drop down menu. Can I write a query that contains instructions to delete columns that are contain nothing but Nulls? J, You could write ...

visio startup starts many programs
I have Visio 2003 and Office 2003 nothing has been added or changed in over a year. Now for some reason every time I start Visio numerous other programs start as well. This goes on until memory is exhausted, Visio never actually starts. I have uninstalled and reinstalled Visio twice now and still get the same behavior. Does anyone know what is happening here? On Sun, 04 Nov 2007 13:45:43 -0800, asc4john <john@kinnear.ca> wrote: >I have Visio 2003 and Office 2003 nothing has been added or changed in >over a year. Now for some reason every time I start Visio numerous >other pro...

exportar uma pivot table
olá, gostaria de tirar uma dúvida: é possível salvar uma pivot table como uma página web preservando tanto a formatação da planilha (auto-formatação neste caso) como a interatividade da pivot table ? grato, levogiro ...

Many client computers have not reported back to the server in the
Hi ; i found the following error on my event viewer . I tried to search on Google to find some solution but no luck. I need some expert advice , if you can put me in right direction that will be great. Many client computers have not reported back to the server in the last 30 days. 15 have been detected so far. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Please post back the event ID and Source... this will help us pin down the cause and (maybe) the fix. - Larry Please post the resolution to your issue so others ...

reprot/query problem
How do i go about adding a parameter query to a report deisgned to make labels so that i get certain criteria in the report?? have tried record source haven't had much luck with it. Thanks ...

Upgradeing a published website using ftp. Many questions
I published my website using vs2010. I checked the box that made it upgradeable. As a check I ftp copied default.aspx and default.aspx.vb to the site replacing the published files. Looks like it still works. So I can update the site files using ftp? I noticed there is no App_Code folder. Suppose I need to update something in that folder, can I? How? I also noticed there is a file: PrecompiledApp.config Might I ever want to delete that file? What is it for? Would it make sense to delete all the files and ftp the entire site to the host. Can a site be uplo...

differences between two tables
I'm trying to find the differences between two tables, A and B. There are some records in A that are not in B, there are some in B that are not in A, and there are some in both but with differences in the fields. I'm not getting everything. Does anyone have any ideas how I can tackle this? Thanks! Use a UNION ALL query. -- KARL DEWEY Build a little - Test a little "denise" wrote: > I'm trying to find the differences between two tables, A and B. There are > some records in A that are not in B, there are some in B that are not in A, > and there are s...

Appending worksheets
Hello, I have a huge workbook with some 200 worksheets (Excel 2002) and wouldlike to create one single worksheet by appending the data on all worksheets one after another. Is there an easy way to do this without programming a macro? Thanks so much. Provided the total data rows do not exceed 65535 (assuming a standard top row for col labels), one way would be sequential manual copy > paste into a single new sheet placed to the left of the 200 source sheets. At an est 15 sec per manual op, 200 sheets would only take roughly an hour of work to accomplish. Thereafter, to clean up, jus...

instr for select Query
Hi. I've rersearched this forum extensivley and get the idea on how to do it but don't have enough experience to make it all the way. In a MS Access select query, I have a field that returns names in the following format: ;#Lastname1, Firstname1;#Lastname2, Firstname2;# I can't seem to sting the instr, len etc, functions together properly to clean up the string to: Lastname1, Firstname1; Lastname2, Firstname2 I've seen the Microsoft examples as well, I think I have to parse through it to break it up then concatenate back together the cleaned names, but the closest I can g...

locked table error
I am trying to open a report via a control button on a form. The form is based off of tblCustomers and the report is based off of a query which includes tblCustomers (I think the problem is due to that). The error I'm getting is "Run-time error '3211': The database engine could not lock 'tblCustomers' because it is already in use by another person or process." The db is local so I'm the only one using it right now. Any suggestions on how I can fix this problem? -- TIA have you tried closing that form before the report is open; as a sanity check? eve...

call function in query
dear friends i want an query function to have serial numbers between two pre said numbers like between 100 and 105 = 101,102,103,104,105.thus i created an function in standard access module . public function _ foils(firstfoil as int ,lastfoil as int) as integer for foils = firstfoil TO lastfoil step 1 next foils end function IN query window it appeared as foils( <firstfoil>,<lastfoil>) while accessing and running on QBE window and while runnig query error accoring as "undefined function "foils" in qbe how to build query f...

Append and Append To priviliges
Anybody know what these two priviliges do for a record?? Jay Append allows a user to be able to add items to an object for example you can append an address to an account. Append to allows a user to append this object to something else. You would need append to priv on the address object. They work together. "Jay Mehta" <jay.mehta@conexio.com> wrote in message news:b6caea5f.0310311043.682d756c@posting.google.com... > Anybody know what these two priviliges do for a record?? > > Jay So, in the example you gave of appending an Address object to an Account object,...

Using form to add record to table where records are limited by lis
Hi all... Bit hard to explain what I am doing here but I'll have a go :-) In Excel if I want to limit the number of records shown in rows I apply a filter for example to col1 which reduces all remaining cols. Then filter on col2 to reduce further etc etc until I have only a few records left to look at. I want to do this in access on a form BUT... I want to create a table of records that has an ID, Date stamp, a part number, qty I want this table to get its information from a form. I want the form to use a master table which includes the "part number" from above but where ...

flattening aggregated fields in pivot table
I'm trying to build what *should* be a simple pivot table with 2 fields and having some difficulty. The problem is that Excel wants to automatically aggregate like values. For instance, the two fields are Product Family and Part Number. There are cases where 2 different Product Families can share a Part Number (don't ask). In these few instances, The pivot table wizard automatically groups the 2 instances and only lists the Part Number once; in the second instance, it generates a blank cell where the PN should go. This wreaks havoc on one of my macros that needs every singl...

Assistance with Many-to-Many Relationships
I'm looking into correcting a previously developed database that was originally done and works like an excel spreadsheet (all fields are currently in one table). In looking at it, there appear to be many many-to-many relationships involved. This is used to keep records relating to force used against suspects by police. In one incident, there could be 1 or more suspects involved, 1 or more officers involved, 1 or more types of force involved (possibly different force by each officer involved), none/one/more charges placed on the suspect, and a final disposition on each susp...

How to creat a Delete query?
Hi, I have a query that I get all my information from. I created a query that pulls all the info I need and did a Mk tbl with that query. I now have my tbl. I Created another query that will append any new records that are created in the main query. Now I need a way to delete any records that have been removed from the main query. I tried to use a delete query, however I am not very familiar with these. Can someone please help? Thanks in advance! On Tue, 4 Mar 2008 07:35:01 -0800, Tiff <Tiff@discussions.microsoft.com> wrote: >Hi, >I have a query that I get all my informati...

Open all Excel spreadsheets/fles before running queries
I have a number of spreadsheets which use Ms Query to retrieve data from an Oracle database. I have set it up so that queries run automatically when I open a spreadsheet. In Excel 2003, if I open several such spreadsheets at the same time, Excel will open all spreadsheets first and then run the queries. This means that I can open all the spreadsheets I want to update, get immediate notification if any one of them is in use by someone else, open as read-only if so, and then go off and do something else while all queries update (15-20 minutes in some cases). In Excel 2007 wh...