Run sequence of queries in a macro

wrek

Registered User.
Local time
Today, 21:08
Joined
Jun 14, 2001
Messages
88
Hi, I often have need to run queries in sequence, (sometimes as many as 100 in a row). Obviously, I do not want to manually create a macro to do this. This is what I used to do:

I'd use QueryDefs.Name to drop the query names into a table QTable...

Order QueryName Run

1 Query1 Y
2 Query2 N
3 Query3 Y etc


Then the following function would run them, called thru a macro:

numQueries = DCount("[Order]", QTable)
For x = 1 To numQueries

varx = DLookup("[Query]", QTable, "[Order] = '" & x & "'")
vary = DLookup("[Run]", QTable, "[Order] = '" & x & "'")

StringQry = varx

If vary = -1 Then
DoCmd.OpenQuery StringQry
DoCmd.Echo True, StringQry
End If

Next x

Used to work flawlessly, now all of a sudden (we upgraded to Access2002, where it DID work for a few months) it won't work on some databases anymore (You cancelled the Previous Operation, is the perplexing error message)

Any ideas on how I can do/fix this???????? (thanx)
 
For one thing its hopelessly ineffecient doing 2 dlookups on the same table. Doing it with a recordset would be much more efficient. But since its a small table (100 or so records) it dont matter much.

well then. I must say i am surprised it worked at all... In the dlookup the table name should be qouted like the field name.....
so....

varx = DLookup("[Query]", "QTable", "[Order] = '" & x & "'")
vary = DLookup("[Run]", "QTable", "[Order] = '" & x & "'")

might just do the trick

Regards

P.S. as it should be in the DCount as well....
 
thanks for the reply...

QTable was a String parameter that I passed through the function, so it is equivalent to the quotes...also, I did try putting the tablename directly in the DLookup...it refused to go back to its working ways

Ive read that this error is sometimes indicative of corrupted modules within your database...but I've generated the modules from scratch...imported all the objects etc...still no luck...
 
Last edited:
Would order by any chance be a (auto) number field?

Can you post like a sample or something? Maybe that will clarify things for me.... There MUST be a logical source somewhere....

Regards
 
no...order is actually a text field, i had problems getting it to work with a number field
 
the function was basically:

Function UpdateQueries (QTable as string)

Dim varx,vary as variant
Dim numqueries as integer



numQueries = DCount("[Order]", QTable)
For x = 1 To numQueries

varx = DLookup("[Query]", QTable, "[Order] = '" & x & "'")
vary = DLookup("[Run]", QTable, "[Order] = '" & x & "'")

StringQry = varx

If vary = -1 Then
DoCmd.OpenQuery StringQry
DoCmd.Echo True, StringQry
End If

Next x

end function
 
I meant sample db the code allready up here....

But i will try and whip something together when/if i get the time....

Regards
 
unfortunately, i do not have access to the db till the beginning of jan...some rare time off...:D


I will revisit this thread then though. I appreciate your efforts though.


(I figured a lot of people would have need for something like this, and am actually quite surprised that I couldn't find something that already exists that does this. Maybe I gotta keep looking.)
 
Hi guys, finally back from a restful break...

here is a very simplified example of what I'm talking about (I shortened it to 4 queries)....

Try running the macro 'Lookup'...used to work without a hitch, now it just refuses to....

Thanks for the help...

:confused:


[Edit: I've since realized that this way works as well...fieldnames were inconsistent]
 

Attachments

Last edited:
I solved it using SQL....here's the function if anyone's interested:

Put the Queries in a table (in this case MODEL_Auto_Query)***

Three Fields (Order, Query, Selected) [Number, Text, Boolean]

I call the function through a macro.


Function UpdateRefresh()
'
Dim db As Database
Dim rec As Recordset
Dim strSQL As String
'

strSQL = "SELECT MODEL_Auto_Query.Order, MODEL_Auto_Query.Query, MODEL_Auto_Query.Selected "
strSQL = strSQL & "FROM MODEL_Auto_Query "
strSQL = strSQL & "WHERE (((MODEL_Auto_Query.Selected) = True)) "
strSQL = strSQL & "ORDER BY MODEL_Auto_Query.Order;"
'
Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL)
'
Do Until rec.EOF
DoCmd.SetWarnings False
DoCmd.OpenQuery (rec!Query)
DoCmd.SetWarnings True
rec.MoveNext
Loop
'
rec.Close
Set rec = Nothing
Set db = Nothing
'
End Function


***(You don't have to type out the query names individually if you use the following function...it outputs to the Debug Window, (i cut and paste) you could also alter this to output right into a table)

Function ListQuery()

Dim dbs As Database, qdf As QueryDef

Set dbs = CurrentDb
dbs.QueryDefs.Refresh

For Each qdf In dbs.QueryDefs
Debug.Print qdf.Name
Next qdf

Set dbs = Nothing
End Function
 
Last edited:
Which is just about what i was thinking ... when i said:
For one thing its hopelessly ineffecient doing 2 dlookups on the same table. Doing it with a recordset would be much more efficient.

Glad you worked it out....

Just another thought.... Do you have "normal" sets or logical sets of query's? What i mean is this, do you run the same sets over and over
Some allways
Some in other cases
You might try grouping the query's to groups limmiting the number of clicks you need to do to select them all. Also limmiting the click-error changes.

You might even want to be able to recognize the query(groups) by name... But that is another thing....

Hope this give you an idea or 2

Regarsd
 
Thanks mailman,

Actually, that is exactly what i do...in the unsimplified version.

I've got another table grouping each query set to an index, and I sequence only the needed groups based on the most efficient function (user defined).

Also, the recordset idea was good, (and probably more efficient)...

But the Dlookup method works as well (funny thing is, I probably didnt even need to make this post...the reason it didnt work is because the module was looking for 'OrderNum', my field name was 'Order') :rolleyes:

but hopefully others can use this (or in the LEAST - learn from my carelessness)

thx again
 
Last edited:

Users who are viewing this thread

Back
Top Bottom