how to run queries

[rutica]

I'm using Access 2003.

In my database, I have over 300 queries.

Out of that, I have 150 Append queries that I run each month. Because of the way they are named, they are in position 2 thru 151 in the query window.

(They are named AppendCriteriaCostY4; AppendCriteriaScopeR8; AppendCriteriaRiskP7, etc.)

Is there a way to tell Access to run queries 2 thru 151?

Thanks,

 

[jdraw]

I'm using Access 2003.

In my database, I have over 300 queries.

Out of that, I have 150 Append queries that I run each month. Because of the way they are named, they are in position 2 thru 151 in the query window.

(They are named AppendCriteriaCostY4; AppendCriteriaScopeR8; AppendCriteriaRiskP7, etc.)

Is there a way to tell Access to run queries 2 thru 151?

Thanks,

How do you run them now?
Do you have the queryNames in a Table?
Do you always run all queries?
What happens if a query fails (for whatever reason)?

 

[rutica]

Thanks for writing so quickly! Answers below.

How do you run them now?
Manually, by double clicking each one.

Do you have the queryNames in a Table?
No

Do you always run all queries?
Yes, all the Append queries with the similar names (AppendCriteriaXXX) are run monthly. But sometimes new Append queries are added and sometimes existing ones are deleted. If new Append queries are added, they will stay with the same naming convention.

What happens if a query fails (for whatever reason)?
They are manually researched to see why it failed.

 

[Mile-O]

150 queries sounds a tad excessive and no doubt means that your database isn't normalised.

Anyhoo, you could just create a subroutine, like this:

Public Sub RunAllQueries
    On Error Resume Next
    Dim intLoop As Integer
    For intLoop = 2 To 151
        With DoCmd
            .SetWarnings False
            .OpenQuery "MyQueryName" & intLoop
            .SetWarnings True
         End With
    Next intLoop
End Sub ' RunAllQueries

 

[rutica]

Thanks for writing.

Would I have to type each query name in the "MyQueryName" fields?

 

[Mile-O]

Well, the whole idea was that I've interpreted that all the queries were the same name with only the number suffixing them changed. But, on rereading, I see that wasn't the case. You have a mixed naming structure.

What common attributes do these query names have? There's no point in saying etc. if there are many differences.

 

[rutica]

I was trying to show that all my queries started the same, with the words 'AppendCritiera' and then a changing value.


I guess my main question is, does Access assign a 'behind the scenes' name to each query depending on its position in the query window?

So would Access assign the first query a name something like SysQuery1, the second SysQuery2, etc.

Then, regardless of what I name the query, I could ask Access to execute queries 2-151 by using the query's machine names.

Thanks,

 

[gemma-the-husky]

if yuo have a common naming convention for your queries then you can refer to them in that way

ie "myquery" & sequencenumber

so you run

myquery1
myquery2
myquery3
myquery4 etc

if yuo can find a way of generating the suffixes you want (ie they dont have to be 1,2,3,4 etc) this will work

alternatively, create a table of all the queries yuo will need to run (one time only), then go through the table to run all the queries

 

[Mile-O]

I guess my main question is, does Access assign a 'behind the scenes' name to each query depending on its position in the query window?

So would Access assign the first query a name something like SysQuery1, the second SysQuery2, etc.

Not really. You may want to try entering the following bit of SQL into the SQL View of the Query Editor:

SELECT * FROM MSysObjects;

Anyway, this bit of code should do what you need:

Public Sub DoAppendQueries()

    On Error Resume Next

    Dim qd As DAO.QueryDef

    For Each qd In CurrentDb.QueryDefs
        If qd.Name Like "AppendCriteria*" Then
            qd.Execute
        End If
    Next qd
    
Exit_DoAppendQueries:
    Set qd = Nothing

End Sub ' DoAppendQueries

If you get an Undefined Type error, then have a look at this.

 

[rutica]

Mile-O,

Perfect. That works great!!!

Thanks a bunch!

 

[rutica]

How can I make a running total so after running all the append queries, a message box appears that says:

msgbox ("There were " & x & " append queries that ran and you have appended " & y & " records.")

Thanks,

 

[Mile-O]

Public Sub DoAppendQueries()

    On Error Resume Next

    Dim qd As DAO.QueryDef
    Dim intQueryCount As Integer
    Dim lngRecordsAffected As Long

    For Each qd In CurrentDb.QueryDefs
        If qd.Name Like "AppendCriteria*" Then
            qd.Execute
            lngRecordsAffected = lngRecordsAffected + qd.RecordsAffected
            intQueryCount = intQueryCount + 1
        End If
    Next qd
    
    MsgBox "There were " & intQueryCount & " queries run, affecting " & lngRecordsAffected & " records."
    
Exit_DoAppendQueries:
    Set qd = Nothing

End Sub ' DoAppendQueries

 

[rutica]

Perfect again!!

You're the best. It works great.

Thank you thank you thank you thank you

 

[dallr]

Glad you got your solution from Mile-O. However, I want to endorse what he mentioned in his first post. Your database is not properly normalized and you should stop if you can and correct this before moving further.

This would save you plenty of problems and workarounds in the future.

Shortcuts make for long delays.

Dallr

 

[rutica]

Thanks for writing.

The data is entered on a web form that asks Project Managers about 100 questions about schedule, cost, risk and scope.

The raw data is put into Excel and then copied into Access. Depending on what the Project Managers answered on the web form, they get different codes. There are 150 codes they can get.

For example, if they answer their schedule is delayed by at least 30 days, they get a Sched-Red code. If the variance between their Planned and Actual cost is >10%, they get a Cost-Red code. If they don't have a Transition plan and their system is deploying within 3 months, they get a Tech-Yellow code.

There is a different query for each code and there are 150 codes.

Are you saying it's not normalized because there are so many queries?