How to run update queries all at once

[Zonie32]

Does anyone know how I can run several (at least 45) update queries at once? Each week I import new data and each week I have to run the same update queries. I generally spend 10 minutes manually clicking on these to run the updates. I thought there was any easier way to run them all at once.

I am using Access 2003, win xp. Thanks.

 

[PHV]

In the Click event procedure of a button in a form you may call 45 times the DoCmd.OpenQuery method.

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

 

[Zonie32]

thanks for the response. i am confused as to the rest of the code. do i have to list each query name in here..this is some of the code i am trying. sorry, i'm not very good with this code stuff yet.. I put stDocName as upd* because all of my queries begin with upd.?

Private Sub Command23_Click()
stDocName = "upd*"
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

 

[earthandfire]

Private Sub Command23_Click()

DoCmd.OpenQuery FirstQueryName, acViewNormal, acEdit
DoCmd.OpenQuery SecondQueryName, acViewNormal, acEdit
..
DoCmd.OpenQuery LastQueryName, acViewNormal, acEdit

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

Alternatively, if the list of query names was in a table, you could use a loop to process the queries.

 

[Zonie32]

I typed the query as you suggested, one on each line, so now when I click the command button, I still have to click "yes" 45 times. Is there a way to just click the button and then click yes to update all queries in sequence? Is this what you refer to as "loop" if they were in a table?

 

[PHV]

Take a look at the DoCmd.SetWarnings method.

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

 

[earthandfire]

As PHV says use DoCmd.SetWarnings as follows:

DoCmd.SetWarnings False before first DoCmd.OpenQuery
and
DoCmd.SetWarnings True after last DoCmd.OpenQuery


I tend to use resource tables for this sort of thing.
You just need a single text field and populate it with the names of the queries. This means that if you need to change a query name or add or delete a query, you won't have to change you code just edit the field in the table.

 

[Zonie32]

Thanks earthandfire. Your reply was very helpful and it seemed to do the trick! I will look into the resource table as I do add update queries quite often. Thanks again.

 

[cmmrfrds]

I usually stack the queries up in a Macro and put an Echo between each query so I can monitor the progress.

 

[Lightning]

Another way to do it:

Sub CommandButton_Click()

Dim qdf As QueryDef
Dim Db As Database

    Set Db = CurrentDb
    DoCmd.SetWarnings False
    For Each qdf In Db.QueryDefs
        If qdf.Name Like "upd*" Then
            DoCmd.OpenQuery qdf.Name
        End If
    Next
    DoCmd.SetWarnings True
    
End Sub

This will loop through all the queries in the database and, if the query name begins with "upd", it will run the query.

Notes:
1 This will run every query that begins with "upd", so if there are some queries you don't want to run, they may need to be renamed.

2 This uses DAO, so your database will need a reference to the DAO 3.6 Object library.

HTH
Lightning