CODEWITHSUNDEEP
sqlms-accessloops
tksy
tksy

Reputation: 3529

How to run a loop of queries in access?

I have a database with a table which is full of conditions and error messages for checking another database.

I want to run a loop such that each of these conditions is checked against all the tables in the second database and generae a report which gives the errors.

Is this possible in ms access.

For example,

querycrit table

id           query                                 error    
1           speed<25 and speed>56              speed above limit  
2           dist<56 or dist >78                dist within limit

I have more than 400 queries like this of different variables.

THe table against which I am running the queries is

records table

id   speed     dist    accce   decele   aaa   bbb     ccc
1     33        34      44         33   33     33      33
2     45        44      55         55   55     22      23

regards ttk

Upvotes: 0

Views: 13352

Answers (4)

Fionnuala
Fionnuala

Reputation: 91316

Here is some more sample code. It illustrates the use of two different types of recordsets. You may wish to read VBA Traps: Working with Recordsets by Allen Browne and List of reserved words in Access 2002 and in later versions of Access .

Dim rs As DAO.Recordset
Dim rs2 As ADODB.Recordset

Set rs = CurrentDb.OpenRecordset("querycrit")
Set rs2 = CreateObject("ADODB.Recordset")
rs2.ActiveConnection = CurrentProject.Connection
For Each tdf In CurrentDb.TableDefs
'EDIT: TableDefs includes Microsoft System tables and '
'these should never be tampered with. They all begin with Msys '
'so we can leave them out of the loop here. '
   If Left(tdf.Name, 4) <> "msys" And tdf.Name <> "querycrit" Then
        rs.MoveFirst
        strSQL = "SELECT * From [" & tdf.Name & "] WHERE "

        Do While Not rs.EOF
            On Error Resume Next
            Debug.Print tdf.Name
            rs2.Open strSQL & " " & rs![query]
            If Err.Number = 0 Then
                On Error GoTo 0
                If Not rs2.EOF Then
                    Debug.Print rs![Error]
                    Debug.Print rs2.GetString
                End If
            End If
            Err.Clear
            rs2.Close
            rs.MoveNext

        Loop
    End If
Next
End Sub

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91316

Here is some sample code, it is typed, not tested.

Dim rs AS DAO.Recordset
Dim rs2 AS DAO.Recordset

Set rs=CurrentDB.OpenRecordset("querycrit")

strSQL="SELECT * From Records WHERE "
Do While Not rs.EOF
   Set rs2=CurrentDB.OpenRecordset(strSQL & rs![Query])
   If Not rs2.EOF Then
       Debug.Print rs![Error]
       Debug.Print rs2.Fields(1)
   End If

   rs.MoveNext
Loop

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91316

"actually there are many record tables to be checked and not all queries can be run on all tables, for eg in one table speed may not be there and in next table distance may not be there."

The correct think to do, I think, would be to create a table of tables and a query-table junction table that shows which queries are to be run on which table, for example:

TableID  QueryID
1           4
2           1
2           3
3           1

This can the be used to run the correct set of queries on each table.

Upvotes: 0

Patrick Cuff
Patrick Cuff

Reputation: 29786

When you say "report", do you mean an Access Report, or would writing to a file or Access Form work?

You can create a function or sub in a Module to do this. Open a recordset on your querycrit table and spin through the records dynamically building and running the SQL for the records table. You can write the results of these dynamic queries to a file, or a form, or insert the results into a temp table and drive the Access Report from there.

Upvotes: 0

Related Questions