running multiple update queries with one command, query, etc

[akel210]

Hi people. I need urgent help.
I have many update queries in access 2003. I have to run all the times and I would to create SQL, VBA or combination of both to create a process where I can do this by just one query, button, function etc. I can't do this in a macro because the list of these update queries can increase or decrease at anytime and then I would have to update the macro. It defeats the purpose. All of these queries start with common name "0010 01....."

I want something where it says look up queries if it starts with “0070 01” then Run it.
Using this following SQL

SELECT Name
FROM MSysObjects
WHERE Name Like "0010 01*";

I can come up with the list of queries but i am not sure how utilize this into VBA or anywhere else to automate the process to meet my requirements.
Like I said earlier I want something where it says look up queries if it starts with “0070 01” then Run it. I am open to other options if you have any.

Please help me! Thanks in advance

[pbaldy]

You could open a recordset on that SQL and use the name along with CurrentDb.Execute or DoCmd.OpenQuery inside a loop of the results.

[pkstormy]

An example (in ADO - someone else can supply the DAO code - I use ADO myself):

Function runQueries()
dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "SELECT Name FROM MSysObjects WHERE Name Like '0010 01*'"

(note: if the above syntax doesn't work, you can also create a query using the above, save it as qryMyQueriesToRun, and then...
strSQL = "Select * from qryMyQueriesToRun"

rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly
if rs.eof and rs.bof then
rs.close
set rs = nothing
msgbox "No queries to run."
exit function
end if
rs.movefirst
do while not rs.eof
docmd.setwarnings False 'optional - if used you MUST turn warnings back on when done!!, comment out for testing.
msgbox "About to execute query name: " & rs!Name '(for testing. Comment out when live)
docmd.openquery """ & rs!Name & """ '(not sure about this syntax - maybe no " used)
docmd.setwarnings True
rs.movenext
loop
rs.close
set rs = nothing
exit function

Save this in a module. Then in your OnClick code for a button on the form (or wherever), put in code: Call RunQueries

[ssanfu]

Here is a DAO version:

Sub runQueries()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String

   Set db = CurrentDb
   strSQL = "SELECT Name FROM MSysObjects WHERE Name Like '0010 01*'"

   ' sorted order - 
   '       if the queries execute in a specific order set by query name
   '   strSQL = "SELECT MSysObjects.Name"
   '   strSQL = strSQL & " FROM MSysObjects"
   '   strSQL = strSQL & " WHERE (((MSysObjects.Name) Like '0010 01*'))"
   '   strSQL = strSQL & " ORDER BY MSysObjects.Name;"

   Set rs = db.OpenRecordset(strSQL)
   If rs.BOF And rs.EOF Then
      rs.Close
      Set rs = Nothing
      MsgBox "No queries to run."
   Else
      rs.MoveLast
      rs.MoveFirst
      Do While Not rs.EOF
         MsgBox "About to execute query name: " & rs!Name   '(for testing. Comment out when live)
         CurrentDb.Execute rs!Name, dbFailOnError
         rs.MoveNext
      Loop
   End If

   rs.Close
   Set rs = Nothing
   Set db = Nothing
   MsgBox "Done"

End Sub

[akel210]

Thank You guys!! even with my limited skills of SQL and VBA it worked perfectly. I created a button iin the form and pasted the code and it worked! the only question is can i use this code in a query? if yes how?

another thing is that I wasn't prompted with Update query prompts where it says " you are about to run an update query............" "x number of records will be updated", if i want to turn these on what modification do i need in the code?

you guys are wonderful! thanks!

[pkstormy]

the only question is can i use this code in a query? if yes how?

Do you mean copy the "SELECT Name FROM MSysObjects WHERE Name Like '0010 01*" into a query? - Yes. But you can't automate running other queries inside a query. You must use vba (or a macro) to automate the running of a query(s).

another thing is that I wasn't prompted with Update query prompts where it says " you are about to run an update query............" "x number of records will be updated", if i want to turn these on what modification do i need in the code?

Comment out or delete the line that is: Docmd.SetWarnings False

[akel210]

pkstormy, I used DAO version provided by ssanfu and in his verision doesn't have the "Docmd.SetWarnings False" line.

And I tired deleting this line from DAO code
MsgBox "About to execute query name: " & rs!Name '(for testing. Comment out when live)

But this doesn't show the prompt box for the update queries. by deleting this line i just don't see the message "About to execute query name...."

any other idea or I am doing it wrong?

thanks,

[pbaldy]

CurrentDb.Execute does not throw those warnings. If you really want them, you can switch to DoCmd.OpenQuery

[pkstormy]

Thanks Paul.

[pbaldy]

No problemo.

[ssanfu]

pkstormy, I used DAO version provided by ssanfu and in his verision doesn't have the "Docmd.SetWarnings False" line.

And I tired deleting this line from DAO code
MsgBox "About to execute query name: " & rs!Name '(for testing. Comment out when live)

But this doesn't show the prompt box for the update queries. by deleting this line i just don't see the message "About to execute query name...."

any other idea or I am doing it wrong?

thanks,

If you want to see the name of the query that is executing, below is a modification that uses the Status Bar to display the query name and doesn't require a response (like the message box) to continue to the next query.

Sub runQueries()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim bStatusBar As Variant

   Set db = CurrentDb
   strSQL = "SELECT Name FROM MSysObjects WHERE Name Like '0010 01*'"

   '(note: if the above syntax doesn't work, you can also create a query using the above, save it as qryMyQueriesToRun, and then...
   '   strSQL = "Select * from qryMyQueriesToRun"


   'save current status bar state
   bStatusBar = Application.GetOption("Show Status Bar")

   ' show status bar
   Application.SetOption "Show Status Bar", True

   Set rs = db.OpenRecordset(strSQL)
   If rs.BOF And rs.EOF Then
      rs.Close
      Set rs = Nothing
      MsgBox "No queries to run."
   Else
      rs.MoveLast
      rs.MoveFirst

      Do While Not rs.EOF
         ' show which query is executing - see status bar
         Application.SysCmd acSysCmdSetStatus, "Executing query: " & rs!Name
         
         CurrentDb.Execute rs!Name, dbFailOnError

         rs.MoveNext
      Loop
   End If

   ' clear status bar message
   Application.SysCmd acSysCmdClearStatus

   ' set status bar visible state to original state
   If Not bStatusBar Then
      Application.SetOption "Show Status Bar", False
   End If

   'clean up and exit
   rs.Close
   Set rs = Nothing
   Set db = Nothing
   MsgBox "Done"

End Sub

(the added lines are in blue)

[akel210]

you guys are amazing!
thanks ssanfu problem solved! and thanks to everyone else.

[pkstormy]

Nice code ssanfu. I'll keep that one in my archives. Thanks for the post. I'm not sure if it works in ADO but I tested it with DAO and it worked nicely.

[ssanfu]

Thanks