Re: auto-run all of my queries

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 11/30/04 

Date: Tue, 30 Nov 2004 16:27:10 +0200

Nick,

Something like this will do it:

Sub Run_All_Queries()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Name FROM MSysObjects"
strSQL = strSQL & " WHERE Left([Name],3) <> '~sq' AND Type = 5"
strSQL = strSQL & " ORDER BY Name"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
Do Until rst.EOF
    DoCmd.SetWarnings False
    DoCmd.OpenQuery rst.Fields(0)
    DoCmd.SetWarnings True
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

I have to say, though, that as a minimum precaution you should make all your
action query names start with a given prefix, and filter on that rather than
just excluding the system ones (~sq), so you will be able to add non-action
queries if need be, without them being opened from the code.

Note: this piece of code requires an appropriate DAO reference.

HTH,
Nikos

"nh" <reply@thegroup.com> wrote in message
news:uorjHpt1EHA.3404@TK2MSFTNGP10.phx.gbl...
> I am building a process which will ultimately be done in SQL server, but I
> am prototyping in Access..
>
> I have a load of tables and a series of action queries which import dats,
> manipulate it and the output it.
>
> qry1 - Transfer data into new table
> qry2 - Delete old rows
> qry3 - Remove commas
> qry4 - Add brackets
> qry.......
>
> I have writtten a module which runs a them all one after another, but the
> problem is that every time I cerate a new query in the process, I have to
> add another line to the code to run the new query...
>
> Can someone tell me how to write some code which systematically runs all
of
> the queries in the database in alphabetical order.. (this would also be
> useful for so many other tasks..)
>
> I know there is a system table which hold this information, but I have no
> idea how to get a list of queries, and loop through it running each one as
> it goes...
>
> Can someone help me?
>
> Thank you
>
> Nick
>
>

Relevant Pages

  • Re: default to previous record?
    ... Dim rst As Dao.Recordset ... Dim fld As Dao.Field ... Set rst = Me.RecordsetClone.Clone ...
    (microsoft.public.access.forms)
  • Re: Importing csv data
    ... Dim rst As DAO.Recordset ... Open vFile For Input As #1 ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.externaldata)
  • Re: running a query in a loop
    ... Dim rst As DAO.Recordset ... Dim strSQL As String ... Set dbs = CurrentDb ... Set rst = dbs.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Requerying Problem
    ... Dim intSID As Integer, intCID As Integer, strsql As String ... Set rst = db.OpenRecordset("SELECT C.cid FROM ENROL E, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Requerying Problem
    ... Dim rst1 As DAO.Recordset ... Set rst = CurrentDb.OpenRecordset("SELECT C.cid FROM ENROL E, ... CurrentDb.Execute strsql, dbFailOnError ... "Alex Dybenko" wrote: ...
    (microsoft.public.access.modulesdaovba)